AMAAR INSTITUDE OF TECHNOLOGIES

Introduction to
Microsoft Excel 2016
Microsoft Excel 2016 Interface the Excel interface is where you see and use the tools in Excel on
the screen. This includes the way the tools are organized and presented to you, the software user.
You will learn about The Welcome Page, The Ribbon, Quick Access Toolbar, and File Tab.

Screen Elements

Quick Access Toolbar Tell Me The Ribbon

Expand Formula Bar Button

Formula Bar
File Menu

Vertical Scroll
Bar

Worksheet Navigation Tabs

Horizontal Scroll Bar Zoom

Insert Worksheet Button Level

Normal Page Page

View Layout Break
View Preview

The Ribbon
The Ribbon is designed to help you quickly find the commands that you need to complete a task.
Commands are organized in logical groups, which are collected together under Tabs. Each Tab
relates to a type of activity, such as formatting or laying out a page. To reduce clutter, some Tabs
are shown only when needed. For example, the Picture Tools tab is shown only when a picture is
selected.

1
File Menu
Here you will find the basic commands such as open, save, print, etc.

Quick Access Toolbar

The place to keep the items that you not only need to access quickly, but want to be immediately
available regardless of which of the Ribbon's tabs you're working on. If you put so many items on
the Quick Access Toolbar that it becomes too big to fit on the title bar, you can move it onto its own
line.
Formula Bar
A place where you can enter or view formulas or text.

Expand Formula Bar Button

This button allows you to expand the formula bar. This is helpful when you have either a long
formula or large piece of text in a cell.

Worksheet Navigation Tabs

By default, every workbook starts with 1 sheet.

Insert Worksheet Button

Click the Insert New Worksheet button to insert a new worksheet in your workbook.

Horizontal/Vertical Scroll
Allows you to scroll vertically/horizontally in the worksheet.

Normal View
This is the “normal view” for working on a spreadsheet in Excel.

Page Layout View

View the document as it will appear on the printed page.

Page Break Preview

View a preview of where pages will break when the document is printed.

Zoom Level
Allows you to quickly zoom in or zoom out of the worksheet.

Navigating in the Excel Environment

Below is a table that will assist you with navigating/moving around in the Excel environment.

Key Description

2
 ARROW KEYS Move one cell up, down, left, or right in a worksheet.
SHIFT+ARROW KEY extends the selection of cells by one cell.

BACKSPACE Deletes one character to the left in the Formula Bar.

Also clears the content of the active cell.

DELETE Removes the cell contents (data and formulas) from selected
cells without affecting cell formats or comments.

END Moves to the cell in the lower-right corner of the window when
SCROLL LOCK is turned on.

ENTER Completes a cell entry from the cell or the Formula Bar, and
selects the cell below (by default).

ESC Cancels an entry in the cell or Formula Bar. Closes an open

menu or submenu, dialog box, or message window.

HOME Moves to the beginning of a row in a worksheet.

CTRL+HOME moves to the beginning of a worksheet.

PAGE DOWN Moves one screen down in a worksheet.

PAGE UP Moves one screen up in a worksheet.

SPACEBAR In a dialog box, performs the action for the selected button, or
selects or clears a check box.
CTRL+SPACEBAR selects an entire column in a worksheet.
SHIFT+SPACEBAR selects an entire row in a worksheet.
CTRL+SHIFT+SPACEBAR selects the entire worksheet.

TAB Moves one cell to the right in a worksheet.

Highlighting/Selecting Areas Using the Mouse

Select cells:

Moves a cell’s contents:

3
Activate the Autofill feature:

To Select a Column: Click on the column letter

To Select a Row: Click on the row number
To Select the Entire Worksheet: Click above row 1 and to the left of column A or hit CTRL A on
the keyboard
Entering Text
To Enter Text/Labels:
1) Click in a cell
2) Type text
3) Press Enter
Autofill
This tool allows preprogramed lists, as well as custom lists, to be easily added to a spreadsheet.

