Download Microsoft Excel 2016: Understanding the Ribbon and Screen Elements and more Schemes and Mind Maps Design in PDF only on Docsity!
Introduction to
Microsoft Excel 20 16
Screen Elements
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. The Ribbon File Menu Quick Access Toolbar Formula Bar Expand Formula Bar Button Insert Worksheet Button Worksheet Navigation Tabs Normal View Page Layout View Page Break Preview Vertical Scroll Bar Horizontal Scroll Bar Zoom Level Tell Me
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. 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 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.
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:
- Click in a cell
- Type text
- Press Enter NOTE: By default, pressing the Enter key will move you to the cell below the active cell. 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.”
Autofill
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 same way as labels. It is important NOT to type values with characters such as “,” or “$”. To Enter Values:
- Navigate to a cell
- Type a value
- 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 A1, 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 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
- Multiplication / Division To Create a Formula:
- Click in a cell
- Press the = key
- Type the formula
- 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:
- Click in the cell that contains the formula
- Position the mouse on the Autofill handle (a thin black cross will appear)
- Click and drag to copy the formula
The Autosum function automatically looks for cells that have values in them. It will read values until it finds the first blank cell. Autosum will always look for values in the cells above it first, then to the left. This means that you need to be aware of what cells will be in the formula. Autosum will select the range of cells to use in the formula by highlighting the range.
- Press Enter
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:
- Click on the File tab
- Click Save
- Choose the destination
- Type a file name
- 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 F key, and typing in the formula bar. To Edit a Cell in the Worksheet:
- Position yourself in the cell you would like to edit
- Press the F2 key on the keyboard or double-click in the cell
- Use the backspace or delete keys to edit the cell
- Press Enter when you have finished editing the cell ~OR~
- Click in the cell you would like to edit
- Click in the formula bar and make any necessary changes
- 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. 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:
- Click in the cell that contains formatting
- Click the drop-down arrow next to the Clear button on the Home tab in the Editing group
- Click Clear Formats
Using the Dialog Box:
- Highlight the cells
- 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
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:
- Highlight cell(s)
- Format the cell(s) to the desired format
- Select the formatted cell(s)
- Click the Format Painter from the Clipboard group of the Home tab
- 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.
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:
- Highlight cell(s)
- 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
- Highlight the data to be charted
- Click on the Insert tab
- Click on a Chart Type in the Charts group
- 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
Printing a Worksheet
To Print, Preview and Modify Page Setup
- Click on the File tab
- 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. Clicking on Page Setup will open a dialog box with four tabs: Page Margins Header/Footer Sheet
Page:
- Change the Orientation
- Adjust the Scaling
- Change the Paper Size Margins:
- Change the margins
- Center on the page either horizontally, vertically or select both Header/Footer:
- 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
- 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
- Click in a section to position your cursor
- Enter text/fields
- Click OK when finished Sheet Tab:
- Repeat Rows and Columns under Print Titles
- Check off what to print under Print
- Change the Page Order
