[PDF]Microsoft Office( Ms Excel 2016)

Microsoft Office


Excel 2016 for Windows


INTRODUCTION TO MS-EXCEL


Learning Objectives
The Ribbon, Customizing the Ribbon
The Quick Access Toolbar, Tell Me and Smart Lookup
The Smart Lookup Tool, The File Tab
Columns and Rows, Entering Text
Long Words and Numbers, Completing a Series
Selecting Multiple Cells, Moving Text and Numbers
Copying Data
Insert a Row or Column, Delete a Row or Column
Changing Column Width and Row Height, Formatting Numbers
Formatting Text and Numbers, Changing the Font, Borders


GraphicsClip Art, Images from a FilePrinting, Saving


Intro to Excel spreadsheets


What is a spreadsheet?


A spreadsheet is the computerized equivalent of a general ledger. It has taken the place of the pencil,
paper, and calculator. Spreadsheet programs were first developed for accountants but have now been
adopted by anyone wanting to prepare a budget, forecast sales data, create profit and loss statements,


compare financial alternatives and any other mathematical applications requiring calculations.


The electronic spreadsheet is laid out similar to the paper ledger sheet in that it is divided into columns
and rows. Any task that can be done on paper can be performed on an electronic spreadsheet faster and


more accurately.


Page 2 of 150


The problem with manual sheets is that if any error is found within the data, all answers must be erased
and recalculated manually. With the computerized spreadsheet, formulas can be written that are


automatically updated whenever the data are changed.
What can a spreadsheet do?


In contrast to a word processor, which manipulates text, a spreadsheet manipulates numerical data and
text. Using a spreadsheet, one can create budgets, analyze data, produce financial plans, and perform


various other simple and complex numerical applications.


By having formulas that automatically recalculate, either built by you, the user, or the built-in math
functions, you can play with the numbers to see how the result is affected. Using this “what-if?” analysis,


you can see what affect changing a data value or calculation can have on your monitoring program.


Spreadsheets can also be used for graphing data points, reporting data analyses, and organizing and


storing data.


Starting Excel


You are encouraged to start using MS Excel as you read through the following materials to familiarize


yourself with the topics and procedures.


Click the Start button on the Windows taskbar.


The Start menu opens


Point to Programs


The Programs menu opens


Click Microsoft Excel


Excel opens a new workbook


Note: an icon for MS Excel may be located either on the desktop or on the Office toolbar.


Page 3 of 150


The Excel Screen


The screen in Excel looks different than those used in other types of applications.


COLUMNS
FORMULA BAR


SHEETS NAVIGATOR SHEETS TAB


VIEWING BUTTONS Leow Asseneuy


Columns


The large window, labeled "Microsoft Excel" may take up the entire screen. This is referred to as the
Application Window. The top line is called the Title Bar and has three buttons (Minimize, Restore, and
Close) to the right. These buttons are used to size the window and close it. This title bar is standard in


all Windows programs.


The second line is called the Menu Bar. Notice that one character of each selection is highlighted or


underlined. This menu bar is also standard in all Windows programs.


The next two lines contain buttons with text or images and are referred to as the Standard and
Formatting Toolbars. If you have a mouse, these toolbars allow you to enhance your worksheet without
accessing the menu. Keep in mind that these may not be in the exact same place as on the illustration


above. All toolbars can be customized to display any buttons you desire.


Page 4 of 150


The next line is the Formula Bar and displays the current cell address (see below) and contents. As you
move from cell to cell, Excel will keep track of the current cell address for you. The Formula Bar can also


be used to edit the text (contents) or formulas contained in the cell.
Columns and Cells and Row


The horizontal bar across the top of the worksheet area is filled with letters, beginning with A. Each letter
represents a column while the vertical bar on the left side of the worksheet filled with numbers refers


to rows.


The intersection between a column and a row is referred to as a cell. A cell is similar to a box that can be
used to store pieces of information. Each piece of information could be a word or group of words, a


number or a mathematical formula.


Each cell has its own address. This address is used in formulas for referencing different parts of the
worksheet. The address of a cell is defined by the letter of the column in which it is located and the
number of the row. For example, the address of a cell in column B, row 5 would be referred to as B5.


The column is always listed first followed by the row without any spaces between the two.


The outlined cell (the one with the dark borders) within the worksheet is referred to as the active cell.
Each cell may contain text, numbers, or dates. You can enter up to 32,000 characters in each cell


(Equivalent to a 44 page report!).


These cell addresses are useful when entering formulas. Instead of typing actual values in your


equations, you simply type the cell address where the value is stored. Then, if you need to go back and


change one of the values the spreadsheet automatically updates the result of the formula based on the


new data.