Entering Values
Numerical pieces of information that will be used for calculations are called values. They are
entered the same way as labels. It is important NOT to type values with characters such as “,” or
“$”.
To Enter Values:
1) Navigate to a cell
2) Type a value
3) Press Enter

Creating Formulas
Formulas perform calculations or other actions on the data in your worksheet. A formula starts with
an equal sign (=).
It is also important to know the operators Excel uses for formulas:

Operator (Key) Function

= Begins all Excel functions and formulas
+ Addition
- Subtraction
* Multiplication
/ Division

4
To Create a Formula:
1) Click in a cell
2) Press the = key
3) Type the formula
4) Press Enter

Copying Formulas
Like many things in Excel, there is more than one way to copy formulas. Feel free to choose what
works best for you.

To Copy Formulas Using Autofill:

1) Click in the cell that contains the formula
2) Position the mouse on the Autofill handle (a thin black cross will appear) 3) Click
and drag to copy the formula
To Copy Formulas Using Copy and Paste:
1) Click in the cell that contains a formula
2) Select Copy on the Home Ribbon in the Editing group
3) Highlight the cell where you would like to paste the formula 4) Select Paste on the Home
Ribbon in the Editing group

Autosum Function
The most common formula in Excel is SUM, or the addition of multiple values. In this example, we
could create a formula that reads =C6+D6+E6+F6+G6+H6. That’s a lot of typing! Instead, we can
use the SUM function and specify a range of cells.

To Create the Total Column’s Values Using Autosum:

1) Click in the cell where you would like the Total to be located
2) Press the Autosum button on the Home Ribbon

3) Press Enter

5
Saving a Worksheet
When working in Excel it is necessary to save your files. It is also very important that while
working, your file is saved frequently. When naming a file, you are restricted to 255 characters.
Avoid most punctuation; spaces are acceptable.

To Save the File:

1) Click on the File tab
2) Click Save
3) Choose the destination
4) Type a file name
5) Click Save

Editing Cells
Excel provides a major enhancement over earlier spreadsheet products in its ability to edit cells
easily. There are various methods for cell editing, including double-clicking in the cell, using the F2
key, and typing in the formula bar.

To Edit a Cell in the Worksheet:

1) Position yourself in the cell you would like to edit
2) Press the F2 key on the keyboard or double-click in the cell
3) Use the backspace or delete keys to edit the cell
4) Press Enter when you have finished editing the cell

~OR~

5) Click in the cell you would like to edit

6) Click in the formula bar and make any necessary changes 7) Press Enter when you have
finished editing the cell

Undo
Excel and other Windows applications have a convenient method of correcting mistakes known as
Undo. In many applications, including Excel, you can undo an almost limitless number of
commands. The Undo button has a small down-pointing arrow next to it. When pressed, it will
display a list of actions that can be undone. Redo works in the same way, allowing you to repeat
actions.

Excel will undo actions in reverse chronological order, meaning that the most recent command is
reversed first, then the one prior to that, and so on. You cannot reverse an earlier action using
Undo without first undoing the actions that were performed after it.

NOTE: The list of commands to undo is reset after the file is saved. You cannot use Undo to
fix an error after the file is saved.

6
To Undo a Command:

Click Undo

Clearing Cells
As we begin to look at formatting, it is important to understand what makes up the contents of a
cell. There are three distinct items that can be in a cell:

• Contents
• Formats
• Comments

These allow items to be formatted properly, even if the values change. However, when trying to
delete or clear a cell, it can be a bit tricky. Excel stores formats and contents separately, simply
deleting the contents does not delete the format.

To Clear a Cell Format:

1) Click in the cell that contains formatting

2) Click the drop-down arrow next to the Clear button on the Home tab in the Editing group 3)
Click Clear Formats

Formatting Values
Applying formats to any cell(s) can be done either using the Font, Alignment and Number groups
or using the dialog box which will include all the formatting options.

To Apply the Currency Format:

