Excel Dashboard



Overview



Dashboard - Excel version constraints


• Applies to Excel 2007 onwards except for:
◇ Slicers - Excel 2010 onwards
◇ Alternatives to Slicers Combo boxes, VBA codes
• Map Charts - Excel 2016 for Office 365 users only

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Tips



• Don't develop Dashboard zoomed out. Always at 100%
• Format source data into Excel Table

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Steps



• Format data in Sheet Data into Excel tables (ctrl-t) and name the table
◇ Excel Table range will auto grow when new data is pasted in

• Create the following sheets with pivot chart. Name the tables and charts. Copy sheet to create new sheet
◇ Line Pivot
◇ CategoryPivot
◇ ManagerPivot
◇ PiePivot
◇ SparkLinePivots

• SparkLinePivots
◇ Field Settings > Show items with no data
◇ SparkLineTotalPivot
◇ SparkLineNextLook
◇ SparkLineFashionsDirect

• Map Chart
◇ Field Settings > Show item labels in tabular form, repeat item labels

• Cut and paste charts to dashboard
◇ SparkLine Box - add color bar
◇ Home > Conditional Formatting > Data Bar
◇ show color on same cell / different cell
◇ SparkLine Box - add chart
◇ Create dynamic named range for each state
◇ Insert > Line (source: Pivot Table)

• Insert Slicers
◇ Slicers for Category, State, Financial Year
◇ Method 1
▪ From pivot table, select field, right click and select "Slicer" (Excel 2013 onwards)
◇ Method 2
▪ Insert tab > Slicers
◇ Move and hold down "Alt" will snap Slicers to the Grid

• Tweaks
◇ Turn off Gridlines (view > gridlines)
◇ Make manual legends
▪ Insert > Shapes / TextBox
▪ shift-ctrl to copy legend
▪ Format > Shape Fill > Color / Gradient
◇ Set unit display to thousands
▪ Double click the chart to open the Chart Pane
▪ Format Axis > Display Units

• Create dynamic labels to Pie Chart
◇ Refer to formula in sheet PiePivot
◇ GETPIVOTDATA function: auto gen (click on the item you want on the pivot table)
◇ Back to dashboard, Insert > Illustrations > Shapes > TextBox
◇ Enter "=" in formula bar and select the cell containing the desired text in sheet PiePivot
◇ Format TextBox: no outline, no fill

• Link Chart to Slicer
◇ Right click Slicer > Report Connections
◇ Missing pivot tables from the list?
▪ All pivot tables must use the same source data. Check the data source
▪ Accidentally create pivot cache from the same source data
▪ Copy existing pivot table and recreate the pivot table that's missing
◇ For Slicer State, include all pivot tables but the MapPivot

• Update Dashboard with New Data
◇ Add data to sheet Data
▪ Excel Table will automatically include the new data in table range
◇ Refresh all pivot tables and charts, Data > Refresh All

• Beautify
◇ Themes: Page Layout > Themes
▪ Try "Parcels"
◇ Alignment
▪ Group objects in chart together
- shift and click: select multiple objects
- Format > Group > Group
▪ Options > Align > Align Left/ Right/ Top ..
◇ Adjust Chart Size
▪ Format > Size
▪ Adjust to equal heights etc.
◇ Move Chart with Arrow Keys
▪ Hold down ctrl and select Chart, you can see the handle change to "circle"
◇ Edit Caption of Slicers
▪ Right click > Slicer Settings
◇ Get rid of column labels, sheet tabs, scroll bars
▪ View > Headings
▪ File > Options > Advanced > Display Options > Show horizontal / vertical scroll bar
▪ File > Options > Advanced > Display Options > Show sheet tabs

• Dashboard Protection
◇ Refer to worksheet “Dashboard Protection”

• Embed Dashboard in Webpage

• Chart Receipe e-book

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Source



https://www.youtube.com/watch?v=K74_FNnlIF8

Index