A Complete Excel Guide to Creating Charts and Excel Dashboards

Welcome to our Excel guide on how to construct interactive charts and dashboards.Usually, students of Excel thinks that only the formulas and syntaxes are the most important aspects of learning Excel.

But, tell you what, presenting those data are equally important.

The users of your crunched numbers will most likely don’t have time to dig deep into your excel file.

They want it simple, they want it fast.

And you can accomplish what they want through the use of charts. Good charts are pleasing to the eyes and are easy to understand.

Suggested: Read more about excel for finance

Before you start the course, please note that we are using Excel 2013. Some tactics might still work for earlier versions, but, you can get the most of this course if you are using Excel 2013.

You will be given different scenarios and your target chart for each scenario. You will have to follow the steps in order to achieve your target chart.

Please download our Excel Chart Guide A and B. These contain the raw data and the solutions to our exercises.

Have fun!

A Complete Excel Guide to Creating Charts and Excel Dashboards

Excel Guide to Creating Charts and Excel Dashboards

Bar Charts

Actually, creating bar charts with excel is pretty easy. All you have to do is first create a table then go to INSERT, then choose Bar Chart.

Bar Charts

Bar Charts

Bar charts vary depending on how you would like to present your data. You can even choose 2D or 3D.

Bar Chart Types

Bar Chart Types

For the following exercise, we are going to create a bar chart for two different variables. We will also be discussing some simple tricks which will make your bar charts better.

Scenario: You were given the quarterly sales for the products Alevo, Philpa and Xenol for the Asia and Europe Region. Please refer to Excel Chart Guide A, “Bar Chart” tab.

Your goal: A bar chart

Sample Bar Chart

Sample Bar Chart

We have the following information:

Sample Sales Data

Sample Sales Data

Step 1: Highlight the tables then go to INSERT, choose Bar Chart, and the choose the 2D Column Stacked format.

Your chart will look like this:

Unedited Bar Chart

Unedited Bar Chart

Step 2: Edit the title. Double click the “Chart Title” box. Let’s use the title “2016 Sales for Asia and Europe”.

Bar Chart Title

Bar Chart Title

Step 3: Notice that the “Asia” label is not aligned with its corresponding bars?

7 Bar Chart Label

Bar Chart Spacing

We can solve this by adding two spaces on the highlighted cell:

Bar Chart Spacing

By adding two spaces on this row makes Excel think that there’s another label between “Asia” and “Europe”, thus, it moved the label “Asia” to the left.

Bar Chart Spacing

Bar Chart Spacing

Step 4: Clean-up the chart using the CHART ELEMENTS option. This is the “+” button you can find every time you click the chart.

Chart ElementsCHART ELEMENTS have several options.

  1. Axes refer to the values of the horizontal and vertical axis. Unchecking it will remove those names. In our chart, the axes are the amounts on the vertical side and the Quarter names on the horizontal side.
  2. Axis Titles gives you the option to label the axes values. For example, you may name the values on the vertical axis as “Sales” using the Axis title option.
  3. Chart Title allows you to show or hide the Chart Title. If you want to edit the Chart Title, just double click the box of the chart title.
  4. Data Labels will let you show or hide labels on the bars.
  5. Error Bars

Chart Elements

Chart Elements

  1. Gridlines are those lines you can find on the bar chart. Deselecting gridlines will hide those lines.
  2. Legend presents the values being represented by the colors of the bars.

For our exercise, uncheck “Legend”. We don’t need it in our discussion.

Your Chart will now look like this:

Sample Sales Bar Chart

Sample Sales Bar Chart

Step 5: You may change the chart style using the brush icon on the side of the bar chart.

Bar Chart Styles

Bar Chart Styles

Step 6: You can use this filter button if you are searching for data on the chart and on its related table. For example if you highlight.

Bar Chart Filters

Bar Chart Filters

The 2016 Sales of Alevo will be highlighted on the Bar Chart:

Filtered Bar Chart

Filtered Bar Chart

The 2016 Sales of Alevo will be highlighted also on the table:

Filtered Table

Filtered Table

The filter function is very useful when you are skimming through tons of data.

Next Batch Starts on 29th April, 2019
- Classroom Course with Placement Assistance: Our Full-Time Financial Modeling Investment Banking Course (6 Weeks) starts on 29th April, 2019 and 2.5 Months Weekend Workshop starts on 6th April, 2019 in New Delhi, India and on 16th March, 2019 in Gurgaon, India.

- Online Course with Placement Assistance: Join our Online Financial Modeling Investment Banking course with Placement Assistance from anywhere in the world.

- CFA Level - I: 16th April, 2019

- Financial Modeling Investment Banking Weekend in Gurgaon: 6th April, 2019

- See Testimonials.

Only a few seats remain. Interested candidates can contact me.

View All BIWS Courses –Free $397 Bonus for FinanceWalk Readers
A Complete Excel Guide to Creating Charts and Excel Dashboards 1

Leave a Comment