1) Highlight the cell(s)
2) Click on the Currency Style button on the Home tab in the Number group
3) If necessary, click on the Increase or Decrease Decimal button on the Number group

To Apply the Comma Format:

7
 1) Highlight cells
2) Click on the Comma Style button on the Number group
3) If necessary, click on the Increase or Decrease Decimal button on the Number group

Formatting Labels
A Label, or text formatting is applied virtually the same way it is done in word processing
programs.

To Format the Title Labels:

1) Highlight the cell(s)
2) Select a font from the Font group
3) Select a point size from the Font group

Using the Dialog Box:

1) Highlight the cells
2) Click on the arrow in the corner of one of the formatting groups (Font, Alignment, Number) to
open the Format Cells dialog box and click on one of the tabs

8
Format Painter
Frequently, you will need to take a format that is applied to one cell and apply it to other cells. A
quick way to do this is by using the Format Painter .

To Apply a Format to Cells:

1) Highlight cell(s)
2) Format the cell(s) to the desired format
3) Select the formatted cell(s)
4) Click the Format Painter from the Clipboard group of the Home tab
5) Highlight the cells you wish to format

Tips and Tricks: If you would like the Format Painter to remain active, double-click the Format
Painter. It will remain active until you press the Esc key.

Wrapping Text and Merging Cells

To Wrap Text

• Step 1: Select the cells with text you want to wrap.

• Step 2: Select the Wrap Text command on the Home tab

• Step 2: Select the Wrap Text command on the Home tab

To Merge Cells

• Select the cells you want to merge together.

• Select the Merge & Center command on the Home tab.
• The selected cells will be merged and the text will be centered.

Note: If you change your mind, re-click the Merge & Center command to unmerge the cells

Using Borders

• Step 1: Select the cells you want to modify.

Step 2: Click the drop-down arrow next to the Borders

command on the Home tab.

• Step 3: Select the border style you want to use.

Hide/Unhide

9
• Step 1: Select the column(s) or row(s) you want to hide, right-click the mouse, then select Hide
from the formatting menu.

• Step 2: The columns/rows will be hidden. The green column line indicates the location of the
hidden columns

To unhide, select the columns to the left and right of the hidden columns/rows (in other words, the
columns on both sides of the hidden columns/rows).

• Step 4: Right-click the mouse, then select Unhide from the formatting menu. The hidden
columns/rows will reappear.

Precedence of Operations
It’s important to note that a formula in Excel is not always evaluated left to right, like you might
think. Certain operators are evaluated before others, which changes the formula’s result.

The Precedence of Operations would force the multiplication to be evaluated first and then the
addition after that, 2 * 3 (6), and then add 5 to that (5 + 6 = 11) making the correct answer 11.

Centering Text Across Columns

When it comes to titles, it may be preferable to have the information centered across the
document, rather than in only one cell. Excel uses the feature Merge Cells to accomplish this.

To Center the Title Across Columns:

1) Highlight cell(s)
2) Click the Merge and Center button on the Alignment group

NOTE: Each cell must be done individually. Excel will delete the contents of all but the top
most cell if multiple cells are selected.

This option basically takes all the cells in the highlighted range and merges them into one large
cell. For example, the range A1:F1 became cell A1 after the Merge Cells button was selected.
There is no cell B1, C1, etc. any longer.

Creating a Basic Chart

1) Highlight the data to be charted
10
 2) Click on the Insert tab

3) Click on a Chart Type in the Charts group

4) Click on a Chart Style

To Move your Chart:

Click and drag the chart to a new location on the worksheet.

When the chart is selected you will notice a new tab “Chart Tools” on the Ribbon. If you do not
see the Chart Tools, click on the chart to select it. Under Chart Tools you will find 2 tabs:

• Design
• Format

Excel Functions
As we have previously seen, the power of Excel lies in its ability to perform calculations. The real
strength of this is shown in Functions. Functions are more complex formulas that are executed by
using the name of a function and stating whatever parameters the function requires.

