Introduction

As a Data Analyst or Business Intelligence Analyst, you're probably familiar with "Excel Pivot Tables." Excel Pivot Tables is one of the strongest features of Microsoft Excel, that when properly used, can help one perform effective data analysis.

In this article, we'll look at Data Analytics with Excel Pivot Tables. We'll learn what Data Analytics is and the different types of Data Analytics and why they're essential in making appropriate business decisions. We'll also learn about the various areas where Analytics can be applied and how we can use Excel Pivot Pivot Tables to perform effective Data Analysis with examples.

The following section covers some of the requirements needed to get the most from this article.

Prerequisites

This article is intended for anyone who likes to play around with data and make use of that data to take appropriate business decisions.

All you need to follow along this article is:

• At least Microsoft Excel 2016 and above installed on your computer
• A basic know-how of Excel and willingness to learn.
• Dummy Data you can use throughout the different steps.

Understanding Data Analytics

In today's business, where competition is highly prevalent, it is important to always stay ahead in the race. For this, you need to understand the changing business trends and do a thorough market analysis. It is highly recommended to analyze business data to take effective business decisions.

Hence, data analytics is the science of processing raw data, deriving meaningful information from it, and using it for fact-based decision-making.

Where Does this Data Come From?

One of the main sources of the data is the internet through

• Emails
• Internet cookies
• Browser logs
• Shopping websites
• Google maps

Other sources include your grocery store shopping pattern, HR information, etc.

Data Analytics Scenarios

You can apply data analytics in different fields, and there are different scenarios where we’ve all come across Data Analytics applications; some are described below.

The first example is based on our day-to-day life. Most of us watch television and enjoy various shows that fit our preferences. Now that we have some back story ask yourself this. How does a broadcast company X decide what to telecast during its prime time? By making use of data analytics

Company X collects lots of data, including viewer information, likes and dislikes, and the age group of the viewers, through various social platforms. After analyzing this data, company X understands and caters to public demand.

Similarly, telecom companies provide the best offers to customers after studying their needs in terms of their call frequency and usage, data usage, etc.

Analytics in the form of performance analytics can also be used in corporate divisions such as Human Resource Management. For this, the employee's performance is monitored with respect to the expectation of the business, and timely feedback or corrective measures are taken to improve the employee's productivity and hence improve the business.

When you go to a shopping website, you may often see shopping recommendations best suited for you. This is an output of the data analysis done on a user's age, gender, country, search history and even purchase history.

This shows that data analytics can be used in all possible sectors like manufacturing, retail, healthcare, finance, human resource, and supply chain. The possibilities are limitless!

Categories Of Data Analytics

Data analytics can be broadly classified into the following four categories:

• Descriptive analytics
• Diagnostic analytics
• Predictive analytics
• Prescriptive analytics.

Descriptive Analytics

Descriptive analytics is the simplest type of analytics that uses data mining techniques to break a huge chunk of data into smaller and more meaningful information. The analytics gives an answer to what has happened and is derived from the data in the past.

For example, when you visit a shopping website, you're shopping pattern, shopping history, gender, nationality, and preferences are all stored in the retailer's database. This is just raw data, and with the help of analytics, it can be analyzed to draw your shopping patterns. The data can later be used to provide great recommendations that suit your taste when you visit the website again.

Diagnostic Analytics

Diagnostic or Inquisitive analytics is done to probe further, and understand why something happens. It helps determine the factors and events that contributed to a given outcome. Let's take a social media campaign, for example, based on the number of posts, reviews, and followers; you can diagnose the data and see what worked and did not in your campaigns.

Predictive Analytics

This type of analytics is done to identify patterns and trends and make inferences and predictions about the future based on the available data. It answers, what could happen. Predictive analytics can be used to forecast a customer's behavior and purchasing patterns, hence identifying the trends in sales activities. This can thus help the retailer to plan ahead and stock enough products to meet future customer demands.

Prescriptive Analytics

Combining all the other forms of analytics mentioned helps us give an answer to what is best, and this is called Prescriptive analytics. Prescriptive analytics makes it easier to choose the best option from many possible solutions, leading to an effective business decision.

Healthcare divisions can use prescriptive analytics to decide how much they need to invest in new facilities and equipment based on the health trend of the population.

