Helpful Tips for Using Microsoft Excel

LEARNING & DEVELOPMENT, HR 1

Helpful Tips for Using

Microsoft Excel

LEARNING & DEVELOPMENT, HR        2 
 
Contents 
MS Excel .................................................................................................................... 3 
Get Started .............................................................................................................. 3 
Share and Print ....................................................................................................... 3 
Formulas and Functions ......................................................................................... 3 
Logical Functions .................................................................................................... 4 
Errors ...................................................................................................................... 5 
Import and Analyse ................................................................................................. 5 
Tables ..................................................................................................................... 6 
Sort and Filter ......................................................................................................... 6 
Charts ..................................................................................................................... 6 
Pivot Tables ............................................................................................................ 7 
Data Models ............................................................................................................ 7 
Enter Data ............................................................................................................... 8 
Format Data ............................................................................................................ 8 
Layout ..................................................................................................................... 9 
Validation .............................................................................................................. 10 
Customise ............................................................................................................. 10

LEARNING & DEVELOPMENT, HR        3 
 
MS Excel 
 
Get Started 
 
Create a Workbook in Excel 
Save you workbook to the One drive 
Analyse and Format 
 
Share and Print 
 
Collaborate in Excel 
Work on a file at the same time with co-authoring  
Password protect a workbook  
Protect a worksheet with or without a password  
Remove a password from a worksheet or workbook  
Print a worksheet in landscape or portrait orientation  
Print rows with column headers on top of every page  
Create and print mailing labels for an address list in Excel  
Set a specific print area   
Insert, move, or delete page breaks in a worksheet   
Print the top row on every page  
Preview worksheet pages before you print  
Make a worksheet fit the printed page  
Print gridlines in a worksheet  
Scale a worksheet for printing  
Repeat specific rows or columns on every printed page  
 
Formulas and Functions 
 
Overview of formulas in Excel  
Define and use names in formulas  

LEARNING & DEVELOPMENT, HR        4 
 
Create a simple formula  
Display or hide formulas   
Move or copy a formula  
Display or hide zero values  
Use Excel as your calculator  
Summing up ways to add and count Excel data  
Count how often a value occurs  
Find external references (links) in a workbook  
Calculate the average of a group of numbers  
Sum values based on multiple conditions  
Calculate percentages  
Subtract numbers  
Guidelines and examples of array formulas  
Write, insert, or change an equation  
YouTube: Learn basic formulas and functions with Microsoft Creators (English only)  
YouTube: Learn advanced formulas and Functions with Microsoft Creators (English 
only)  
SUM function  
AVERAGE function  
MAX function  
MIN function  
COUNT function  
All functions (alphabetical)  
All functions (categories)  
YouTube: Learn basic formulas and functions with Microsoft Creators (English only)  
YouTube: Learn advanced formulas and Functions with Microsoft Creators (English 
only)  
 
Logical Functions 
 
Logically compare values with the IF function  

LEARNING & DEVELOPMENT, HR        5 
 
IF function - nested formulas and avoiding pitfalls  
IFS function  
Count cells using a single criteria, use the COUNTIF function  
Count cells using multiple criteria, use the COUNTIFS function  
Count unique values among duplicates  
Add values that meet a single criteria with SUMIF function  
Add all values that meet multiple criteria with SUMIFS function  
Return the average of all cells that meet a single criteria with AVERAGEIF  
Return the average of all cells that meet multiple criteria with AVERAGEIFS  
AND-function  
OR-function  
NOT function  
All logical functions  
 
Errors 
 
How to avoid broken formulas  
How to correct #VALUE errors  
How to correct #NUM errors  
How to correct #NA error  
How to correct #REF! error  
How to correct #NAME? error  
How to correct a ##### error   
Hide error values and error indicators in cells  
Count unique values among duplicates  
All math functions  
 
Import and Analyse 
 
Getting Started with Get and Transform  
Import data from external data sources  

LEARNING & DEVELOPMENT, HR        6 
 
