In today’s world, data is the new currency. Data is everywhere, and hence visualizing data is the first step in making sense out of data. If you ever wanted to do quick data visualization without the need of having a separate (license-based) tool, then this blog is just for you. In this series of blogs, we will discuss how to do Data Visualization using MS Excel. This doesn’t just bring about a whole new means of visualizing data but also form a crucial part of any Excel Certification Program.
In this article, you will get an insight into the ease of using MS Excel for data visualization. We shall discuss the following topics:
- Why Data Visualization for MS Excel?
- How to import data in excel?
- Visual Filtering using Slicer
- Bring life to your data using Conditional Formatting
Now the first question which comes to mind is,
Why Data Visualization using MS Excel?
There are many reasons why using Excel is an ideal choice for Data Visualization including but not limited to the following;
No need to pay separate license cost for other tools (as it is safe to assume that most of us have MS Excel installed on their computers)
Easy to use since there is no need to learn a separate tool
Easy to share the visualization (for example dashboard) with others as the receivers also don’t need a separate visualization tool.
Initial Data Visualization using Excel: Importing data in Excel
Many of the data files are directly in the .xlsx format so we can directly work with them. However, for other formats like CSV, etc Excel provides an easy to import them.
Open a new instance of MS Excel and on the top ribbon, click on the icon of Data. After clicking on it, you will get the following screen.
Excel provides an easy way to import the data from various formats like CSV, XML, JSON etc.
Click on the relevant data source. Choose the correct import options (for ex the delimiter in a CSV file), and the data will get imported into excel.For example: after importing a CSV file into excel, the data looks like this:
The next important step after loading the data is to convert it into a table.
To convert the data into a table, go to the Home icon and then click on the Format as Table option. Select the desired format and then select the entire data as the range. The data looks like this after converting into table format.
After converting to the table format, the data looks much more readable. Moreover, Excel recognizes it as Table which means that a lot of functions can be applied directly on the data like a filter, sort etc.
Initial Data Visualization using Excel: Visual filters using Slicer
One of the common operations which need to be performed on data is to apply data filters. Although filters can be applied by clicking on each attribute name, there is a better and more visual way of doing it i.e. the Slicer.
Click on the Insert icon and then click on the Slicer icon.
After clicking the Slicer, a dialog box called Insert Slicer will appear with all the attributes and a checkbox against them.
Click on the desired attribute name and press Ok. Please note that multiple attributes can also be selected. In this example, select Location and then press Ok.
After pressing Ok, a new dialog box with the name as Location gets opened. All the various Locations which are present in the data set can now be seen in the dialog box. Just click on any of the values to filter the data by that value.
Initial Data Visualization using Excel: Bringing life to your data using Conditional formatting
Conditional formatting helps in visually exploring and analyzing data, detecting issues, and identifying trends and patterns. Using Conditional formatting, it is very easy to highlight interesting cells or ranges of cells and visualize data by using data bars, color scales, and icon sets.
1. Data Bars
A data bar helps to analyze the value of a cell in comparison to other cells. The length of the data bar is directly proportional to the value in the cell. A longer bar represents a higher value, and a shorter bar represents a lower value. Data bars are useful in spotting higher and lower values, especially with large volumes of data.
Select all the values for any column whose values you can explore using the data bar. After selecting the values, click on Conditional Formatting Icon under the Home Menu. Once the drop-down appears, click on Data Bars option and select the desired color.
After selecting the desired color, the data in Total Revenue column will look like the below snapshot. Please observe that now just by eyeballing the data, it is very easy to make out the patterns in Total Revenue attribute.
2. Color Scales
Color scales are visual guides that helps in understanding data distribution and variation. A three-color scale helps in comparing a range of cells using a gradation of three colors. The shade of the color represents the higher, middle, or lower values. Similarly, in two-color scale, the shade of the color represents higher or lower values.
Select all the values for any column whose values you can explore using color scales. After selecting the values, click on Conditional Formatting Icon under the Home Menu. Once the drop-down appears, click on Color Scales option and select the desired color style.
After selecting the desired color style, the data in Temperature column like the below snapshot. Please observe that Excel displays the cooler temperatures in green, the hotter temperatures in red and the middle-temperature ranges in yellow.
3. Icon Sets
You can use Icon sets to classify data into various categories separated by a threshold value. Each icon represents a range of values.
Select all the values for any column whose values you can explore as icon sets. After selecting the values, click on Conditional Formatting Icon under the Home Menu. Once the drop-down appears, click on Icon Sets option and select the desired style.
After selecting the desired icon style of your choice, the data in Pamphlets columns will look like the snapshot below. Please observe that the star is filled as per the number of pamphlets distributed.
4. Top/Bottom Rules
You basically use Top/Bottom rules to quickly find and highlight top values, bottom values, average values etc.
Select all the values for any column wherein the Top or bottom values have to be highlighted. After selecting the values, click on Conditional Formatting Icon under the Home Menu. Once the drop-down appears, click on Top/Bottom Rules option and then multiple options can be seen.
After selecting the Top 10% option, select the desired color style. The data in Total Sales column will look like the below snapshot. In the illustration below, the top 10% values (2 values out of 22 values) are highlighted.
We can apply multiple rules in the same attribute. For example, the below illustration shows the top 10% values in red and bottom 10% values in yellow.
As illustrated in this blog, MS Excel is a powerful tool which provides many useful data visualization options like Slicer, Data Bars, Color Scales, Icon sets, Top/Bottom Rules etc. Using these options, one can quickly analyze data patterns and visually explore the data.
Hence, next time you are given some data, try using these options to make more sense out of your data. In the next blog, advanced data visualization techniques using MS Excel will be presented which will help in detailed data analysis.
Microsoft Excel is one of the simplest and powerful software applications available out there. The Advanced Excel Training Programme at Edureka helps you learn quantitative analysis, statistical analysis using the intuitive interface of MS Excel for data manipulation. The usage of MS Excel spans across different domains and professional requirements. There’s no better time than now to begin your journey! To get in-depth knowledge on any trending technologies along with its various applications, you can enroll for live Edureka Online training with 24/7 support and lifetime access. Don’t miss out on this opportunity to enhance your career prospects and become a sought-after business intelligence professional. Click the link below to enroll in our MSBI Course now!