We can trick Excel using a quick way to link to many non adjacent cells. So instead of manually creating a summary section and clicking on each cell individually we can make use of the Filter and Paste Link tools.
Link to many non adjacent cells to create charting data
In the example below we need to create a chart and want all the totals (rows in grey) to be next to each other so we can (easily) create the chart.
Instead of manually
- clicking a cell,
- typing equals,
- finding and clicking on the right row,
- clicking enter and
- repeat
you can do the following:
- Switch on your Filter
- use the Filter so that you only see the rows you are interested in (see below, we used the filter to show only the cells that have a grey fill)
- Now highlight the cells that are grey in one of the columns (say column I) and click COPY. You will notice that the dashed lines indicate that it is not copying the cells that are hidden, only the visible cells (only works when the filter is on, with normal hidden rows it would include the hidden rows).
- Go to the first cell you want to use, click Paste Special and click on the Paste link button as shown below.
How the Paste Link works
If you look at the resultant formula in the cells, you will see that each one is looking at one of the grey rows without referencing the hidden cells (see below). As you will see it has put dollar signs on. This is easy to remove with the FIND/ REPLACE tool (replace all the $ with nothing).
Now you can copy these formula across and you have created a summary area of rows and rows of non adjacent cells in no time at all.
