2 great features to Hide data in Excel

Oct 28, 2018 | Excel Hints and Tips

When you work with large spreadsheets the ability to hide data is useful to gain more space for the most important information or to get to a point where the data can be printed more sensibly.  There are a couple of options that can help you do this.

Hide and Unhide

HideThis method is the most popular and is great if you are hiding your data and will then generally keep it hidden most of the time.

Right click on the row (number) or column (letter) heading that you want to hide.  Choose Hide.  Multiple columns or rows can be hidden at the same time by selecting blocks of data.  This can be performed by using the shift key or dragging across multiple row or column headings.

The best way to unhide the data is to select the row or column headings either side of the data you want to unhide.  Then right-click on what you have just selected and choose unhide.  You can tell there is hidden data because the row and column headings are not consecutive.

Using Group and Outline




Grouping can either be performed manually or by using the auto outline option.  This relies on the calculations on the spreadsheet to determine which rows and columns may require hiding.  It is a better option than hide and unhide if the data is going to be hidden and shown again on a regular basis.  The data can be expanded and contracted by using the outline tools that appear above and to the left of the main spreadsheet.  They contain ‘+’ and ‘-‘ symbols which can be clicked on to determine the level of detail to be viewed.  There are also numbers that appear along with the outlining tools.   These allow the data to be shown to a particular level.

Auto outline

If it works then the most efficient way of outlining a document is using auto outline.  The calculations on the spreadsheet are used to determine which rows and/or columns will be grouped to enable them to be hidden/unhidden quickly.

For example, if the function SUM is used to add monthly data to produce a quarterly set of figures then auto outline is likely to suggest the monthly figures are hidden.  To perform this action, click on one cell in the data.  Select the drop-down on the group icon on the data tab. Select auto outline.  If this doesn’t work then the outline can be removed.  Click on one cell in the data then choose the drop down from ungroup then select clear outline.

Manual Grouping

Not all data lends itself to auto outline.  In this case any groupings must be set up manually.  The first set of rows or columns to group are selected.  The data to be grouped must be contiguous.  By choosing group on the data tab these are now grouped.  The process is repeated for any other sets of rows and columns required.

Which method you use will be determined by how you need to work with your data,

Want to learn more time-saving tips then have a look at the Excel courses and content we can deliver or to discuss your requirements further email lara@laramellortraining.co.uk