MS Excel Intermediate User
Online Course
of
lessons completed
0%
MS Excel Intermediate User
-
Customise Views and Settings
- Introduction
- 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 Arrange All tool
- Use the View Side by Side 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
- Create a drop-down list using 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
- Insert a PivotTable in an existing worksheet
- Prepare data to be used in a PivotTable
- 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
Insert a formula using data from different sheets
EXERCISE Download the file used in the video. In the Sales less Expenses sheet insert the formula in cell B2 as shown in the video. Change the Sales value and the Expenses value – the formula you have entered should change accordingly. Close the file (you don’t need to save the changes). Download the exercise […]
Insert a formula using data from different sheets
Please purchase the course before starting the lesson.