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.
Excel Guide to Creating Charts and Excel Dashboards
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 vary depending on how you would like to present your data. You can even choose 2D or 3D.
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
We have the following information:
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
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
Step 3: Notice that the “Asia” label is not aligned with its corresponding bars?
Bar Chart Spacing
We can solve this by adding two spaces on the highlighted cell:
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
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 ELEMENTS have several options.
- 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.
- 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.
- 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.
- Data Labels will let you show or hide labels on the bars.
- Error Bars
- Gridlines are those lines you can find on the bar chart. Deselecting gridlines will hide those lines.
- 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
Step 5: You may change the chart style using the brush icon on the side of the bar chart.
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
The 2016 Sales of Alevo will be highlighted on the Bar Chart:
Filtered Bar Chart
The 2016 Sales of Alevo will be highlighted also on the table:
The filter function is very useful when you are skimming through tons of data.