Paste the cells wherever you want and you will see it took only the visible cells. Right click on the cells you selected in step 1 and click COPY. You can also use Paste Special to paste the raw data with or without any formatting to have your information ready for reporting or plotting onto a chart. In Excel 2007, 2010, 2013 or Excel 2016, select the cells you are interested in. The data pasted is only the grouped totals and not the underlying or hidden data. Select the data in its grouped state as below:Ĭlick on the Select Visible Cells command in your QAT to only select the visible data, use Ctrl + C to copy the data to memory and then go to the sheet where you want to paste the data and use Ctrl + V to paste the data:

The command should now be available in your QAT on the top left of your screen. Click on Add to add it to your QAT and click on OK and OK. We can use it to return the SUM of the filtered range, or any of the following calculations. SUBTOTAL actually gives us eleven (11) different functions to choose from. This is great for tying out numbers in a filtered column to summary reports and pivot tables. Press S on your keyboard to jump to all commands beginning with 'S' and search for Select Visible Cells. The SUBTOTAL function will display the result of the visible cells only. Once the box below has opened, select All Commands from the first dropdown menu on the left side of the window. Go into the Customise Toolbar by clicking on the dropdown menu next to your Quick Access Toolbar and selecting More Commands. Firstly, we will need the command adding to the Quick Access Toolbar to 'Select Visible Cells'.

Excel 2016 select visible cells only