Function Defined
=SUM(range of cells) returns the sum of the selected cells
=AVERAGE(range of cells) returns the average of the selected cells
11
 =MAX(range of cells) returns the highest value of the selected cells
=MIN(range of cells) returns the lowest value of the selected cells
=COUNT(range of cells) returns the number of values of the selected cells

To Enter the SUM Function:

1) Click in a cell
2) Click on the AutoSum button in the Editing group
3) Highlight the range of cells that are to be added (The colon means “through”) 4) Press
ENTER

To Insert the Average Function into the Worksheet:

1) Click in a cell
2) Click on the drop-down arrow next to the AutoSum button
3) Click on Average
4) Highlight the range of cells be calculated
5) Press ENTER

To Insert the MAX Function into the Worksheet:

1) Click in a cell
2) Click on the drop-down arrow next to the AutoSum button
3) Click on Max
4) Highlight the range of cells be calculated
5) Press ENTER

To Insert the MIN Function into the Worksheet:

1) Click in a cell
2) Click on the drop-down arrow next to the AutoSum button
3) Click on Min
4) Highlight the range of cells be calculated
5) Press ENTER

To Insert the COUNT NUMBERS Function into the Worksheet:

1) Click in a cell
2) Click on the drop-down arrow next to the AutoSum button
3) Click on Count Numbers
4) Highlight the range of cells be calculated
5) Press ENTER
IF Function
The if function can be used for comparative analysis. It returns one value if a condition you specify
evaluates to TRUE and another if it evaluates to FALSE. Any of the following mathematical
expressions for comparison can be used:

12
Nested Functions
You can nest a number of IF functions, as long as you don’t exceed the character limit for single-
cell entries. The formula in cell D9 is an example of a nested if function.
=IF(B9>=85,"A",IF(B9>=70,"B",IF(B9>=55,"C",IF(B9>=40,"D","FAIL"))))
How to use the PMT Function in Excel
The PMT function can be entered as part of a formula in a cell of a worksheet. To understand the
uses of PMT, let us consider an example.
Example 1

13
Let’s assume that we need to invest in such a manner that after two years, we’ll receive $75,000.
The rate of interest is 3.5% per year and the payment will be made at the start of each month. The
etails are:

Sheet Tab
The names of the sheets appear on tabs at the bottom of the workbook window. To move from
sheet to sheet, click the sheet tabs.

Changing the Tab Colour of a Worksheet

▪ Right click on the sheet tab and select Tab Color.
▪ Select the required colour
▪ And click on OK.

14
 Deleting a Worksheet
▪ Right click on the sheet tab and select Delete.

To hide a worksheet
▪ Select the tab of the sheet you wish to hide
▪ Right-click on the tab
▪ Click Hide

To unhide a worksheet
▪ Right-click on any worksheet tab
▪ Click Unhide
▪ Choose the worksheet to unhide for the Unhide
Dialogue box that appears and Click OK
Protecting a Workbook
Step 1: Click the File tab to access the backstage view.
Step 2: From the Info pane, click the Protect Workbook command.
Step 3: Choose the option that best suits your needs.
Step 4: Click OK.
Step 5: Another dialog box will appear. Click OK to confirm.
Range Protection
• Step 1: Select all cells in the current worksheet by pressing Ctrl A
• Step 2: Right click and select Format Cell in the menu

Step 3: In the Format Cells dialog box, uncheck Locked under the Protection tab and click OK.

15
 • Step 4: Select the cells that you want to lock.
• Step 5: Right click the selected range and select Format Cell from the menu.
• Step 6: In the Format Cells dialog box, check Locked under the Protection tab and click
OK.

• Step 7: Click Protect Sheet in the Changes group under the Review tab.

• Step 8: In the Protect Sheet dialog box, enter a password and click OK.

• Step 9: A Confirm Password dialog box will appear, re-enter the password and click OK.
Freezing Worksheet Panes
To Freeze Rows or Columns

