How to build an interactive Excel Dashboard (step-by-step): 📊
Step 1: Setup
For the sake of this tutorial, I’ve gone ahead & created an Excel workbook with a bit of sample data.
If you’d free copy, click the link below and you can download a template of the exact spreadsheet I am using here: https://t.co/rB6zgj5EYM <div class='jekyll-twitter-plugin'><p>There was a ‘Not Found’ error fetching URL: ‘https://twitter.com/blakeaburge/status/1568571007392452612/photo/1’</p></div>
Step 2:
Start with the following worksheet tabs:
• Dashboard - Empty (for now) • Data - Raw data
Within our “Data” tab we’ll have the following columns:
• Customer Name • Item Description • Qty Sold • Price Each • Revenue • Cost • Profit • Date Sold
Like this: <div class='jekyll-twitter-plugin'><p>There was a ‘Not Found’ error fetching URL: ‘https://twitter.com/blakeaburge/status/1568571018817720325/photo/1’</p></div>
Step 3: Format Data As A Table
Click anywhere inside the range of cells.
Then head to the top Ribbon & select:
Insert → Table
Or use the keyboard shortcut: CMD/CNTRL + T
Excel will automatically identify the range of data.
✓ “My table has headers” and click OK: <div class='jekyll-twitter-plugin'><p>There was a ‘Not Found’ error fetching URL: ‘https://twitter.com/blakeaburge/status/1568571058613293056/photo/1’</p></div>
Step 4: Insert Pivot Table #1
Next we’re going to use a series of Pivot Tables to pull the data we want into our Dashboard.
• Click inside your table • Head to “Insert” → PivotTable • Select the existing table • Place in a new worksheet
Click OK: <div class='jekyll-twitter-plugin'><p>There was a ‘Not Found’ error fetching URL: ‘https://twitter.com/blakeaburge/status/1568571097523814401/photo/1’</p></div>
Step 5: Duplicate PivotTables
Create a separate PivotTable for each metric you want on your dashboard.
Use CNTRL+drag to duplicate the sheet.
Name each one according to the info you’d like to display.
P1 - Rev By Customer P2 - Qty Sold By Customer P3 - Qty Sold By Item <div class='jekyll-twitter-plugin'><p>There was a ‘Not Found’ error fetching URL: ‘https://twitter.com/blakeaburge/status/1568571144663617536/photo/1’</p></div>
Step 6: Setup Pivot1-Rev by Customer
Within the PivotTable Field selector, move the categories listed to the appropriate areas:
Customer → Rows Item → Columns Revenue → Values
Format the data as currency.
Sort ‘grand total’ & ‘item’ columns from highest to lowest value. <div class='jekyll-twitter-plugin'><p>There was a ‘Not Found’ error fetching URL: ‘https://twitter.com/blakeaburge/status/1568571203547443201/photo/1’</p></div>
Step 7: Insert Chart For Pivot1
Next we’ll create a quick chart to visualize the data from our first PivotTable.
• Click inside the table • Pivot Analyze • Pivot Chart • Chart Design • Change Type → 3D Stacked Column • Add Chart Title <div class='jekyll-twitter-plugin'><p>There was a ‘Not Found’ error fetching URL: ‘https://twitter.com/blakeaburge/status/1568571261294641152/photo/1’</p></div>
Step 8: Move Pivot1 Chart
Alright, you’ve created your first chart.
Now we need to move it to our dashboard tab so the data is visually displayed in a simple summary form.
Here’s how:
• Select the chart • Design → Move Chart • Object in → Dashboard <div class='jekyll-twitter-plugin'><p>There was a ‘Not Found’ error fetching URL: ‘https://twitter.com/blakeaburge/status/1568571300054183936/photo/1’</p></div>
Step 9: Pivot 2 & 3
Follow the same basic steps as above:
P2 -Qty Sold By Customer
• Customer Name → Rows • Qty Sold → Values • Format → Number • Sort Large → Small
P3 -Qty Sold By Item
• Item → Rows • Qty Sold → Values • Format → Number • Sort Large → Small <div class='jekyll-twitter-plugin'><p>There was a ‘Not Found’ error fetching URL: ‘https://twitter.com/blakeaburge/status/1568571355960066051/photo/1’</p></div>
Step 10: Charts for Pivot 2 & 3
Follow the same process from Step 8 above:
• Click inside the table • Pivot Analyze • Pivot Chart • Chart Design
• Change Type →P2: 3D Pie →P3: 3D Column
• Select charts • Design → Move Chart • Object in → Dashboard <div class='jekyll-twitter-plugin'><p>There was a ‘Not Found’ error fetching URL: ‘https://twitter.com/blakeaburge/status/1568571420921450496/photo/1’</p></div>
Step 11: Insert Slicers
To make our Dashboard truly interactive we need to insert a couple of slicers.
These allow users to select data of their choice and gain a more personalized experience.
• Select chart • Pivot Analyze • Insert Slicer • Customer Name / Item Type <div class='jekyll-twitter-plugin'><p>There was a ‘Not Found’ error fetching URL: ‘https://twitter.com/blakeaburge/status/1568571475355127808/photo/1’</p></div>
Step 12: Connect Slicers
You may have noticed in the previous step that only one of our charts was updating when we selected criteria using our slicers.
Let’s connect them to ALL of our PivotTables now:
• Right-click on a slicer • Report connections • Select PivotTables <div class='jekyll-twitter-plugin'><p>There was a ‘Not Found’ error fetching URL: ‘https://twitter.com/blakeaburge/status/1568571520494223361/photo/1’</p></div>
Step 13: Refresh Data
As sales come in, you’ll want to add them to your table to keep your dashboard up to date:
Head to the ‘sample new data’ tab.
• Copy data • Paste to the next row in your ‘data’ table • Dashboard → Select chart • PivotChart Analyze • Refresh
Done! <div class='jekyll-twitter-plugin'><p>There was a ‘Not Found’ error fetching URL: ‘https://twitter.com/blakeaburge/status/1568571590711070720/photo/1’</p></div>
I hope you learned something new today!
Want more Excel?
The basics is a good place to start.
• 2 Emails a week • 12 Lessons
Stop “punching the clock”
Start Moving Forward Today👇🏻 https://t.co/h1cq3WojZQ
That’s all for today!
If you enjoy learning new things about Excel, Google Sheets, and software in general, follow me @blakeaburge.
Also, join 20K+ people and check out my newsletter:
NERD ALERT 🚨
3 Tech Tips | Every Sunday | 5 Minutes https://t.co/8gpnSlkewx
@blakeaburge #save #excel