How to avoid selection of range in excel that are grouped/hidden and only select the visible ranges.
We often come in situation where we have a range of data say quarterly reviews with annual reviews of sales. In such cases mostly the quarterly data is grouped/hidden to show that they are easily recognized separately from the annuals figures. However they is a problem when we want to copy and paste the annual data for some other calculation. In this case if we copy paste we would see the whole of quarterly and annual data selected together instead of only annual data. Therefore to avoid this we have a solution.
Step 1. Group all the quarterly data as shown below. Use ALT+D+G+G to group for a selected rage and then click on the + sign on the top of the sheet to hide the quarterly data.
Step 2: Select the data you want to copy for annuals, in this case let’s say all of the data. After selection use the ALT+ Semicolon (;) on the key board. This will only highlight the selected ranges which are visible. Alternatively you can use Function Key F5, this will popup the Go To window. Then click on “Special” on the Go To window and you will see a list of radio button to select from. Use the “Visible cells only” and click OK.
Step 3: Now once you have the highlighted range you can copy paste which will only paste the annuals value.