This Guest Post is contributed by Aprajita Sharma from ExcelChamps. Learn about 3 new charts available in Excel 2019 (or with a Office 365 subscription).
When we think of Excel the first thing that comes to mind is data and calculations.
But there is something more to do other than data; Analysis and its Presentation.
The presentation of the data is very important to explain our point to the audience.
You might spend hours analyzing a large dataset but it’s not useful or helpful until you present it.
You probably use basic and advanced charts but there are a few new ones in OFFICE 2019/365.
…Funnel, waterfall and 2D Map! Use these to present your data in a more appealing and simpler way.
In this post we’ll explore these charts…so let’s get started.
1. Funnel Chart
This chart is the visual representation of decreasing data in each step or stage.
Funnel chart presents the figures in descending order due to its funnel shape; highest at the top and least at the bottom.
This chart works only if you have one group of data. There is no axis in this type of chart.
Suppose you’re working on an idea collection workshop and you receive 500 ideas from the participants. In the end, you could only implement 15.
Now you want to graphically present the lost ideas at each stage between 500 and 15.
How will you present this in graphical form? Yes, Funnel chart is the best option.
Now you cannot deny the fact that Funnel chart is more conclusive and visually appealing.
You will notice that the size of the bar against 250 is half of our start point i.e 500.
The size of the bar is determined by its value, higher the value, larger the bar size.
Steps to make Funnel Chart
- Organize the data in descending order. If the stages or data set cannot be altered or sorted in descending order, it is not advised to use a funnel chart.
- Then click on the insert tab ➜ Charts.
- In case you are not able to find the funnel charts, click on Recommended charts ➜ All charts. The funnel chart is listed in the left column of the window.
Quick Tip: The funnel chart is used to present descending values. If you use the same chart for ascending values, it will look like a Pyramid chart.
2. Waterfall Chart
Making Waterfall charts in previous versions was also possible but it could take around 20 minutes to organize your data.
But now in Office 2019, Microsoft has given this as an inbuilt chart type reducing users’ efforts.
This chart type shows the cumulative effect of the data series both positive and negative.
This chart is used to represent both positive and negative values especially in data related to finance and accounting where we have both outflow and inflow.
In this chart, we have shown the price trend from Jan-19 to Jul-19. We started from USD 1000 in the month of Jan’19.
The bars in orange color denote negative figures i.e. decrease in price and blue positive.
In Feb-19 we increased the price by USD 40 i.e. 1000+40 = USD 1040.
Hence the bar in the month of Feb-19 starts from 1000 and ends up in 1040.
The bar for Mar’19 starts from 1040 and ends in 1040 +30=1070.
Similarly, in Apr-19, we decreased the price by USD 500. The orange bar starts at USD 1070 (1000+40+30) and goes down to USD 500 (1070-500).
Steps to make Waterfall Chart
- Start from arranging your data in the order or actual trend. Don’t try to sort your data in descending or ascending order.
- Select any cell of the data table ➜ Insert ➜ Charts ➜ Waterfall charts.
2D Map Chart
You might come across situations where your data has more geographical details like population, area, market size, sales channel distribution across the globe.
Such data can be best presented in a map. How do you insert a map in your data?
Probably you have been downloading the images from google and inserting the same into your data.
This includes a lot of struggle in terms of manually entering labels on each point, formatting and resizing the map.
Here’s the solution called 2D map chart.
You need an internet connection to make or append data in this chart. But you can view a map chart without an internet connection.
This chart can be used in the data where we have cities, states, countries, postal codes.
If your data includes cities which can be common across the globe, you should add a country name or postal codes to your data.
Steps to make 2D Map chart
We have data with revenue from different cities in Asian Countries. Select any cell from the data ➜ Insert ➜ Charts ➜ Maps ➜ Filled Map.
Your map chart is ready.
But hold on, this is not the same as we wanted. The cities are too small and not very visible.
Now you need to change a few settings. Right click on the chart and click on Format Data Series.
Series Options: You will find 3 tabs Map Projection, Map Area and Map Labels.
- Map Projection gives you a drop down of different types of projection of the map chart Automatic, Mercator, Miller and Robinson.
- Map Area allows you to zoom a particular portion of the map based on data; Automatic, the only region with data and World.
- Map Labels is like data labels. It gives you a drop-down list of None, Best fit only a show all.
Select Map Projection and map Labels as per your choice and requirement but select Map area “Only region with Data” for the above-mentioned example.
Series Color: The series color option is only available for a chart with values. If your chart is based on some categories, you will not find the Series Color options.
Using series color, you can change the color of the map chart from 2 to 3 colors.
- Sequential (2-color): – The map chart above is a 2-color map chart where all the values are highlighted with different shades of the same color blue. Highest the value, deeper the color. You can reverse the color code by changing the figures or colors against the “maximum” and “Minimum”.
- Diverging (3-color): – In case 2 color chart looks boring, you can change the chart to 3 colors using diverging (3-color). This will give you an additional option when compared to Sequential (2-color) i.e. Midpoint.
Now for the Map chart, we have made, let’s change the Map area to “Only regions with data”.
You map chart is ready.
Guest Author: Aprajita Sharma
Aprajita is an MBA in Sales & Marketing and has been using Excel for the last 8 years.
Her journey started from learning a basic pivot table from Google which made her fall in love with Excel. She is a lifeguard to people around who fight with data on day to day basis.
My name is Kevin Lehrbass. I’m a Data Analyst. I live in Marham Ontario Canada.
Microsoft Excel is my favorite data software. In each new version there are so many new charts, tools, functions, etc!
A major benefit of blogging is that I interact with so many people and learn from them 🙂