For example, instead of typing 67*5.4 you could enter C5*D5. The number 67 is stored in cell CS and the
number 5.4 is stored in cell D5. If these numbers change next month or next year, the formula remains
correct because it references the cells - not the actual values. With the second formula, you can change


the numbers stored in cells CS and D5 as often as required and see the result recalculated immediately.


The next section of the screen lists the columns and rows within the current worksheet. As mentioned,


columns are lettered and rows are numbered. The first 26 columns are lettered A through Z. Excel then
Page 5 of 150


begins lettering the 27th column with AA and so on. In a single Excel worksheet there are 256 columns


(lettered A-IV) and 65,536 rows (numbered 1-65,536), totaling 16,777,216 individual cells.


Sheets and Workbooks


Towards the bottom of the worksheet is a set of small Tabs that identify each sheet in the workbook
(file). If there are multiple sheets, you can use the tabs to easily identify what data is stored on each
sheet. For example, the top sheet could be "Expenses" and the second sheet could be called "Income".


When you begin a new workbook, the tabs default to being labeled Sheet1, Sheet2, etc.


At the bottom of the screen is another bar called the Status Bar. This bar is used to display various


information about the system and current workbook.


The left- hand corner of this line lists the Mode Indicator, which tells you what mode you are currently


working in.


The Zoom button (located on the toolbar at the top of the screen) allows you to change the size of the
viewing area. This does not affect the actual printing of the file. Click on the down arrow located to the
right side of the current zoom factor. Scroll through the available zoom choices. When you select a zoom


factor, Excel will zoom in or out of the worksheet area - as specified in the Zoom. You can also access


the View ZA Zoom menu. In addition, you can hide everything except the worksheet and the menu (which


will increase your working area) by accessing the View A Full Screen menu.


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.


File Menu


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


Page 6 of 150


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.
Tell Me


This is a text field where you can enter words and phrases about what you want to do next and quickly
get to features you want to use or actions you want to perform. You can also use Tell Me to find help


about what you're looking for, or to use Smart Lookup to research or define the term you entered.
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


Page 7 of 150


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


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.
In cell editing mode, it deletes the character to the left of the


insertion point.


DELETE Removes the cell contents (data and formulas) from selected


cells without affecting cell formats or comments.


In cell editing mode, it deletes the character to the right of the


insertion point.


Moves to the cell in the lower-right corner of the window when


SCROLL LOCK is turned on.


Page 8 of 150


Also selects the last command on the menu when a menu or
submenu is visible.

CTRL+END moves to the last cell on a worksheet, in the lowest
used row of the rightmost used column. If the cursor is in the
formula bar, CTRL+tEND moves the cursor to the end of the text.


CTRL+SHIFT+END extends the selection of cells to the last used


cell on the worksheet (lower-right corner). If the cursor is in the
formula bar, CTRL+SHIFT+END selects all text in the formula bar


Completes a cell entry from the cell or the Formula Bar, and


selects the cell below (by default).


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


menu or submenu, dialog box, or message window.


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.


Page 9 of 150


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:
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


Any items that are not to be used in calculations are considered, in Excel’s terminology, labels. This


includes numerical information, such as phone numbers and zip codes. Labels usually include the title,


column and row headings.
To Enter Text/Labels:

1) Click in a cell

2) Type text

3) Press Enter


NOTE: By default, pressing the Enter key will move you to the cell below the active cell.


Page 10 of 150


The label actually “lives” in the cell you typed it into. If you type long text it might appear to be in multiple
columns. It is important to understand this concept when trying to apply formatting to a cell. Using the


formula bar will confirm where the label actually “lives.”


Autofills


Frequently, it is necessary to enter lists of information. For example, column headings are often the
months of the year or the days of the week. To simplify entering repetitive or sequential lists of
information, Excel has a tool called Autofill. This tool allows preprogrammed 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


“any


same way as labels. It is important NOT to type values with characters such as “,” or “S”.


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 possible to create formulas in Excel using the actual values, such as “4000*.4” but it


is more beneficial to refer to the cell address in the formula, for example “D1*.4”. One of the benefits
of using a spreadsheet program is the ability to create a formula in one cell and copy it to other cells.


Most spreadsheet formulas use a concept called relative referencing.


This is the explanation of relative referencing from Excel’s help file:


“A relative cell reference in a formula, such as Al, is based on the relative position of the cell that
contains the formula and the cell the reference refers to. If the position of the cell that contains the


formula changes, the reference is changed. If you copy the formula across rows or down columns, the
Page 11 of 150


reference automatically adjusts. By default, new formulas use relative references. For example, if you


copy a relative reference in cell B2 to cell B3, it automatically adjusts.”
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


——T| Begins all Excel functions and formulas


a


To Create a Formula:
>>>