Excel
Lessons
1
MS Excel – 1 – Overview of Office 365
- 7 minutes
- Text Reading
2
3
MS Excel – 2 – Create a workbook in Excel – Lab
Create a workbook Open Excel. Select Blank workbook.Or, press Ctrl+N. Enter data To manually enter data: Select an empty cell, such as A1, and then type text or a number. Press Enter or Tab to move…
- Text Reading
4
MS Excel – 3 – Analyze and format in Excel – Lab
Analyze and format in Excel Automatically fill a column with Flash Fill For example, automatically fill a First Name column from a Full Name column. In the cell under First…
- Text Reading
5
MS Excel – 4 – Collaborate in Excel – Lab
Collaborate in Excel Share your workbook with others Select Share on the ribbon.Or, select File > Share. Note: If your file is not already saved to OneDrive, you’ll be prompted to upload your file…
- Text Reading
6
7
MS Excel – 5 – Create a new workbook – Lab
Create a new workbook A workbook is a file that contains one or more worksheets to help you organize data. You can create a new workbook from a blank workbook or a…
- Text Reading
8
9
MS Excel – 6 – Insert or delete a worksheet – Lab
Insert or delete a worksheet In Excel, you can easily insert, rename, and delete worksheets in your workbook. Insert a worksheet Select the plus icon at the bottom of the screen.…
- Text Reading
10
MS Excel – 7 – Move or copy worksheets or worksheet data – Demo
Move or copy worksheets or worksheet data
- Text Reading
11
MS Excel – 7 – Move or copy worksheets or worksheet data – Lab
Move or copy worksheets or worksheet data You can move or copy a worksheet in the same workbook to organize your workbook exactly how you want. You can use the Move…
- Text Reading
12
MS Excel – 8 – Print a worksheet or workbook – Lab
Print a worksheet or workbook You can print entire or partial worksheets and workbooks, one at a time, or several at once. And if the data that you want to…
- Text Reading
13
MS Excel – 9 – Use Excel as your calculator – Demo
Subtract in Excel Multiply in Excel Divide in Excel
- Text Reading
14
MS Excel – 9 – Use Excel as your calculator – Lab
Use Excel as your calculator Instead of using a calculator, use Microsoft Excel to do the math! You can enter simple formulas to add, divide, multiply, and subtract two or…
- Text Reading
15
MS Excel – 10 – Fill data automatically in worksheet cells – Demo
Fill data automatically in worksheet cells
- Text Reading
16
MS Excel – 10 – Fill data automatically in worksheet cells – Lab
Fill data automatically in worksheet cells Select one or more cells you want to use as a basis for filling additional cells. For a series like 1, 2, 3, 4, 5…, type…
- Text Reading
17
MS Excel – 11 – Insert or delete rows, and columns – Demo
Insert or delete rows, and columns
- Text Reading
18
MS Excel – 11 – Insert or delete rows, and columns – Lab
Insert or delete rows, and columns Insert or delete a column Select any cell within the column, then go to Home > Insert > Insert Sheet Columns or Delete Sheet Columns. Alternatively, right-click the top of the column,…
- Text Reading
19
20
MS Excel – 12 – Select cell contents in Excel – Lab
Select cell contents in Excel Select one or more cells Click on a cell to select it. Or use the keyboard to navigate to it and select it. To select…
- Text Reading
21
MS Excel – 13 – Freeze panes to lock rows and columns – Demo
Freeze panes to lock rows and columns
- Text Reading
22
MS Excel – 13 – Freeze panes to lock rows and columns – Lab
Freeze panes to lock rows and columns Freeze rows or columns Freeze the first column Select View > Freeze Panes > Freeze First Column. The faint line that appears between Column A and B shows…
- Text Reading
23
24
MS Excel – 14 – Hide or show rows or columns – Lab
Hide or show rows or columns Hide columns Select one or more columns, and then press Ctrl to select additional columns that aren’t adjacent. Right-click the selected columns, and then…
- Text Reading
25
MS Excel – 15 – Filter for unique values or remove duplicate values – Lab
Filter for unique values or remove duplicate values In Excel, there are several ways to filter for unique values—or remove duplicate values: To filter for unique values, click Data > Sort & Filter…
- Text Reading
26
MS Excel – 16 – Split text into different columns with the Convert Text to Columns Wizard – Demo
Split text into different columns with the Convert Text to Columns Wizard
- Text Reading
27
MS Excel – 16 – Split text into different columns with the Convert Text to Columns Wizard – Lab
Split text into different columns with the Convert Text to Columns Wizard Select the cell or column that contains the text you want to split. Select Data > Text to Columns. In…
- Text Reading
28
MS Excel – 17 – Create a list of sequential dates – Demo
Create a list of sequential dates
- Text Reading
29
MS Excel – 17 – Create a list of sequential dates – Lab
Create a list of sequential dates Use the Fill Handle Select the cell that contains the first date. Drag the fill handle across the adjacent cells that you want to…
- Text Reading
30
MS Excel – 18 – Move or copy cells and cell contents – Demo
Move cells Copy cells in your worksheet
- Text Reading
31
MS Excel – 18 – Move or copy cells and cell contents – Lab
Move or copy cells and cell contents Use Cut, Copy, and Paste to move or copy cell contents. Or copy specific contents or attributes from the cells. For example, copy the resulting value of…
- Text Reading
32
MS Excel – 19 – Change the column width or row height in Excel – Demo
Change the column width or row height in Excel
- Text Reading
33
MS Excel – 19 – Change the column width or row height in Excel – Lab
Change the column width or row height in Excel You can manually adjust the column width or row height or automatically resize columns and rows to fit the data. Note: The…
- Text Reading
34
MS Excel – 20 – Find or replace text and numbers on a worksheet – Lab
Find or replace text and numbers on a worksheet Use the Find and Replace features in Excel to search for something in your workbook, such as a particular number or…
- Text Reading
35
36
MS Excel – 21 – Merge and unmerge cells – Lab
Merge and unmerge cells You can’t split an individual cell, but you can make it appear as if a cell has been split by merging the cells above it. Merge…
- Text Reading
37
38
MS Excel – 22 – Apply data validation to cells – Lab
Download example workbook with all data validation examples Apply data validation to cells You can use data validation to restrict the type of data or the values that users enter…
- Text Reading
39
MS Excel – 23- Import or export text (.txt or .csv) files – Lab
Import or export text (.txt or .csv) files There are two ways to import data from a text file with Excel: you can open it in Excel, or you can…
- Text Reading
40
MS Excel – 24 – Available number formats in Excel – Demo
Available number formats in Excel
- Text Reading
41
MS Excel – 24 – Available number formats in Excel – Lab
Available number formats in Excel In Excel, you can format numbers in cells for things like currency, percentages, decimals, dates, phone numbers, or social security numbers. Select a cell or…
- Text Reading
42
MS Excel – 25 – Use conditional formatting to highlight information – Demo
Use conditional formatting Conditional formatting provides visual cues to help you quickly make sense of your data. For example, it’ll clearly show highs and lows, or other data trends based…
- Text Reading
43
MS Excel – 25 – Use conditional formatting to highlight information – Lab
Download “Conditional formatting” examples in Excel Use conditional formatting to highlight information Use conditional formatting to help you visually explore and analyze data, detect critical issues, and identify patterns and…
- Text Reading
44
45
MS Excel – 26 – Align or rotate text in a cell – Lab
Align or rotate text in a cell If you want to change the way data appears in a cell, you can rotate the font angle, or change the data alignment.…
- Text Reading
46
47
MS Excel – 27 – Change the format of a cell – Lab
Change the format of a cell You can apply formatting to an entire cell and to the data inside a cell—or a group of cells. One way to think of…
- Text Reading
48
49
MS Excel – 28 – Copy cell formatting – Lab
Copy cell formatting Select the cell with the formatting you want to copy. Select Home > Format Painter. Drag to select the cell or range you want to apply the formatting to. Release…
- Text Reading
50
51
MS Excel – 29 – Add a watermark in Excel – Lab
Add a watermark in Excel Microsoft Excel doesn’t come with a built-in watermark button. But you can use an image, like a logo, to create a watermark. Create a watermark…
- Text Reading
52
MS Excel – 30 – Display or hide zero values – Lab
Display or hide zero values You may have a personal preference to display zero values in a cell, or you may be using a spreadsheet that adheres to a set…
- Text Reading
53
54
MS Excel – 31 – Create a custom number format – Lab
Create a custom number format Select the numeric data. On the Home tab, in the Number group, click the Dialog box launcher. Select Custom. In the Type list, select an existing format, or type a new one…
- Text Reading
55
56
MS Excel – 32 – Overview of formulas in Excel – Lab
Download the attached “Formulas Tutorial Workbook” Overview of formulas in Excel Get started on how to create formulas and use built-in functions to perform calculations and solve problems. Create a…
- Text Reading
57
58
MS Excel – 33 – VLOOKUP – Lab
Download the attached “Formulas Tutorial Workbook” VLOOKUP Use VLOOKUP when you need to find things in a table or range by row. Select a cell. Type =VLOOKUP( and then select…
- Text Reading
59
60
MS Excel – 34 – SUM function – Lab
Download the attached “Formulas Tutorial Workbook” SUM function The SUM function adds values. You can add individual values, cell references or ranges or a mix of all three. For example: =SUM(A2:A10) Adds the…
- Text Reading
61
62
MS Excel – 35 – COUNTIF function – Lab
Download the attached “Formulas Tutorial Workbook” Use COUNTIF, one of the statistical functions, to count the number of cells that meet a criterion; for example, to count the number of times…
- Text Reading
63
64
MS Excel – 36 – IF function – Lab
Download the attached “Formulas Tutorial Workbook” IF function The IF function is one of the most popular functions in Excel, and it allows you to make logical comparisons between a…
- Text Reading
65
66
MS Excel – 37 – IFS – Lab
Download the attached “Formulas Tutorial Workbook” IFS Use the IFS function to check whether one or more conditions are met and returns a value that corresponds to the first TRUE…
- Text Reading
67
68
MS Excel – 38 – SUMIF – Lab
Download the attached “Formulas Tutorial Workbook” SUMIF Use the SUMIF function to sum the values in a range that meet criteria that you specify. Select a cell. Type =SUMIF(. Click…
- Text Reading
69
70
MS Excel – 39 – SUMIFS – Lab
SUMIFS Use SUMIFS to test multiple conditions and return a value based on those conditions. For example, you could use SUMIFS to sum the number of a products sold by…
- Text Reading
71
MS Excel – 40 – Automatically number rows – Lab
Automatically number rows Unlike other Microsoft Office programs, Excel does not provide a button to number data automatically. But, you can easily add sequential numbers to rows of data by…
- Text Reading
72
MS Excel – 41 – Calculate the difference between two dates – Lab
Download Date Calculation examples Calculate the difference between two dates Use the DATEDIF function when you want to calculate the difference between two dates. First put a start date in…
- Text Reading
73
MS Excel – 42 – Define and use names in formulas – Demo
Define and use names in formulas
- Text Reading
74
MS Excel – 42 – Define and use names in formulas – Lab
Define and use names in formulas By using names, you can make your formulas much easier to understand and maintain. You can define a name for a cell range, function,…
- Text Reading
75
MS Excel – 43 – Combine text from two or more cells into one cell – Demo
Combine text from two or more cells into one cell
- Text Reading
76
MS Excel – 43 – Combine text from two or more cells into one cell – Lab
Combine text from two or more cells into one cell You can combine data from multiple cells into a single cell using the Ampersand symbol (&) or the CONCAT function.…
- Text Reading
77
78
MS Excel – 44 – Create and format tables – Lab
Create and format tables You can create and format a table, to visually group and analyze data. Insert a table in your spreadsheet. Select a cell within your data. Select Home > Format…
- Text Reading
79
80
MS Excel – 45 – Sort data in a table – Lab
Sort data in a table Sorting is one of the most common tools for data management. In Excel, you can sort your table by one or more columns, by ascending…
- Text Reading
81
82
MS Excel – 46 – Filter data in a range or table – Lab
Filter data in a range or table Use AutoFilter or built-in comparison operators like “greater than” and “top 10” in Excel to show the data you want and hide the…
- Text Reading
83
MS Excel – 47 – Total the data in an Excel table – Demo
Total the data in an Excel table
- Text Reading
84
MS Excel – 47 – Total the data in an Excel table – Lab
Total the data in an Excel table You can quickly total data in an Excel table by enabling the Total Row option, and then use one of several functions that are provided…
- Text Reading
85
86
MS Excel – 48 – Use slicers to filter data – Lab
Use slicers to filter data Slicers provide buttons that you can click to filter tables, or PivotTables. In addition to quick filtering, slicers also indicate the current filtering state, which makes it…
- Text Reading
87
MS Excel – 49 – Create a chart from start to finish – Demo
Create a chart from start to finish
- Text Reading
88
MS Excel – 49 – Create a chart from start to finish – Lab
Create a chart from start to finish Charts help you visualize your data in a way that creates maximum impact on your audience. Learn to create a chart and add…
- Text Reading
89
90
MS Excel – 50 – Add or remove titles in a chart – Lab
Add or remove titles in a chart To make a chart easier to understand, you can add chart title and axis titles, to any type of chart. Axis titles are…
- Text Reading
91
MS Excel – 51 – Show or hide a chart legend or data table – Demo
Show or hide a chart legend or data table
- Text Reading
92
MS Excel – 51 – Show or hide a chart legend or data table – Lab
Show or hide a chart legend or data table You can hide or show the legend of a chart. You can also show a data table for a line chart,…
- Text Reading
93
MS Excel – 52 – Add or remove a secondary axis in a chart in Excel – Demo
Add or remove a secondary axis in a chart in Excel
- Text Reading
94
MS Excel – 52 – Add or remove a secondary axis in a chart in Excel – Lab
Add or remove a secondary axis in a chart in Excel When the numbers in a chart vary widely from data series to data series, or when you have mixed…
- Text Reading
95
MS Excel – 53 – Add a trend or moving average line to a chart – Demo
Add a trend or moving average line to a chart
- Text Reading
96
MS Excel – 53 – Add a trend or moving average line to a chart – Lab
Add a trend or moving average line to a chart Add a trendline to your chart to show visual data trends. Add a trendline Select a chart. Select the +…
- Text Reading
97
98
MS Excel – 54 – Analyze your data instantly – Lab
Analyze your data instantly Excel’s Quick Analysis button lets you instantly create different types of charts, including line and column charts, or add miniature graphs called sparklines. Select a range…
- Text Reading
99
MS Excel – 55 – Update the data in an existing chart – Demo
Update the data in an existing chart
- Text Reading
100
MS Excel – 55 – Update the data in an existing chart – Lab
Update the data in an existing chart If you need to change data in a chart, you can do it from its source. Create a chart from a table Select…
- Text Reading
101
MS Excel – 56 – Use sparklines to show data trends – Demo
Use sparklines to show data trends
- Text Reading
102
MS Excel – 56 – Use sparklines to show data trends – Lab
Use sparklines to show data trends A sparkline is a tiny chart in a worksheet cell that provides a visual representation of data. Use sparklines to show trends in a…
- Text Reading
103
MS Excel – 57 – Create a PivotTable to analyze worksheet data – Demo
Create a PivotTable to analyze worksheet data
- Text Reading
104
MS Excel – 57 – Create a PivotTable to analyze worksheet data – Lab
Create a PivotTable to analyze worksheet data A PivotTable is a powerful tool to calculate, summarize, and analyze data that lets you see comparisons, patterns, and trends in your data.…
- Text Reading
105
MS Excel – 58 – Use the Field List to arrange fields in a PivotTable – Demo
Use the Field List to arrange fields in a PivotTable
- Text Reading
106
MS Excel – 58 – Use the Field List to arrange fields in a PivotTable – Lab
Use the Field List to arrange fields in a PivotTable After you create a PivotTable, you’ll see the Field List. You can change the design of the PivotTable by adding and…
- Text Reading
107
MS Excel – 59 – Group or ungroup data in a PivotTable – Demo
Group or ungroup data in a PivotTable
- Text Reading
108
MS Excel – 59 – Group or ungroup data in a PivotTable – Lab
Group or ungroup data in a PivotTable Grouping data in a PivotTable can help you show a subset of data to analyze. For example, you may want to group an…
- Text Reading
109
110
MS Excel – 60 – Filter data in a PivotTable – Lab
Filter data in a PivotTable PivotTables are great for taking large datasets and creating in-depth detail summaries. Sometimes, you want the added flexibility of being able to further filter your data…
- Text Reading
111
112
MS Excel – 61 – Create a PivotChart – Lab
Create a PivotChart Sometimes it’s hard to see the big picture when your raw data hasn’t been summarized. Your first instinct may be to create a PivotTable, but not everyone can…
- Text Reading
113
MS Excel – 62 – Share your Excel workbook with others – Demo
Share your Excel workbook with others
- Text Reading
114
MS Excel – 62 – Share your Excel workbook with others – Lab
Share your Excel workbook with others Share a workbook with others, right within Excel. You can let them edit the workbook or just view it. Select Share. Select permissions and then Apply.…
- Text Reading
115
116
MS Excel – 63 – Comments and notes – Lab
Comments and notes Excel for Microsoft 365 has changed the way comments work. Comments are now threaded, and allow you to have discussions with other people about the data. Notes are for making notes…
- Text Reading
117
MS Excel – 64 – Collaborate on Excel workbooks at the same time with co-authoring – Demo
Collaborate on Excel workbooks at the same time with co-authoring
- Text Reading
118
MS Excel – 64 – Collaborate on Excel workbooks at the same time with co-authoring – Lab
Collaborate on Excel workbooks at the same time with co-authoring You and your colleagues can open and work on the same Excel workbook. This is called co-authoring. When you co-author,…
- Text Reading
119
MS Excel – 65 – See files others have shared with you – Lab
See files others have shared with you The Shared (on Mac and iOS) or Shared with me (on Android or Windows Desktop) view lets you see the files others have…
- Text Reading
120
MS Excel – 66 – Lock or unlock specific areas of a protected worksheet – Lab
Lock or unlock specific areas of a protected worksheet By default, protecting a worksheet locks all cells so none of them are editable. To enable some cell editing, while leaving…
- Text Reading
121
MS Excel – 67 – Lock or unlock specific areas of a protected worksheet – Lab
Lock or unlock specific areas of a protected worksheet By default, protecting a worksheet locks all cells so none of them are editable. To enable some cell editing, while leaving…
- Text Reading
122
123
MS Excel – 68 – Protect an Excel file – Lab
Protect an Excel file To prevent others from accessing data in your Excel files, protect your Excel file with a password. Note: This topic covers file-level protection only, and not workbook…
- Text Reading
124
MS Excel – 69 – Save or convert to PDF – Lab
Save or convert to PDF Use the Office programs to save or convert your files to PDFs, so that you can share them or print them using commercial printers. And…
- Text Reading
125
Legend
- Still to start
- Completed