Formatting in Microsoft Excel



Introduction to Microsoft Excel

Parts of the Screen

Define the Term Spreadsheet:

List Examples of How A Spreadsheet Could Be Used:

Identify And Label The Parts Of The Window Using The Blocks Provided.

Microsoft Excel – Navigation & Recording Data

Microsoft Excel is a spreadsheet program that stores information in a workbook made up on one or more worksheets. Each worksheet contains a grid with rows and columns. The worksheets can be used to record numerical data, perform mathematical calculations, and display charts.

The last column letter is: __________________

The last row number is: __________________

The first cell address is: __________________

The last cell address is: __________________

The intersection of a row and a column is called a ___________________.

The cell address for a selected cell can be displayed in the ______________________.

Mouse Pointers:

Selection Tool

Autofill Handle

Autofit Pointer

Select Column Pointer

Select Row Pointer

Move/Copy Pointer

Types of Data: Text

Numbers (or values)

Dates/Months/Times (calendar data)

Formulas (equations)

Automatic Features:

____________________ A feature used to reduce the amount of typing by automatically filling in repeated data that appears in the same column.

____________________ A feature used by the program to predict the group of cells that could be added together to calculate a total.

____________________ A feature used to adjust the width of a column by double-clicking on the right edge of a column header.

____________________ A feature used to reduce typing of numeric or calendar data by dragging a small crosshair pointer at the bottom right corner of a cell.

Microsoft Excel – Formatting Options

Steps to Apply Number Formatting:

1. Click on the Home Tab

2. Expand the “Number” Formatting Options

3. Click on “Number” Tab in Window

Click on Categories to Select Number a Formatting Option. A description of the selected category will appear in the window.

Choose from additional options that appear to the right of the categories.

Example: Currency (See window to right)

Description: Currency formats are used for monetary values (money) to align decimal points in a column.

Additional Options: Choose the number of digits to appear after the decimal point. Choose the desired symbol ($). Negative numbers can be formatted using a minus sign (-), parentheses ( ) symbols, or the color red.

Steps to Align Text/Numbers:

Alignment options control where text/numbers are positioned in each cell. For example, the text can be formatted line up on the left of a cell, the right, or in the center.

1. Click on the Home Tab

2. Expand the “Number” Formatting Options

3. Click on the Alignment Tab

* Horizontal Alignment options can also be applied using the formatting options on the ribbon.

Orientation: Use to turn text in a cell vertically or on an angle. (Under Text Alignment in Window)

Wrap Text: Use to create multiple lines (similar to a paragraph) within one cell. (Found under Text Control in Window)

Microsoft Excel – Organizing Data

Excel Workbooks – Getting Organized

Workbook = collection of worksheets in a single spreadsheet file

Rename Worksheets

• Double-click on Sheet Tab (Sheet1)

• Type label (text)

• Enter Key

Add Worksheets

• On the Ribbon, Click on the “Home” Tab.

• In the Cell Group on the Ribbon, Choose Insert.

• In the drop-down list, choose “Worksheet”.

• A new worksheet will appear at the bottom of the Excel screen.

Users can also click on the + button located to the right of the Sheet1 Tab at the bottom of the screen.

Add Rows/Add Columns

• Select the location on the spreadsheet where you would like a new row or column to appear.

• On the Ribbon, Click on the “Home” Tab.

• In the Cell Group on the Ribbon, Choose Insert.

• In the drop-down list, choose “Insert Sheet Rows” or “Insert Sheet Columns”.

• An existing row will be pushed down; an existing column will be pushed to the right.)

Delete Worksheet

• Click on Worksheet to Delete

• In the Cell Group on the Ribbon, Choose Delete.

• In the drop-down list, choose “Delete Worksheet”.

Move Worksheets

• Click on Sheet Tab to Move.

• Click and Drag to New Location (to left or right).

• Release when the triangle points at the correct location. (Sheets will be rearranged.)

Copy Worksheet:

• Click on the square ABOVE the Row A label. This will select the entire worksheet.

• Click on the Copy Button (on left of Ribbon, Home Tab)

• Add a New Worksheet.

• Click on the Paste Button.

Copy Cells:

• Select cells to copy ( Copy Button (on left of Ribbon, Home Tab)

• Click in new location. (Be sure to click in the cell that will become the TOP LEFT corner of the copied data.)

• Click on the Paste Button (on left of Ribbon, Home Tab)

Microsoft Excel – Page Setup & Printing

Adding a Header (before printing):

Steps:

➢ Click on the Page

Layout Tab

(above Ribbon)

➢ Click on the Page Setup Expand Button (to open window pictured to the right).

➢ Click on the Header and Footer Tab

➢ Click Custom Header Button and Enter:

➢ Click in Each Section and Enter the Following:

• Left Block – Your Name

• Center Block – Leave Empty

• Right Block – Assignment Name

Print Preview

Always check to see that your work fits on one page whenever possible.

➢ File Tab (above Ribbon)

➢ Print

** The screen will display a preview of the printed document on the right of the print window. If the data does not fit on one page, use Step 3 to adjust the placement of the data.

The Page Number and the Total Pages appear at the bottom of the Print Window.

A spreadsheet that fits on ONE Page will read “1 of 1”.

▪ If the data fits on the page, choose the PRINT button in the top left.

▪ If the data does not fit on ONE page, use the tips that follow below under the heading “Arranging Data to Fit on One Page”.

Arranging Data To Fit on ONE Page

Tip #1: Change the Orientation

Spreadsheets normally print in Portrait Orientation (the page prints vertically). Change the orientation to Landscape to turn the page so that it is wider across when printing.

Steps to Change Orientation:

➢ Click on the Page Layout Tab (above Ribbon)

➢ Click on the Orientation Button

➢ Choose Landscape.

Tip #2: Change the Column Width

Change the width of one or more columns to fit data on one printed page.

Steps to Change Column Width:

➢ Place the mouse pointer over the RIGHT EDGE of a column label (letters).

➢ Find the double-headed arrow pointer, and hold down the left mouse button.

➢ Drag the mouse to the left to make the column more narrow.

OR

Drag the mouse to the right to make the column wider.

** You can also double-click on the RIGHT EDGE of a column to autofit the width to fit the longest item in the column. (Do not cut off text.)

Tip #3: Printing Option to Fit to One Page

➢ File Tab (above Ribbon)

➢ Print

➢ Under Settings, Click on “No Scaling”

➢ Choose the “Fit Sheet on One Page” option to shrink the overall printout so that it is ONE PAGE wide.

➢ PRINT.

• Note that the Orientation Option is also available in the PRINT Window under Settings.

Microsoft Excel

Performing Mathematical Calculations

Mathematical Tools: Microsoft Excel has tools available to assist individuals with performing mathematical calculations. These tools can be used to complete common tasks such as addition, subtraction, multiplication, division, averages, percentages, and much more.

Formulas

Formulas are used to perform the math in Excel. (Make the program do the work….no calculators!) Cell addresses and mathematical operators are used to complete an equation. Excel will recognize that you want to perform an operation when the formula begins with the equal sign (=).

Review of Operators In Excel

Addition +

Subtraction -

Multiplication * (not x or • )

Division / (not ÷ )

Step-by-Step Instructions for Creating Formulas

Steps: Example #1: Example #2:

1. Always start a formula with the = sign. = =

2. Add a cell address (example 1) or value (example 2) = A1 = 5

3. Add an operator ( + - * / ) = A1 + = 5 *

4. Add a second cell address or value. = A1 + A2 = 5 * 88

*** Continue this pattern to use additional cell addresses or values in your formulas.

Examples:

-----------------------

UNIT OUTCOME: Students will be able to use a spreadsheet to record, organize, and format text and numerical data.

OUTCOME: Students will be able to navigate and enter simple text and numeric data in a spreadsheet.

TROUBLESHOOTING TIP:

If the ########## symbols appear in a cell, it means that the numbers do not have enough space to display. The symbols will disappear and print correctly after the column width is increased.

OUTCOME:

Students will format numeric data and align text in cells.

OUTCOME: Students will organize information in rows, columns, and worksheets by using options to insert, delete, copy, move, and rename.

** Right-Click (on Mouse) Shortcut To delete Sheet:

• Right-click on Sheet Tab

• Click on Delete

TROUBLESHOOTING TIP

Advanced Formatting Option to Copy/Paste

Cells with FORMULAS:

• To copy a NUMBER in a cell containing a formula, use the following steps to paste ONLY THE NUMERIC VALUE to a second cell (applies to any worksheet in workbook):

o Copy the Cell.

o Click on the new cell.

o On the Ribbon, Home Tab, Click on the Paste Button.

o Go to the group labeled “Values” under Paste, and choose Paste Values (123).

(This will make a copy of the NUMBER instead of copying the formula and any error messages.)

o The number will be copied to the new cell. Format as needed.

OUTCOME: Students will be able to apply page setup options to include a header, change the orientation, and fit data on one page.

OUTCOME: Students will use formulas with values and cell addresses to perform mathematical operations without the use of pencil/paper or a calculator.

................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download