Excel Tips
How to create "Data Validation" with a list in other worksheet ?
Go to menu "Insert" -> "Name" -> "Define",
add one new define name "listPIC", with "Refer to" field set to "=WorkSheetList!$J2:$J10"
How to create "Data Validation" with a dynamic list ?
=OFFSET(WorkSheetList!$J$1,0,0,COUNTA(WorkSheetList!$J:$J),1)
How to add path info on excel title bar ?
Excel by default not display the open workbook full path name, create some inconvenience on identifying open work book.
Tips : add the web toolbar item
1. Right click at the toolbar area, choose "Customize..."
2. Browse "Categories:" to "Web" and "Commands:" to "Address"
3. Drag the "Address" Commands to toolbar area by holding left mouse key.
How to change chart's name ?
Tips : Hold the Shift key whilst selecting the chart, edited the chart name in the Name box.
Tips : Or via VBA code:
'Rename Selected Chart Object
ActiveChart.Parent.Name = "ChartXYZ"
How to apply cells formatting, data validation etc up from row 6 to row 65536 without file size increase ?
Tips : 1. Select the whole column, apply cells formatting, data validation etc.
2. Remove cells formatting, data validation etc for first 5 rows.
3. File size will not increase up to ~1MB.
(If you select row 6 to row 65536 by click at cell A6, press [Ctrl]+[Shift]+[Down], use format painter tool to apply cells formatting etc,
file size will increase tremendously up to ~1MB)
How To reset used range (prevent file size increase) ?
1. Select the entire row immediately below your data, press [Ctrl] + [Down Arrow] to extend the selection right to the bottom of the sheet
2. Right click mouse and select Delete.
(Note that you've got to use the Right-Click > DELETE option, NOT the Delete key on the keyboard.)
Or use VBA code below
Sub ResetUsedRange()
Dim sht As Worksheet
Dim lng As Long
For Each sht In ActiveWorkbook.Worksheets
lng = sht.UsedRange.Rows.Count
Next
End Sub
How to make alternate row with different conditional formatting ?
=MOD(ROW()-3,2*1)+1<=1
=MOD(ROW()-4,2*1)+1<=1
How to create mini in-cell bar chart ?
1. Enter a percentage value (30 for 30%) at cell K26, for example.
2. Enter formula below at cell K27to draw bar chart
=REPT("█",ROUNDDOWN(K26*10, 2))&REPT("▓",ROUNDUP((1-K26)*10, 2))
3. Use a suitable font for the symbol rectangular bar, for example MS PGothic.
4. Screen shot below for reference.