How to Analyze Large Data Sets in Excel (6 Effective Methods)

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.


Download Practice Workbook


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.

how to analyze large data sets in excel

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.

how to analyze large data sets in excel


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.

how to analyze large data sets in excel

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.

how to analyze large data sets in excel

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 Data in Excel Using Pivot Tables (9 Suitable Examples)


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])

how to analyze large data sets in excel
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.

how to analyze large data sets in excel

  • 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.

how to analyze large data sets in excel

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.

how to analyze large data sets in excel using power query editor

  • 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.

how to analyze large data sets in excel

  • 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.

how to analyze large data sets in excel

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.


Similar Readings


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.

how to analyze large data sets in excel using pivot chart

  • 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.

how to analyze large data sets in excel

  • 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.

how to analyze large data sets in excel using pivot 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.


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.

how to analyze large data sets in excel

  • 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.

how to analyze large data sets in excel using power pivot

  • 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

how to analyze large data sets in excel

  • 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.

how to analyze large data sets in excel

  • 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.

how to analyze large data sets in excel

  • 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.

how to analyze large data sets in excel method 6

  • 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.

how to analyze large data sets in excel

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: [Fixed:] Data Analysis Not Showing in Excel (2 Effective Solutions)


Practice Section

Here, I’m giving you the dataset of this article so that you can practice these methods on your own.

how to analyze large data sets in excel do it yourself


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. This will help me enrich my upcoming articles. For more queries, kindly visit our website ExcelDemy.


Related Articles

Nahian

Nahian

Hello, Nahian here! I do enjoy my efforts to help you understand some little basics on Microsoft Excel I've completed my graduation in Electrical & Electronic Engineering from BUET and I want to be a successful engineer in my life through intellect and hard-work, and that is the goal of my career.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo