When you need to group data in Excel or filter and copy and paste the results to another sheet we often find that it pastes everything including the hidden data. So we will add a command to our Quick Access Toolbar to paste only the visible data.
[ Video tutorial produced by Activia Training and purchased by ZandaX ]
Firstly, we will need the command adding to the Quick Access Toolbar to "Select Visible Cells".
Go into the Customise Toolbar by clicking on the dropdown menu next to your Quick Access Toolbar and selecting More Commands. Once the box below has opened, select All Commands from the first dropdown menu on the left side of the window. Press S on your keyboard to jump to all commands beginning with "S" and search for Select Visible Cells. Click on Add to add it to your QAT and click on OK and OK.
The command should now be available in your QAT on the top left of your screen. Select the data in its grouped state as below:
Click 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 data pasted is only the grouped totals and not the underlying or hidden data. 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.
Want Better Excel Skills?
We have online courses with full 12-months' access. RRP from $49 limited time offer just $12.00
Get a solid foundation in Project software to create solid, resilient project plans.
You don't need prior experience with Project: just be able to use a PC with Microsoft Windows.
Microsoft Project Advanced
The Advanced course takes you to a level that will put you in complete control of your projects.
You should, of course, be fully conversant with the skills and concepts taught in the Introduction course.
Become a Visio master with our two intensive but very easy-to-follow CPD certified Microsoft Visio courses.