Difficulty: Easy
Issue: Worksheets with a lot of content can sometimes feel overwhelming and even become difficult to read. Fortunately, Excel can organize data in groups, allowing you to easily show and hide different sections of your worksheet. You can also summarize different groups using the Subtotal command and create an outline for your worksheet.
Tip 1:To group rows or columns
Select the rows or columns you want to group. In this example, we'll select columns A, B, and C.
Select the Data tab on the Ribbon, then click the Group command.
The selected rows or columns will be grouped. In our example, columns A, B, and C are grouped together.
To ungroup data, select the grouped rows or columns, then click the Ungroup command.
The group will be hidden. To show a hidden group, click the Show Detail button
Tip 2: Creating subtotals
Difficulty: Medium
Issue: In our example, we will use the Subtotal command with a T-shirt order form to determine how many T-shirts were ordered in each size (Small, Medium, Large, and X-Large). This will create an outline for our worksheet with a group for each T-shirt size and then count the total number of shirts in each group.
First, sort your worksheet by the data you want to subtotal. In this example, we will create a subtotal for each T-shirt size, so our worksheet has been sorted by T-shirt size from smallest to largest.
Select the Data tab, then click the Subtotal command.
The Subtotal dialog box will appear. Click the drop-down arrow for the At each change in: field to select the column you want to subtotal. In our example, we'll select T-Shirt Size.
Click the drop-down arrow for the Use function: field to select the function you want to use. In our example, we'll select COUNT to count the number of shirts ordered in each size.
In the Add subtotal to: field, select the column where you want the calculated subtotal to appear. In our example, we'll select T-Shirt Size. When you're satisfied with your selections, click OK.
The worksheet will be outlined into groups, and the subtotal will be listed below each group. In our example, the data is now grouped by T-shirt size, and the number of shirts ordered in that size appears below each group.
Tip 3: To view groups by level
When you create subtotals, your worksheet it is divided into different levels. You can switch between these levels to quickly control how much information is displayed in the worksheet by clicking the Level button ( 1 / 2/ 3 etc.)
Click the lowest level to display the least detail. In our example, we'll select level 1, which contains only the grand count, or total number of T-shirts ordered.
Click the next level to expand the detail. In our example, we'll select level 2, which contains each subtotal row but hides all other data from the worksheet.
Click the highest level to view and expand all of your worksheet data. In our example, we'll select level 3
You can also use the Show and Hide Detail buttons to show and hide the groups within the outline.
Full Excel Masterclass Course
If you want to learn more about Excel, please check out our full excel course here (you can try the free course first here)
If you want to learn more about Excel, regardless of your skill level, we offer free and premium Excel training courses on our website, www.meritexcel.com. Our Excel masterclass covers:
- all the key formulas you need (sumif, index, match, offset, eomonth, concatenate, lookup, if, or, sumproduct, date and time formulas, trim and much more)
- How navigate and use Excel effectively (viewing in 2 windows, paste special, locking cells, removing duplicates, autofill, freezing rows, fill blanks)
- Best practice techniques in Excel (formatting, checks, grouping, formula auditing, structure)
- How to present and manipulate data (Pivot tables, charts, editing charts, filtering and sorting)
- How to create engaging and dynamic spreadsheets (drop-downs, protect data, goal seek and data tables)
- And much, much more!
We offer a free, limited course which you can view here OR you can purchase the full, Excel masterclass here, which has over 20+ hours of amazing Excel content.
Comments