Microsoft Excel 2016 Tutorial
1. Introduction
Microsoft Excel 2016 is a powerful spreadsheet program used to organize, calculate, and analyze data. It
allows users to create tables, perform mathematical formulas, and make charts for visualizing
information.
2. Starting MS-Excel 2016
To start the application, do the following:
1. Click the Start Menu (the Windows icon in the bottom-left corner).
2. Type "Excel" into the search bar.
3. Click on the "Excel 2016" application that appears in the search results.
3. Creating an Excel Workbook
When you click Excel 2016, select Blank workbook to create a new Excel workbook
4. The main screen elements
The main elements of the screen are:
Title Bar: The horizontal bar located at the top center of the Excel window that displays the name of the
current workbook (e.g., "Book1") and usually contains the control buttons (Minimize, Maximize, Close)
on the right.
Ribbon: The main command center at the top of the Excel window, which organizes all commands into
Tabs (e.g., Home, Insert) and Groups (e.g., Font, Alignment).
Quick Access Toolbar: A small, customizable toolbar, usually located above the Ribbon, that holds
frequently used commands like Save, Undo, and Redo.
File Menu (or Backstage View): The screen that appears when you click the File tab; it provides access to
all non-worksheet functions like Save, Save As, Open and Print
Formula Bar: The long, horizontal bar below the Ribbon where you view, enter, or edit the data or
formula contained within the active cell.
Status Bar: The horizontal area at the bottom of the Excel window that includes helpful tools like the
Zoom slider and the Excel views
Zoom: A slider tool, usually found on the bottom-right status bar, used to increase or decrease the
magnification of the worksheet display
Excel views: shows how the worksheet is displayed on screen, the Normal view is the default view.
Active cell: The cell where you enter any data surrounded by a rectangular box.
Scroll bar: is a tool that lets you move up, down, left, or right through a worksheet to view data that is
not currently visible on the screen.
5. Workbook and worksheets
A workbook is an Excel file that contains one or more worksheets. It is where all your data, charts, and
formulas are saved.
A worksheet is a single sheet within a workbook, made up of rows and columns where you enter and
organize data.
1. Row: is a horizontal line of cells in a worksheet, labeled with numbers (1, 2, 3, …).
2. Column: is a vertical line of cells, labeled with letters (A, B, C, …).
3. Cell: is the intersection of a row and a column, used to enter data (for example, cell A1).
4. Cell Reference: is the address of a cell in a worksheet, identified by its column letter
and row number (for example, the cell reference A1: is the cell at the intersection of
column A and row 1).
Remember: Active cell reference appears in the Name Box.
6. Entering data in a worksheet
Excel allows you to enter different types of data into the cells, such as dates, text, formulas, and
numbers.
Steps to enter data into a worksheet:
1. Click on the cell where you want to enter data.
2. Type the text, number, or formula.
3. Press Enter to move down to the next cell, or Tab to move right.
7. Saving the workbook
After you have entered the data, you will need to save your work for later use either using Save or Save
As.
Steps to save the file for the first time:
1. Click the File tab.
2. Choose Save As.
3. Select the location (This PC, OneDrive, or Browse).
4. Type a file name.
5. Click Save. The file will be saved with the .xlsx extention.
Steps to save a file that was already saved before:
1. Click the File tab.
2. Choose Save, or simply click the Save icon on the Quick Access Toolbar.
3. Excel updates the existing file with your latest changes.
8. Closing the Workbook
1. Click on the File tab in the Ribbon.
2. Select Close from the menu.
3. If you haven’t saved your changes, Excel will display the following box:
Click Save to save and close.
Click Don’t Save to close without saving.
Click Cancel to return to the workbook.
You can close the workbook using the Close icon
9. Opening a workbook
Steps to open a workbook in Excel:
1. Open Microsoft Excel.
2. Click on File in the menu bar.
3. Select Open from the list.
4. Choose Browse to find your file.
5. Locate and click the workbook you want to open.
6. Click the Open button.
10. Rows and Columns Selection
To select a row
1. Move the cursor to the row number on the left side of the worksheet.
2. Click the row number to highlight the entire row.
To select a column
1. Move the cursor to the column letter at the top of the worksheet.
2. Click the column letter to highlight the entire column.
To select adjacent rows
1. Click the first row number you want to select.
2. Hold down the Shift key on your keyboard.
3. Click the last row number in the range.
→ All rows between the first and last will be highlighted.
To select adjacent columns
1. Click the first column letter you want to select.
2. Hold down the Shift key on your keyboard.
3. Click the last column letter in the range.
→ All columns between the first and last will be highlighted.
To select un-adjacent rows
1. Click the first row number you want to select.
2. Hold down the Ctrl key on your keyboard.
3. While holding Ctrl, click each additional row number you want to select.
→ Only the rows you click will be highlighted.
To select un-adjacent columns
1. Click the first column letter you want to select.
2. Hold down the Ctrl key on your keyboard.
3. While holding Ctrl, click each additional column letter you want to select.
→ Only the columns you click will be highlighted.
11. Working with rows, columns and sheets(video)
At the beginning, we will work with rows and columns. We will learn the steps needed to
insert, delete, hide/unhide and adjust rows and columns. Start by opening the workbook to
apply the steps.
To insert a row or a column:
1- Select the row number or the column letter where you want the new row to appear
2- Click the Home Tab
3- Go to the Cells group, click Insert
4- Choose Insert Sheet Rows or choose Insert Sheet Columns
To delete a row or a column
1. Select the row number or column letter you want to delete.
2. Click the Home tab.
3. Go to Cells group, click Delete.
4. Choose Delete Sheet Rows or choose Delete Sheet Columns
To hide a row or a column
1. Select the row(s)/column(s) to hide.
2. Click the Home tab.
3. Go to Cells group, click Format.
4. Click Hide & Unhide ,choose Hide Rows/Hide Columns
To unhide a row or a column
1. Select the rows(columns) around the hidden row(column).
2. Go to the Home tab.
3. Click the Cells group, click Format.
4. Choose Hide & Unhide.
5. Click Unhide Rows (Unhide Columns).
Adjusting row’s height and column’s width:
1. Select the row / column you want to adjust.
2. click the Home tab.
3. Go to Cells group, click Format.
4. Choose Row Height/ Column Width
5. Type the height/ width you want then click OK.
Now let us work with sheets:
To delete a sheet:
1. Click the sheet tab you want to delete.
2. Click the Home tab.
3. Go to Cells group, click Delete.
4. Choose Delete Sheet.
Or right-click the sheet tab then click Delete
To insert a sheet:
1. Go to the Home tab.
2. Go to the Cells group, click Insert.
3. Choose Insert Sheet. A new worksheet will appear.
Or right-click the sheet tab then click Insert
To rename a sheet:
1. Select the sheet you want to rename.
2. Click the Home tab.
3. Go to the Cells group, click Format.
4. Choose Rename Sheet.
5. Type the new name and press Enter.
Or right-click the sheet tab then click Rename
To hide a sheet:
[Link] the sheet you want to hide.
[Link] the Home tab.
3. Go to the Cells group, click Format.
[Link] Hide & Unhide then click Hide Sheet.
Or right-click the sheet tab then click Hide
To unhide a sheet:
1. Click the Home tab.
2. Go to the Cells group, click Format.
3. Choose Hide & Unhide then click Unhide Sheet.
4. In the dialog box, select the sheet you want to unhide.
5. Click OK.
To move a sheet
1. Click the Home tab.
2. Go to the Cells group, click Format.
3. Choose Move or Copy Sheet
4. In the dialog box, under Before sheet, choose the location for the sheet.
5. Click OK.
To copy a sheet
1. Click the Home tab
2. Go to Cells group, click Format.
3. Choose Move or Copy Sheet. In the Move or Copy dialog box:
4. Check Create a copy then choose where you want the copied sheet to appear under
Before sheet.
5. Click OK
Or right-click the sheet tab then click Move or Copy
12. Copy, move, copy format and delete cell contents
To Copy cell contents:
1. Select the cell or range of cells.
2. Click Home tab –Clipboard group→ Copy (or press Ctrl + C).
3. Select the destination cell.
4. Click Home tab –Clipboard group → Paste (or press Ctrl + V).
To Move cell contents:
1. Select the cell or range of cells.
2. Click Home tab –Clipboard group → Cut (or press Ctrl + X).
3. Select the destination cell.
4. Click Home tab –Clipboard group → Paste (or press Ctrl + V).
To copy the format of a cell contents
1. Select the cell that has the formatting you want to copy.
2. Click Home tab -Clipboard group Format Painter (paintbrush icon).
3. Drag or click on the cell(s) you want to apply the same formatting to.
4. Release the mouse button to apply the format.
To Delete:
1. Select the cell or range of cells.
2. Press Delete on the keyboard
13. Formatting Data Using Font and Alignment groups
Once the data is entered, you can customize its appearance using the formatting tools.
1. Select the cell or range of cells you want to format.
2. Go to the Home tab on the Ribbon.
3. In the Font group, choose the formatting options you want:
Select a Font type (e.g., Calibri, Arial).
Choose a Font size.
Choose the Font style: Bold (B), Italic (I), or Underline (U) if needed.
Click the Font Color button to change text color.
Click the Fill Color button to change the background color.
4. In the Alignment group, select the vertical and horizontal alignment you want
14. Formatting Numbers using Number group
The numbers you entered can be formatted using the Number group. Do the following steps:
Select the cell you want to format.
Go to the Home tab on the Ribbon.
In the Number group, choose the formatting options you want:
1. Number Format Dropdown list
o Displays the current format (e.g., General, Number, Currency, Accounting, Date,
Time, Percentage, etc).
2. Accounting Number Format ($) symbol
o Applies accounting style with aligned currency symbols and two decimal places.
o The dropdown list displays more currency symbols (€, £, and more.).
3. Percent Style ( % )symbol.
o Converts numbers to percentages by multiplying the number by100
4. Comma Style
o Adds a thousand separator comma
5. Increase Decimal / Decrease Decimal
Increase Decimal: show more decimal places.
Decrease Decimal: show fewer decimal places.
15. Searching data
To search for a number, text or formula do the following:
Go to the Home tab on the Ribbon.
In the Editing group, click Find&Select , then form the dropdown list click Find
A dialog box will appear, in the Find What text box, write the data you are looking for.
Click Find All button
16. Autofill
Copy the data or continue a series or pattern into neighboring cells in any direction
To copy the contents of a cell:
1. Type the data in the cell that you want to copy (Ex1: 1 in cell A7, Ex2: 5 in cell C7).
2. Move the mouse pointer to the bottom-right corner of the cell until it changes to a small
black plus sign (+) (the fill handle).
3. Drag the fill handle over the cells where you want to copy the content either down a
column or across a row.
4. Release the mouse button.
To copy a data series:
1. Type in the first cell and the second cell (below or beside it.) the data you want make a
series for and copy it. (For example: 1, 2 in cells G7,G8 or Sunday, Monday in Cells
E7,E8)
2. Select both cells to highlight the pattern.
3. Move the pointer to the bottom-right corner of the selection until it changes to a small
black plus sign (+) the fill handle.
4. Drag the fill handle over the cells where you want the series to continue.
5. Release the mouse button
[Link] Formulas
A formula is a mathematical calculation that may contain numbers, cells references and
mathematical operators (i.e. +, -, (),%,^,*,/).
In Excel 2016, every formula must start with an equal sign (=) so that Excel knows you’re entering a
calculation rather than plain text.
Priority of operators:
1. Parenthesis ()
2. Exponent ^
3. Division and Multiplication from left to right. / , *
4. Addition and Subtraction from left to right. +, -
Example1:
1. Click on a blank cell where you want the result to appear for example E3.
2. Type =3+4 in the cell, you can notice the formula in the formula bar.
3. Press the Enter key.
4. The cell will display the result 7.
Example2:
1. Type 6 in the cell E5
2. Type 3 in the cell F5
3. Type 1 in the cell G5
4. Click the blank cell where you want the result to appear for example E7.
5. Type =E5+F5-G5
6. The cell will display the result 8
18. Functions
What is a Function?
A function is a predefined formula (built –in) that performs a specific operation on data to return a
result. Functions are used to simplify calculations.
You just write the function name and its arguments.
for example: =SUM(B1:B5) adds all the values from cell B1 to B5.
Instead of writing the formula: =B1+B2+B3+B4+B5
Some of the functions are:
1- Sum: Adds all the numbers in a range of cells.
2- Average: return the average (i.e. mean) of its arguments
3- Min: returns the smallest number in a set of values.
4- Max: returns the largest number in a set of values.
5- Count: counts the number of cells in a range that contains numbers
Different methods to insert a function:
Method 1: Using the Formula Bar
1. Click the cell where you want the result.
2. Type =functionName(
3. Select the range of cells you want to apply the function to.
4. Type ) then click Enter.
Example: Finding the summation of the Grade1 column.
Method 2: Using the AutoSum Button
1. Select the cell where you want the result.
2. Click the Home tab Editing group AutoSum (Σ).
3. Click the function you want to use.
4. A range of cells will be automatically displayed change it if needed.
5. Click Enter.
Example: Finding the average of Grade2 column.
Method 3: Using the Insert Function Dialog Box
1. Click the cell where you want the result.
2. Click the Formulas tab.
3. Click Insert Function (fx).
4. In the dialog box, select the function you want from the list.
5. Type the cell range, then click OK.
6. Note: you can use the Insert Function (fx) button from the formula bar.
Example: Finding the maximum value of the Grade3 column.
19. Sorting
In Excel 2016, you can sort numbers, text or dates, in either ascending or descending order.
Sorting can be done from:
The Sort&Filter group from the Data tab, OR
The Editing group from the Home tab
Let us explain the steps needed to Sort Text Data
1. Open the workbook.
2. Select sheet1.
3. Click any cell in the column that you want to sort by for example we want to sort the
table according to the Department column
4. Click Home tab, then select Sort & Filter from Editing group. OR you can use Data tab.
5. Choose either Ascending (Sort A to Z) or Descending (Sort Z to A) order.
The result will be a table in which its contents are sorted according to the department column.
Now let us repeat the same steps to Sort Number Data
1. Open the workbook.
2. Select sheet1.
3. Click any cell in the column that you want to sort by for example we want to sort the
table according to the Grade1 column
4. Click Home tab, then select Sort & Filter from Editing group. OR you can use Data tab.
5. Choose either Ascending (Sort smallest to largest) or Descending (Sort largest to
smallest) order.
The result will be a table in which its contents are sorted according to the Grade1 column.
Finally, we will Sort Date Data following the previous steps:
1. Open the workbook.
2. Select sheet1.
3. Click any cell in the column that you want to sort by for example we want to sort the
table according to the BirthDate column
4. Click Home tab, then select Sort & Filter from Editing group. OR you can use Data tab.
5. Choose either Ascending (Sort oldest to newest) or Descending (Sort newest to oldest)
order.
The result will be a table in which its contents is sorted according to the BirthDate column.