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.