top of page
Search

Advanced Graphing in Excel

Updated: Jul 13

Today, I want to write a bit about some more advanced graphing ideas in Excel. Generally, if you know these are available as options, they are pretty easy to implement. Once again, the Excel files are included as a download.


First, using checkboxes to plot series selectively. Basically, once you have some data set, insert checkboxes, then use an IF statement to link the data set back to either NA or the original values. The checkboxes when checked act as TRUE in the IF statement, so when unselected, NA values will pop up and remove the data series from the plot. See below. You can also use a number of other tools, such as list boxes to do the same thing.

Spreadsheet displays data in columns, checkboxes marked true, and a line chart with three series on the right, labeled Series1 to Series3.
Spreadsheet with data columns and a line graph on the right. Graph shows trends in orange and green lines, labeled Series1-3, over time.

Next, creating a Gantt chart. This is basically used for project timelines. You need to insert a stacked bar chart. Use no fill to hide the data series (right click on series, fill, no fill). Then invert the categories in format axis (right click). See below. You don't need the checkboxes here, but you can include them if you want to highlight certain tasks (you would need to reformat them, obviously).

Spreadsheet featuring tasks, start times, durations, and a Gantt chart with green bars in a white background. Columns labeled 1 and 2.

Next, a waterfall chart. Basically, we select our data and insert a waterfall chart in all charts. The only adjustment is to right click on the EBITDA and EBIT and format the data points to set them as totals.

Spreadsheet with a waterfall chart showing financial data: Revenue, COGS, EBITDA, SG&A, D&A, and EBIT. Bars in blue, orange, and green.

Now, you can also create sunburst charts and treemaps. The only thing you need to know is how to format your data tables, which you can see below. So, it's relatively straightforward and I won't comment on them much. For treemaps I format the data series to have a banner. Similarly, world maps can also be created. You can also fiddle around and add things like 3d projections onto them.

Spreadsheet with a table of SBUs, product lines, and revenue. It has a treemap comparing Retail and E-commerce, and a sunburst chart with sections for each category.
Map highlighting Canada, U.S., Brazil, and Germany in blue shades by revenue amount. Adjacent table lists countries with revenues.

Fortunately, most of this is pretty streamlined in terms of what you need to do, which is mostly Insert, All Charts, Select Chart. Hopefully, you can see the use of these graphs types as well for visualizing segmented revenues and such.


If you found this helpful or enjoyable, please consider subscribing to our newsletter for more.  


You can reach out to us here for consulting services or here to discuss booking a workshop to learn useful skills and help you get more familiar with us. 

Comments

bottom of page