In this video I will show you how to create a horizontal bar chart in Excel comparing values to the left and to the right. The data I am using is for digital acquisition by channel comparing YoY numbers for September 2018 vs September 2017.
I recently took part in a challenge organized by Storytelling with Data to create a waterfall chart.
I rarely see this type of chart in digital marketing dashboards, if ever! The challenge wasn’t limited to data related to digital marketing but I wanted to find a use case for a waterfall chart in a digital marketing environment.
What is a waterfall chart
Waterfall charts are used to show how an initial value has increased or decreased by a number of values in between that lead to a final value. These intermediate values can either be time based or category based.
My challenge was not only to create such a chart for a digital marketing report but to also find real data.
A couple of years ago I started working with a client on their blog and content strategy. Since we started publishing content that is relevant but also targeting high volume search terms, their visibility improved significantly.
A Waterfall Chart To Visualize Online Visibility Improvements
This type of data is a great candidate for a waterfall chart that will show how the number of keywords ranking on the first page of Google has increased over a certain period of time.
Such data can also be visualized with a bar chart or even a line chart.
The use of a waterfall chart however will emphasize on the intermediate values or in this case the number of new keywords in high positions for the intermediate time period (each month in 2017).
With the inclusion of a takeaway title this visual turned into a great data storytelling example.
Here are a few other use cases for a waterfall chart to be used in digital marketing dashboards
- Newsletter subscribers at the start and end of year with their intermediate positive or negative numbers
- Number of external links at the start of a link building campaign and at its end
- Number of acquired leads for the duration of a paid media campaign
You can create a waterfall chart in Excel 2016 and other data visualization tools like Tableau. I used Excel 2013 and this tutorial.
While it is the data in any Excel reporting that matters, good styling, colorful charts and graphs will turn a report from raw data to a compelling story and be easier to digest and understand.
Here are a few basic tips to help you create prettier reports in Excel and tell a story with your data
Styling Excel Tables
1. Start your data table in B2
Tables in Excel are not very easy to digest. Data is best understood when visualized with graphs and chart. However there are data numbers that need to be presented in a table.
Moving the table to start from B2 gives a nice spacing between the top and left margins of your report. This spacing simply makes it easier to look at a table of data and easier to read and understand the data
2. Branding and Report Title
It might also make sense to start even further down in column B and insert logos of your agency, client or brand at the top of each sheet on your report. Underneath this branding fill one of the rows with a suitable branding color by selecting the entire row and then fill it with an appropriate color. You can begin your table of data one row below this filled row.
You can also insert report title and date.
3. Always use table formatting
Tables with data are easier to read if formatted properly. Excel has a great table formatting feature with some really good built in table formatting templates. You can also build your own table formatting style to use or you can manually format your tables. When formatting tables try to use a header row, lines between each row and rotate colors between rows.
4. Remove background noise by deleting the gridlines from the sheet. You can do this by going to the View tab and untick the Gridlines option. This makes the sheet background white without any distraction making it easy to focus on the data.
Styling Excel Charts
5. Align axis titles on the chart horizontally
One of the things I hate the most about Excel charts is when I have to tilt my head to read the axis titles.
There are many ways to avoid this. You can change the chart type or stretch the chart to fit in the titles.
6. Delete gridlines and legends with one data series
In order to tell a story with your data the reports need to be clean of any background noise or anything not useful on the charts. I personally love charts and graphs that have no gridlines. Gridlines might be useful with a line chart but any other chart is better without them. It is very easy to delete gridlines, simply click on one of the lines to select and hit delete.
Also if your chart has only one data series it is no use to keep a legend of it. Delete it to focus on the important story the chart has to tell.
7. Sort your data before charting
Make it easier to understand what data is saying by sorting largest to smallest numbers before charting your table.
It is way easier to visualize how numbers in your data relate if they are sorted beforehand.
8. Color your tabs
If you have a report with data in several tabs you have the option to give colors to your tabs. You can choose a color by right clicking on the tab and choose “tab color” option and select the color of you choice.
*Disclaimer: For this video I have used data from the Google Merchandise Store Google Analytics which is publicly available.
Combo Boxes in Excel create drop down menu options and dynamically populate tables and charts.
In this video I will show how to create a dynamic mini dashboard in Excel with the Combo Box. The drop down menu will give us the option to select All Devices, Desktop, Mobile and Tablet and the table and charts will dynamically change to reflect the number of sessions and the revenue made for each device or all devices. The date range is January 2017 to June 2017 and the data is broken down by month.
The most important part of creating a combo box is to properly configure the input range and cell link (demonstrated in the video). The other important part if to understand the Index function in Excel. For the combo box the Index function contains an array, which includes the column or certain rows in a column, and a row_num which would be the cell we have selected as a Cell Link. All of these are demonstrated in the video.
Styling and designing is just to give ideas and show you options but you can style it in any other way that suits your reporting.
*Disclaimer: For this excel heatmap video I have used data from the Google Merchandise Store Google Analytics which is publicly available.
In this video I show how you can create a heatmap chart in Excel. The excel heatmap will highlight the most and least popular times of day and days of week when users make purchases on the online store. You can use it in any way to suit your data and your reporting needs. You can use it to look at marketing channel and location or conversion and browser technology, just to name a few ideas.