How to Build Excel Advanced Charts and Graphs | Tutorial for Dashboard Design, by Excel Visual on YouTube. 27 min 13 s.
- Video length
- 27 min 13 s
- Time to do it
- 45 minutes
- Difficulty
- Medium
- Steps
- 10
What you need
Steps
Lay out your data in three blocks: 6 expense categories, 4 goal categories and 5 income categories, with one column per month. Keep each block in a fixed range so formulas can refer to it.
Create named ranges for each block using Formulas, then Name Manager. Point each name at the category values and the matching month column so the chart series can swap.
Build the first bar chart from one named range. In Select Data, set the series to the named range rather than a fixed cell range so it changes when the name changes.
Add a cursor or highlight element to the bar chart so the selected category stands out. Use a second series or a shape aligned to the bars.
For a rounded column look, insert rounded rectangle shapes over the bars, or use a custom shape fill, and size them to match the column widths.
Build the combo chart by adding a second series to the bar chart and changing its chart type to a line or column on a secondary axis.
Insert Form Control option buttons or toggle buttons for each indicator. Link them to a cell that returns a number, then use INDEX or CHOOSE to feed the chart series.
Add a radar chart for the goal or structure view. Link its series to the same named ranges so it updates when the buttons change.
Add global month buttons linked to a month cell. Use that cell inside your named range formulas so every chart on the sheet switches month together.
Group the buttons and charts, test each toggle, and hide helper cells on a separate sheet before sharing the file.
Tips and common mistakes
Name every range you use in charts, because fixed cell references break when you add months or categories.
Link option buttons to one cell per group and keep the cell visible while testing so you can see which value is returned.
Too many series on one chart causes clutter, so use the toggle buttons instead of adding every indicator at once.
Save a copy before adding shapes and buttons, since overlapping objects are fiddly to select and remove.
Questions
Do I need macros for the toggle buttons?
No. Form Control option buttons and a linked cell work without macros. Formulas like INDEX or CHOOSE then feed the chart series.
Why does my chart not update when I click a button?
Check that the button is linked to the correct cell and that your named range formula refers to that cell. Also confirm the chart series uses the name, not a fixed range.
Can I use this dashboard for personal budgeting?
Yes. The expense, goal and income blocks fit a personal finance tracker. Replace the sample categories with your own and keep one column per month.
This guide was drafted from the video's details and general task knowledge. It has not been checked against the full video. Check the video and relevant product instructions before following a procedure. Report a correction.
