Use Excel Tables
Microsoft Excel Proficient User
Collapse
Expand
-
Use Grouping Tools
- Group sheets
- Apply grouping to rows
- Apply grouping to columns
- Copy grouped data
- Insert subtotals
-
Use Formula Auditing Tools
- Use the Watch Window tool
- Use the Trace Precedents tool
- Use the Trace Dependents tool
- Display formula used / find a formula
- Use the Edit Links tool
-
Use Excel Tables
- Use the Format as Table tool
- Use structured references
- Insert a slicer
- Convert a table to a normal range of cells
-
Use Named Cells and Ranges
- Use a named cell range within a function
- Edit / delete a named range
- Use a named cell within a formula
- Insert a hyperlink to a named cell
- Insert a hyperlink to a named cell in a different workbook
- Assign a hyperlink to a button
-
Use an Advanced Filter
- Setup an advanced filter
- Filter using the And operator
- Filter using the Or operator
- Remove duplicates
- Extract filtered data
-
Use Data Validation
- Validate values entered
- Circle invalid data
- Validate dates entered
- Create a drop-down list using a cell range
- Create a drop-down list using a named range
- Create a drop-down list using comma separated values
- Create a cascading drop-down list
-
Use the IF Function
- Insert the IF Function to test a value cell
- Insert the IF Function to test a date cell
- Insert the IF Function to test a text cell
- Insert the IF Function to test a blank cell
- Insert the IF Function to verify a calculation
- Insert a nested IF Function
- Use the IFS Function
- Insert a nested LEN Function
-
Use the AND Function
- Insert the AND Function
- Insert a nested AND Function within an IF Function
- Insert a nested AND Function to test a range of numbers
- Insert a nested AND Function to test a range of dates
- Use a nested AND Function with the TODAY Function
-
Use the OR Function
- Insert the OR Function
- Insert a nested OR Function within an IF Function
-
Use Functions to Lookup Data
- Troubleshoot VLOOKUP Function errors
- Use a named range within a VLOOKUP Function
- Use a table name within a VLOOKUP Function
- Use the IFERROR Function to replace the #N/A Error
- Use a VLOOKUP Function between workbooks
- Use a Lookup Value linked to merged data
- Use a VLOOKUP Function within an IF Function
- Use a VLOOKUP Function within an ISERROR and IF Function
- Create a Combo Box linked to a VLOOKUP Function
- Use the INDEX Function and the MATCH Function
-
Use Conditional Formatting
- Use a formula to create a text rule
- Use the TODAY Function in a rule
- Use the AND Function in a rule
- Use the OR Function in a rule
- Apply a rule to an entire column
- Stop a rule if a condition is true
- Apply a rule to highlight cells in a row
-
Use Tools to Identify Blank Cells
- Use the Go To Special tool
- Use the COUNTIF Function
- Use the COUNTBLANK Function
- Use the IF Function
- Use the OR Function within the IF Function
- Use conditional formatting to highlight blank cells
-
Use Tools to Find Duplicates
- Use the Remove Duplicates tool
- Use conditional formatting to highlight duplicate data on a sheet
- Use the CONCATENATE Function to find duplicates
- Use the COUNTIF Function to find duplicates within a column
- Use the COUNTIF Function within an IF Function to find duplicates
- Use a PivotTable to find duplicates
-
Use PivotTables
- Resolve PivotTable defaulting to the COUNT Function
- Copy a PivotTable
- Use the GETPIVOTDATA Function
- Change report layout to Outline Form
- Customise a PivotTable
- Change the formatting of a value field
- Insert more than one values field
- Insert a calculated field
- Insert a calculated item
- Apply grouping to date fields
- Show report filter pages
- Insert a slicer
- Change slicer settings