3 ways you can use to create a Pivot Table

The first way is to use a worksheet range as a source.

The second way is to use a table as a source

The third way and Super Excel way is to use another file as a source.

The 3 ways are listed based on increasing level of expertise in Excel. Read on for the details.

What’s a Pivot Table?

It’s a function in Excel for you to get a quick summary of your data. Your data can be as small as 100 rows or as big and hundreds of thousands of rows.If you are currently tracking your numbers for daily use like operations, or preparing report for monthly use, you can use Pivot Table. You don’t have to know much to get started.

The first and most common way to create a Pivot Table.

Using a worksheet range as a source is a quick way to find out what your data contains. To use this first way, your data must be clean. This means that your data must be organized with the first row of the range set up as the header. And you cannot have 2 rows of header for the range.

You can use this method to start your journey with Pivot Table.

The second way to create a Pivot Table

Using a table instead of a worksheet range to create a Pivot Table allow you to get your updates instantly. This is because the new function called Table in Excel is intelligent enough include the rows you have just added in. All you have to do is perform a right click and refresh on your Pivot Table or refresh all your Pivot Tables at one go. This second method is very useful if you have weekly or a monthly reports that require update regularly. You don’t have to change the source range manually or re-create all your Pivot Tables again.

Learning how to create and use a table will also help you understand database rules too. This will train you to manage bigger sets of data in future. It is an important skill to have in the new digital economy.

The third way and the most advanced method to create a Pivot Table

Using Super Excel function “Power Query”, you can create a pivot table using another file as source. Unlike the first 2 methods, this method allows you to keep your data raw (in the downloaded format). When you download a new set of raw data, you don’t have to do any cleaning. All you have to do is to replace the old source file with the new file. And with a simple right click and refresh, the data is cleaned and summarized for you straight away. This method requires higher level of expertise than the first 2 methods. But it is the method that can save you time “massaging” the numbers.

Another benefit of power query is the ability to remove unnecessary columns which you don’t need. This means that your reports able to response faster to updates and you have a smaller file to send to your boss. Your boss will appreciate it if he/she can open the file and see the report instantly. And with a slicer, he can see different versions of the report without asking you to send the file to him/her again.

In the current digital economy, there is no time to keep cleaning data. The focus is on analysis.

Drop us an email below if you would like to learn any of the 3 ways. We have courses to help you save time.