View and Manage Queries in a Workbook  
Add a query to an Excel worksheet  
Introduction to the Query Editor  
Shape data  
Edit Query Step Settings  
YouTube: Learn Get & Transform with Microsoft Creators (English only 
 
Tables 
 
Overview of Excel tables  
Create and format tables  
Copy a Word table into Excel  
Format an Excel table  
Resize a table by adding or removing rows and columns  
Use calculated columns in an Excel table  
Total the data in an Excel table  
YouTube: Learn tables with Microsoft Creators (English only)  
 
Sort and Filter 
 
Sort data in a range or table  
Filter data in a range or table  
Filter for unique values or remove duplicate values  
 
Charts 
 
Available chart types in Office  
Create a chart  
Add a pie chart  
Present your data in a Gantt chart  
Create a waterfall chart in Office 2016  

LEARNING & DEVELOPMENT, HR        7 
 
Create a histogram  
Change axis labels in a chart  
Select data for a chart  
Add axis titles to a chart  
Add a trend or moving average line to a chart   
YouTube: Learn charts and dashboards with Microsoft Creators (English only)  
 
Pivot Tables 
 
Create a PivotTable to analyze worksheet data  
Use multiple tables to create a PivotTable  
Calculate values in a PivotTable  
Format PivotTables by using the Field List to arrange fields  
Change the source data for a PivotTable  
Delete a PivotTable  
Create dynamic charts by using PivotCharts  
Filter data in a PivotTable  
Create a PivotTable timeline to filter dates  
YouTube: Learn PivotTables with Microsoft Creators (English only)  
 
Data Models 
 
Create a data model in Excel  
Import Data into Excel and Create a Data Model  
Power Pivot Overview and Learning  
Delete relationships between tables in a Data Model  
Measures in Power Pivot  
Data Model specification and limits  
Add worksheet data to a Data Model using a linked table   
Data Analysis Expressions DAX in Power Pivot  
Upgrade Power Pivot Data Models to Excel 2013 or Excel 2016   

LEARNING & DEVELOPMENT, HR        8 
 
YouTube: Learn data analysis with Microsoft Creators (English only)  
 
Enter Data 
 
Enter data manually in worksheet cells  
Fill data automatically in worksheet cells  
Turn Flash Fill on in Excel 2016  
Move or copy worksheets or worksheet data   
Create a list of sequential dates   
Create a custom list  
Select cells, ranges, rows, or columns on a worksheet  
Copy visible cells only  
Move or copy cells and cell contents  
Paste options  
Locate hidden cells on a worksheet  
Find or replace text and numbers on a worksheet  
Find and remove duplicates  
Learn how to use rows, columns, and cells with Microsoft Creators (English only)  
 
Format Data 
 
Available number formats  
Apply or remove cell borders  
Format numbers  
Format numbers as percentages  
Format a date the way you want  
Format text in cells  
Format numbers as dates or times  
Format numbers as currency  
Keeping leading zeros and large numbers  
Create or delete a custom number format  

LEARNING & DEVELOPMENT, HR        9 
 
Hide or show gridlines on a worksheet  
Change the case of text  
Convert numbers stored as text to numbers  
Wrap text in a cell  
Align or rotate text in a cell  
Use conditional formatting to highlight information  
Learn formatting with Microsoft Creators (English only)  
 
Layout 
 
Hide or show worksheets or workbooks   
Insert or delete cells, rows, and columns   
Hide or show rows or columns  
Move or copy cells  
Change the column width and row height   
Merge and unmerge cells   
Combine text from two or more cells into one cell  
Split text into different columns with the Convert Text to Columns Wizard  
Freeze panes to lock rows and columns  
Create, apply, or delete a custom view  
Lock or unlock specific areas of a protected worksheet  
Turn off Scroll Lock   
Split panes to lock rows or columns in separate worksheet areas  
Automatically number rows  
Group or ungroup shapes, pictures, or other objects   
Insert or delete a worksheet  
Split text into different columns with functions  
Combine two or more columns by using a function  
Consolidate data in multiple worksheets  
Learn formatting with Microsoft Creators (English only)  
Learn how to use rows, columns, and cells with Microsoft Creators (English only)  

LEARNING & DEVELOPMENT, HR 10

Validation

Apply data validation to cells