Benefits Of Data Analytics

Data Analytics has several features that make it worth applying to a business, including:

• Better Communication with customers- with a good amount of data available that describes a customer's needs, it becomes easier to propose exactly what a customer requires, leading to better and more efficient communication with the customer.
• Re-Develop Products- You can redevelop or modify your products based on a customer's requirement by studying the facts and figures from customer feedback or surveys.
• Risk Analysis- You can perform a thorough risk analysis before bringing any major change in the business process. With this, you can predict the impact of the change and ensure that the change will bring the desired results or not.
• Customize Website- Major shopping websites these days provide tailored recommendations to users based on their shopping patterns, age, gender, location, etc. This is made possible by Data Analytics.
• Cut Down on Maintenance Cost- Proper monitoring, for example, in a manufacturing unit, can help calculate the equipment's wear and tear and predict when it might fail. Hence, a cost-effective replacement strategy and a lesser downtime can result in big savings by cutting down the maintenance cost.

Excel And Pivot Tables

Excel has a lot of benefits that make it an ideal tool for Data Analytics, including:

• It is very user-friendly and is an easily available tool.
• It is widely used across organizations.
• It has inbuilt features that make data analytics very easy.
• It is a great tool for analyzing and visualizing data.

Its capability to perform various types of calculations, a collection of statistical functions, a data analysis tool pack, and good data visualization makes excel an obvious choice among users.

Excel is vast and has a number of features available to perform a wide variety of tasks. In this article, I will focus on deep diving into one of its powerful features, the PivotTables, and how it can be used to perform business data analytics.

When you have a huge chunk of data with thousands of rows and columns, it becomes difficult to focus on that particular data which can help you find the answer to your query.

Analyzing your data appropriately can help you make good business decisions. But for that, you need to know where to start. An Excel PivotTable makes data easy to comprehend, helps summarize a huge chunk of data, helps visualize data better, and draws trends and patterns out of that.

With the predefined functions within the pivot tables, it helps make calculations easier and quicker.

Getting Started with Pivot Tables

To get started with Pivot Tables, you ensure you have installed Microsoft Excel 2016 and above. If You are a student and have a valid school email, you can access Microsoft 365 for free, which also contains Microsoft Excel and many other tools.

The next step after installing Microsoft Excel is opening a Worksheet that contains Dummy Data. Your Source Data can be in a worksheet or an external Database file.

In Excel, you can find the Pivot Table options under the Insert ribbon in the Table's panel.

While creating a pivot table, it is important to keep the following points in mind:

1. Data Should be In Rectangular Format
2. Data Must have table headers.
3. No blank Rows or Columns in between
4. Data should be consistent. For example, if you have a date column, make sure that all the values in that column are dates.
5. Worksheets can be in a table form or a normal range.
6. Source data can be in a worksheet or an external database file

The screenshot below shows sample data that has followed all the rules and is ready for importing to a Pivot Table.

With our data ready for importing to a pivot table, we now have everything required for one to create a Pivot Table.

We will create a pivot table using our dummy data in the following section. We will learn about recommended Pivot Tables, various components of a pivot Table, custom PivotTable, how to format your PivotTables, and once you have your PivotTables ready, you will learn how to add charts to your PivotTables.

Recommended Pivot Table

Let get our hands dirty and create a pivot table by building from the case scenario below.

We have a set of dummy data in a tabular form such as the employee ID, employee's last name and first name, region, designation, date of birth, date of joining, net salary, and working hours of each employee.

Suppose you want to determine the total number of working hours for each job designation. Here, a Pivot Table can make your job easier by making this data easy to analyze.

First, select the cells with the required data to create a Pivot Table. You can do this by pressing the down control key, shift key, and right arrow and then the control key, shift key, and down arrow.

Now, go to the Insert tab. Here, in the Table panel, you can see there are two options: PivotTable and Recommended PivotTable, as shown in the screenshot below.

This Recommended PivotTable option helps you by automatically recommending and automatically creating PivotTables. When you click on Recommended PivotTables.

You can see that Excel automatically recommends different combinations of tables like Sum of Net salary by Region, Count of ID by Region, Sum of net Salary by Region, etc.

