Excel is becoming a daily necessity in many jobs and other areas of life. Having good excel skills is something you can boast about in your CV. In addition, knowing how to use the tool to its full extent can save you tremendous amounts of time. It is for these reasons that it is worth investing time and effort into learning how to use Excel so you can feel comfortable taking on a variety of tasks.
One coveted skill is the ability to use Pivot Tables, a type of table Excel provides that can help you analyze large sets of data quickly and efficiently. Once you have the basic design of your pivot table you can reuse this for multiple data sets of the same type – for example, analyzing sales in your store, clicks on articles in your blogs, and how this might be related to their reading time and many other utilizable features. The use of pivot tables can help bypass coding and complex mathematical work and with that make simple data analysis accessible to more users.
If you are finished using your pivot table, you might want to delete it. This is very easily done in Excel and only takes a few simple clicks. However, we remind you that you may want to keep the results of the analysis you perform using the table and occasionally you may want to keep the table for use with the second set of data – we will walk you through all these situations one at a time. You can also navigate to the heading most suitable to your needs on the right-hand side.
Table of Contents
Deleting the Pivot Table and the Data
Just want it all gone? Easy, you have two simple options here. If the pivot table is the only thing on the sheet, you can delete the whole sheet. If you have multiple tables on the sheet but want to delete one pivot table, then you will need to read on for detailed instructions. Firstly, to delete a whole worksheet in an excel document follow these steps:
- Hover your mouse pointer over the sheet you want to delete
- Right click
- In the menu that appears, select delete.
- A pop-up window will appear asking you to confirm that you want to delete the document permanently or cancel -> select delete.
Moving on to the case where you need to delete the table as an element of the sheet but not the whole sheet, here you’ll need to do the following:
- Click on any cell in your pivot table
- In the top ribbon, select PivotTable Analyze
- From here click Select
- A Pop-up menu will appear, select Entire PivotTable here
- The entire table should be highlighted now
- Hit delete on your keyboard -> the table should disappear.
Note that you can instead of steps 2-4 click Ctrl + A which should select and highlight the entire table for most -> note that if the configurations for select all are different on your device this may not work.
The image below walks you through steps 1-4.
Deleting the Pivot Table While Keeping the Data
So you’ve created a pivot table you’re proud of and analyzed some data – now you want your data to stand alone from the table so you can reuse it, no problem at all. You will use a simple trick to copy your data and paste it as numerical values rather than keeping the functions. To do this you will need to follow these steps:
- Select the data you want to keep (this could be the entire table or just the results from your analysis for example.
- Copy the data -> you can do this by clicking Ctrl + C or right-clicking and selecting copy in the menu that appears
- Navigate to where you want to store the data, this could be just a different column in the sheet you are working in, a different sheet where you are keeping results, or an entirely different workbook
- Right-click on the cell where you want to start pasting the data and select under paste options the folder with numbers under it to paste the data as values (see image)
Once you’ve done this you can delete the pivot table as described above, or you can clear the contents to reuse it as described below.
Deleting the Pivot Table Contents without losing the Design
If you need to repeat the same analysis for multiple data sets then you can save a lot of time by reusing an existing pivot table. To do this just clear the contents using the steps below once you’ve saved the results from your last analysis as described above.
- Click on any cell in your pivot table
- In the top ribbon, select PivotTable Analyze
- From here click Clear in the ribbon menu that appears below
- A Pop-up menu will appear, select Clear All
- The table should be restored to being completely empty and you can work from new source data.
