Data Subtotals with visible cells – using the visible cells only option with the Data Subtotals feature. Have you used either before?
This week’s hint and tip is about data subtotals with visible cells in Excel. We are looking at how you can use the data subtotals feature in Excel but alongside the visible cells option too. This isn’t all fully covered in our courses, so we decided to do a hint and tip on it. We are going to go through it now below.
Data Subtotal
Data Subtotal is a very useful function in Excel. This function is covered on our Intermediate Excel training course but we also cover it in more detail in another blog, which you can be found here. It is also covered in another blog when creating a chart from a Subtotalled set of data which can be found here.
Copying a Subtotalled list of data
It is quite usual to wish to keep the set of subtotalled data for another use and so many people may copy this set to another sheet using Outline 2 (summarised view). The problem lies with the fact that the copy takes across all rows including the hidden rows – we just want what we can see.
Open the attached file to see what’s going on. On the first sheet Data Sheet it already has subtotals applied. You can also download the spreadsheet in the video by clicking here (clicking here will download a copy to your computer so you can try it out!). ** In the video, to save time when copying, you can select the cells first then carry on with the steps for Find and Select, Go To Special, etc. This will then just select the visible cells in the cells you highlighted rather than the whole row too (this will just make the copying process a bit quicker) **
If this set of data is just copied straight across to another sheet then the hidden rows are also copied so you must go to the Home Tab, Find and Select, Go To Special and tick Visible cells only.
The video below goes through Data Subtotals and copying the subtotalled list that you can see in the spreadsheet above. We hope that you find the video useful and enjoy learning about it!
Take a look below at the video to find out more!
We hope you liked this hint and tip on Data Subtotals with visible cells, why not take a look at our previous one on the Array Spill feature in Excel?
For details on Excel courses visit our Excel training page.