If you are not satisfied with the recommendations, you can click on the Blank PivotTable button and create a PivotTable on your own.

For this example, let's select the option Sum of Working Hours by Designation and Click OK so we can now use it. You can see that the PivotTable has been created in a new worksheet with the number of working hours for each job description.

Now We have a Pivot Table created, let us understand the different components of a pivot table.

On the right side, you can see a PivotTable field list. From here, you can choose whatever field you want to add to your report. These are actually the headers of your source worksheet.

Below the field list, you can see four areas where you can drag and drop your field list: Filters, Columns, Rows, and Calculated Values.

If you drag and drop a field list into a column area, each unique value in that field becomes a column. And if you want to remove a field from the list, just uncheck that from the field list. Similarly, if you drop a field list into a row area, each unique value in that field becomes a row.

Adding a field to the filter area helps you filter your report based on each component of that field. The value field helps you do instant calculations for your selected fields.

If you are unsatisfied with what Excel recommends, you are free to design your own Pivot Table.

Custom Pivot Tables

With the help of a custom PivotTable, let us now find the total number of working hours for each job designation in each region.

First select all your data, then go to the Insert tab and select PivotTable from the Tables panel.

In the dialog box that opens, the Table/Range data box is automatically populated based on your selection. You also have the option to choose where you want your PivotTable to be placed, whether in a new worksheet or the existing one. I personally prefer building a pivot table in a new worksheet as it looks more organized, but you can also choose to have it in the same worksheet. Now you can select the New Worksheet Option and then Click OK.

Excel will then create a new worksheet with a blank PivotTable and a PivotTable's Fields list box on the right side of the sheet.

To determine the region-wise working hours, drag the designation field to the rows area. Then drag the Region field to the columns area. Finally, drag the Working Hours field to the calculated values area. You should be able to see that the PivotTable has automatically calculated the sum of working hours for each region under each designation.

Like any other spreadsheet, you can perform formatting on these cells in a pivot table. You can do center alignment, and number formatting if you need to.

There is one minor drawback with the Pivot Table, however not significant enough. The PivotTable summary doesn't get automatically updated whenever there's a change in the source data. But this can be easily updated by right-clicking on the summary table and then click on Refresh. Alternatively, you can press Alt F5 on your keyboard to refresh.

Formatting Your Pivot Table.

Formatting your PivotTables is as easy as formatting any other table. The Design tab has special formatting options for PivotTable. Please note that you will not be able to see the Design Tab unless you click on any of the cells within the pivot table.

The Design tab has three panels: Layout, PivotTable Style Options, and the PivotTable Styles.

These formatting options are very easy to understand and apply. For Example, If you do not want the subtotals to be displayed, you can go to the Subtotals option and choose Do Not Show Subtotals. You can bring it back by selecting any of these options. You also have different options under Grand Total to display grand totals. If you do not want to show the grand total value, you can turn it off by selecting Off for Rows and Columns or show grand total for only columns or only for rows.

This is just an example, but you can do much more; play around with the formatting features to achieve even more.

Adding Charts to Your Pivot Tables

Now that we have created a PivotTable and even formatted it to our liking, we can make It even more visually appealing by adding a chart to the pivot table.

To add a chart to your Pivot Table: Click on any cell in the Pivot Table, and go to the Insert tab. Under the Charts panel, click on the PivotChart option.

Here you can see different chart options from which you can choose the most suitable.

Let us select a Column Chart for this set of data. On Clicking Okay, you can notice a Column Chart has been added on the same worksheet where you have your PivotTable. The chart has category filters which can help you easily filter the items. When you apply a filter to the chart, the changes are also applied to the Pivot Table.

You can explore and play around with the different charts and filter functions available to create even great visuals.

Conclusion

In this article, we learned what Data Analytics is, the different types of Data Analytics, why Excel Pivot Tables should be a go-to tool for Data Analytics, creating our first recommended and custom pivot tables, formatting our pivot tables, and adding charts to them.

Excel Pivot Tables are such powerful tools that can help you understand your data and get more insights from the data. This is thanks to the many features and capabilities that it comes with. Excel Pivot Tables have many more functions adding to the ones covered in this article. I will cover them in my upcoming articles, but till then, keep learning and exploring more on the topic.

Thank you for reading, till next time.