The article will show you how to analyze large data sets in Excel. It’s very important to know the pros and cons of your business activities. It’s a dynamic process to keep the accounts and sales records of an organization. Because sales, buying, or exchange happen frequently in a period of time. So if you want to know the sales or profit records of the past 3 or 4 months from today, you may find a huge amount of data about them. Fortunately, Excel has some pretty cool features to help you on this matter. You can analyze those large data sets by parts in Excel and hence make your calculation easier.
6 Ways to Analyze Large Data Sets in Excel
In this article, I’m going to use the following dataset. Although this data set does not contain a large amount of data, we can show examples of how to analyze large data sets using it.
We have sales information about some electronic devices in this dataset.
It’s convenient to use this dataset as a table while analyzing them. To convert this data to a table
- First, select the data set and then go to Insert >> Table.
- After that, a dialog box will show up. Make sure you select My table has headers.
- Next, click OK.
By executing this operation, your dataset will convert to an Excel table.
1. Analyzing Large Data Sets with Pivot Table
There are some different ways to analyze large data sets in Excel. One of them is to use the Pivot Table from the Insert Tab. A Pivot Table helps us to see the information about sales of products by necessary columns and rows. Also, we can see them in months or years. Let’s go through the process below.
Steps:
- First, select the data table and then go to Insert >> Pivot Table.
- Later, a dialog box will show up. You can either choose New Worksheet or Existing Worksheet. Choose according to your convenience.
- After that, as we selected New Worksheet, we will find our Pivot Table features in a new worksheet. Drag the necessary ranges (Date, Price, Seller Name etc.) to the Pivot Table Fields. This will show you a summary of sales information in the Pivot Table.
- Here, I dragged the Date range to the Rows Field. It gave me another range which is the Month range automatically so that I can observe the sales information in months.
- After that, I also added the Seller Name in the Columns Field, because you may want to know which employee sold the most items and give him a bonus.
- And in the Values Field, I dragged the Price range to see how much sales happened in that period.
If you look at your Pivot Table after that, you will see the following information like this. According to the Fields that we have chosen, we will see the total sales by month, how much sales have been done by each seller, and the Grand Total of the sales by the end of the period.
- You can also see this information by dates. Just click on the plus icon (+) beside the Month Name.
Thus, you can analyze large data sets by using the Pivot Table. It’s really helpful when you work with almost everyday sales information.
Read More: How to Analyze Raw Data in Excel
2. Filter Command to Analyze Large Data Sets
Another way of analyzing large data sets in Excel is to use the Filter Command. This may help you filter the information based on the criteria you set.
Steps:
- First, select your range of the dataset and then go to Home >> Sort & Filter >> Filter
- After that, you will see the Filter Icon appear in the headers.
- However, if you want to see the total sales with VAT while using the Filter Command, you need to use the following formula.
=SUBTOTAL(9,Table13[Net Price])
The formula here uses the SUBTOTAL Function to return the total net sales of the filtered data.
- Now filter according to your wish. First, I’m showing you how to filter by month. Just click on the drop-down icon beside the Date header and check one or multiple months.
- After checking May and clicking OK, you will see the sales information for that month. You will also see the total sales with VAT in that month.
- Similarly, if you want to know how much sales have been done by an individual seller, just check his name and click OK.
- After that, you will see the corresponding sales information in the Excel sheet.
Thus, you can analyze large data sets by using the Filter Command.
3. Implementing Excel Power Query Editor to Analyze
The Power Query Editor can be very useful to analyze large data sets in Excel. Let’s go through the process below.
Steps:
- First, select your data table and then go to Data >> From Table/Range.
- After that, you will see your dataset in a Power Query Editor. This feature also has filter. We can apply them to summarize or see some particular records about the sales or employees.
- Although it’s not necessary, it’s convenient to omit the time part in the dates of the Date It’s an easy process, so I’m not going to show you the screenshot. Just go to the Transform Tab of the Power Query Editor and then select Date >> Date Only.
- Later, use the drop-down icon to filter your date. You can filter by individual dates or months.
- I preferred to filter by months so I selected May. There are options to filter for a period of time using the Custom Filter. You can use it if you want to see the sales records for multiple months or periods.
- Thereafter, you will see the sales for the month of May.
- If you want to load this data in an Excel sheet, just click on Close & Load.
After that, you will see the sales records for the month of May as a table in a new sheet.
You can also filter by the Seller Names or Price Range in the Power Query Editor and load them in a new sheet following the same procedure. Thus you can analyze large data sets by using the Power Query Editor.
4. Analyzing Large Data with Pivot Chart
If you want to analyze your data by a chart, you can effectively use the Pivot Chart. Let’s go through the description below.
Steps:
- First, follow the procedure of Section 1 to create a Pivot Table.
- Later, in the sheet of the Pivot Table, go to PivotTable Analyze >> PivotChart.
- Make sure you select any of the cells of the Pivot Table.
- After that, you will see a variety of options for the Pivot Chart. Select any of them and click OK. I chose a simple bar chart
- Thereafter, you will see monthly total sales in the Pivot Chart.
- In addition, if you want to see the sales by dates, just drag the Date range above the Month range in the Pivot Table Fields.
- Later, you will see the sales by dates in the chart.
Thus you can analyze large data sets by using the Pivot Chart. If your data contains yearly sales or transactions in a huge amount, you can use the Pivot Chart effectively for a better visualization.
Read More: How to Analyze Text Data in Excel
5. Using Power Pivot to Analyze Large Data Sets
You can also do the Pivot Table analysis of large data sets by using the Power Pivot feature. Please follow the instructions below.
Steps:
- First, close the current workbook and open a new Workbook and go to Power Pivot >> Manage.
- Next, in the Power Pivot window, select Home >> From Other Sources.
- After that, the Table Import Wizard will show up. In that wizard, select Excel File >> Next.
- Thereafter, browse your Excel Workbook where the data set is stored.
- Later, click Next.
- We will be working on the power pivot sheet of that workbook. So we checked it and clicked Finish.
- A confirmation message will show up, just click on Close.
- After that, this operation will upload the dataset of the selected sheet as a table in the Power Pivot
- The data you see does not have a proper header name. Rename the column headers by right-clicking and selecting Rename Column from the Context Menu.
- To do the Pivot Table analysis, we select the Pivot Table.
- At this time, a dialog box will appear. If you want your Pivot Table in the Existing Worksheet, select it and click OK. In my case, I chose the Pivot Table to appear in a New Worksheet.
- Next, you will see the Pivot Table Fields in a new sheet. Select Power Pivot and it will show you all the ranges it contains like Date, Price, VAT, etc.
- After that, follow this link of Section 1 to see how to analyze the data using a Pivot Table.
- You may use a chart to visualize monthly sales information. To see how analyze data with a Pivot Chart, kindly follow this link of Section 4.
6. Applying Analyze Data Feature
Last but not least, if you want all the data analysis in one sheet, you must use the Analyze Data Feature from the Data Tab. This will save you a huge amount of time. Let’s go through the process below.
Steps:
- First, select your table and select Analyze Data.
- Just after that, you will see the Analyze Data window at the right side of your Excel sheet.
Scroll down and you will get the options to analyze your data. Below here, you will see option for the Pivot Table analysis.
The following image shows the Bar Chart analysis option to compare the Price and Net Price of the product.
- Let’s just select the Net Price by Product chart to show you the analysis.
This operation will show you a bar chart analysis of the variation of Net Price by the Products.
You will find more other options if you scroll down. Select any of your choosing and proceed to analyze.
Thus you can analyze large data sets by using Analyze Data Feature.
Read More: How to Analyze Time Series Data in Excel
Practice Section
Here, I’m giving you the dataset of this article so that you can practice these methods on your own.
Download Practice Workbook
Conclusion
In the end, we can surmise the fact that you will learn very productive methods on how to analyze large data sets in Excel. If you don’t know how to analyze your large data sets, you will have to use manual processes which will cost you a lot of time. If you have any better methods or questions or feedback regarding this article, please share them in the comment box.
Related Articles
- How to Analyze Sales Data in Excel
- How to Analyze Likert Scale Data in Excel
- How to Analyze qPCR Data in Excel
- How to Analyze Time-Scaled Data in Excel
- How to Analyze Quantitative Data in Excel
- How to Analyze Qualitative Data in Excel
- How to Analyse Qualitative Data from a Questionnaire in Excel
- How to Convert Qualitative Data to Quantitative Data in Excel
<< Go Back to Data Analysis with Excel | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
