Use Functions to Modify Data
Microsoft Excel Intermediate User
Collapse
Expand
-
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