After inserting one or more levels of subtotals, use the outline numbers and symbols that appear on the left side of the list to collapse and expand the list to show just summary rows or summary rows along with detailed original data. Top copy a subtotaled list, press Alt+semicolon first to select only visible data.
- [Voiceover] On this worksheet called Expand/Collapse,…we're seeing the data used in the previous movie…with two layers of subtotals.…We're seeing subtotals for each department,…here's the ADC total, for example,…and within that we're seeing totals based on the status.…And in a larger list like Admin Training here…it's perhaps more obvious.…And you might have seen these same actions taken…in the previous movie.…But now let's talk about the idea…that when we do see these we also have…what are called outlining symbols…over in the left-hand side of our screen,…and also outlining numbers above that.…
One, two, three, and four, in this case.…At the moment we're seeing the detail…for each record as we saw it in the original list,…which is on the previous worksheet to the left.…And we're actually using level number four here,…although I haven't really selected that.…If we select the level number three,…we are now seeing a collapsed view of the data,…we are only seeing the subtotals for status…and subtotals for department.…
- Prepping data for analysis
- Multiple-key sorting
- Sorting by rows or by columns
- Setting single- and multi-level subtotals
- Using text, numeric, and date filters
- Creating custom filters
- Filtering tables using slicers
- Using Advanced Filter
- Eliminating duplicate data
- Using SUMIF and COUNTIF functions for quick data analysis
- Working with the database functions such as DSUM and DMAX
Skill Level Intermediate
Working with Real-Time Data in Excelwith Chris Dutton1h 18m Intermediate
Learning Microsoft Power BI Desktop (2016)with Gini von Courter2h 52m Intermediate
1. Data Preparation
2. Sorting Data
3. Creating Automatic Subtotals in Sorted Lists
4. Filtering Data
5. Advanced Filter
6. Eliminating Duplicate Data
7. Data Analysis Tools
- Mark as unwatched
- Mark all as unwatched
Are you sure you want to mark all the videos in this course as unwatched?
This will not affect your course history, your reports, or your certificates of completion for this course.Cancel
Take notes with your new membership!
Type in the entry box, then click Enter to save your note.
1:30Press on any video thumbnail to jump immediately to the timecode shown.
Notes are saved with you account but can also be exported as plain text, MS Word, PDF, Google Doc, or Evernote.