Microsoft Excel Intermediate Lessons

 

---

 

Customize Views and Settings

Customize the Quick Access Toolbar
Change magnification by using Zoom tools
Hide and unhide columns
Hide and unhide rows
Hide and unhide worksheets
Use the Custom Views tool
Use the Freeze Panes tool
Use the View Side by Side tool
Use the Arrange All tool
Use the New Window tool
Change Excel options

Configure Worksheets and Workbooks for Distribution

Set and clear a print area
Use custom scaling options
Use the Page Break Preview tool
Display repeating row titles on multipage worksheets
Display repeating column titles on multipage worksheets

Insert Data in Cells and Ranges

Use insert options
Use Auto Fill options
Use the double-click fill trick
Use advanced replace options
Use paste options to paste values
Use paste options to transpose data
Use paste options to insert data as a picture
Use paste special to paste comments
Use paste special operation options
Use paste special skip blanks
Insert a hyperlink to a file
Create a drop-down list using a cell range / static data

Organise and Visualise Data

Create an Excel table from a cell range
Sort data by a column / by multiple columns
Apply a text filter / number filter / date filter
Apply grouping to rows to outline data
Apply grouping to columns to outline data
Insert subtotals
Amend / delete conditional formatting rules
Link conditional formatting to a cell
Apply more than 1 rule
Insert / edit / clear sparklines
Create a basic chart

Insert Formula Using Cell References

Insert a formula to link cells within a sheet
Insert a formula to link cells between sheets
Insert a formula using data from different sheets
Use an absolute cell reference

Use the SUM Function

Edit the range of cells used in the SUM function
Use the SUM function to add non-adjacent cells
Use the SUM function to add a range of cells in a different sheet
Use the SUM function to add a column in a different sheet

Use the SUMIF Function

Insert the SUMIF function using text criteria
Insert the SUMIF function using a cell reference as the criteria

Use the IF Function

Use the IF function to display a message
Use the IF function to display a blank message
Use an absolute cell reference with the IF function

Use the VLOOKUP Function

Use the VLOOKUP function to lookup data
Use the VLOOKUP function to compare 2 columns

Use Functions to Modify Data

Use the TRIM function
Use the RIGHT function
Use the LEFT function
Use the CONCATENATE function

Modify and Insert a PivotTable

Modify an existing PivotTable
Refresh a PivotTable
Prepare data to be used in a PivotTable
Insert a PivotTable in an existing worksheet
Change the Summarize Values By setting
Use Show Details to display data linked to a value
Insert a PivotChart

Use the Quick Analysis Tool

Apply conditional formatting
Insert a chart
Insert totals
Insert a table
insert sparklines