• Step 1: Select the row below the rows that you want frozen. For example, if you want rows
1 & 2 to always appear at the top of the worksheet even as you scroll, then select row 3. Do
the same manner for columns if freezing columns.
• Step 2: Click the View tab.
• Step 3: Click the Freeze Panes command. A drop-down menu appears.

16
 Freeze Panes
Option

• Step 4: Select Freeze Panes.

• Step 5: A black line appears below the rows that are frozen in place. Scroll down in the
worksheet to see the rows below the frozen rows.

To Unfreeze Panes

• Step 1: Click the View tab.

• Step 2: Click the Freeze Panes command. A drop-down menu appears.
• Step 3: Select Unfreeze Panes. The panes will be unfrozen and the black line will disappear.

Conditional Formatting
Conditional formatting applies one or more rules to any cells that you want. An example of a rule
might be "If the value is greater than 5,000, color the cell yellow."
To Create a Conditional Formatting Rule

Step 1: Select the cells that you want to add the formatting to.
Step 2: In the Home tab, click the Conditional Formatting command. A drop-down menu will appear.
Step 3: Select Highlight Cells Rules or Top/Bottom Rules. We will choose Highlight Cells Rules for
this example. A menu will appear with several rules.
Step 4: Select the desired rule (Greater Than, for example).
Step 5: From the dialog box, enter a value in the space provided, if applicable.
Step 6: Select a formatting style from the drop-down menu.
Step 7: The formatting will be applied to the selected cells

Sorting Data
To Sort in Alphabetical Order
• Step 1: Select a cell in the column you want to sort by.
• Step 2: Select the Data tab, and locate the Sort and Filter group.
• Step 3: Click the ascending command sort ascending to Sort A to Z, or the descending command
sort ascending to Sort Z to A.
17
Filtering Data
Filters can be applied in many ways to improve the performance of your worksheet. You can filter
text, dates, and numbers. You can even use more than one filter to further narrow down your
results.
To Filter Data

Step 1: Begin with a worksheet that identifies each column using a header row.
Step 2: Select the Data tab, and locate the Sort & Filter group.
Step 3: Click the Filter command.
Step 4: Drop-down arrows will appear in the header of each column
Step 5: Click the drop-down arrow for the column you would like to filter.
Step 6: The Filter menu appears.
Step 7: Uncheck the boxes next to the data you don't want to view.
Step 8: Check the boxes next to the data you do want to view.
Step 9: Click OK. All other data will be filtered, or temporarily hidden.

To Clear a Filter
Step 1: Click the drop-down arrow in the column from which you want to clear the filter.
Step 2: Choose Clear Filter From...

Printing a Worksheet
To Print, Preview and Modify Page Setup
1) Click on the File tab
2) Click on Print

The spreadsheet shows as it will be printed. You can proceed to print the document from here, or
you can change things to make the printed output look different.

Page Setup
You can change options under Settings or you can click on Page Setup.

18
Clicking on Page Setup will open a dialog box with four tabs:
• Page
• Margins
• Header/Footer
• Sheet

Page:
1) Change the Orientation
2) Adjust the Scaling
3) Change the Paper Size

Margins:
1) Change the margins
2) Center on the page either horizontally, vertically or select both

Header/Footer:
1) To select from one of the already created headers/footers, click on the drop-down arrow for
Header and also for Footer and choose from the list

19
 2) To create a custom header and/or footer, click on Custom Header and Custom Footer

This area is made of three sections – left, center and right. Any information added in these
sections will appear in that area (left, center or right) in the header or footer. You will also
see a row of buttons in this dialog box. Following are their functions:

Button Image Function

Format Text
Page Number
Total Number of Pages
Date
Time
File Path
Filename
Sheet Name
Picture
Format Picture

3) Click in a section to position your cursor

4) Enter text/fields
5) Click OK when finished

Sheet Tab:
1) Repeat Rows and Columns under Print Titles
2) Check off what to print under Print
3) Change the Page Order

20