SBCUSD – IT Training Program Excel Introduction

SBCUSD – IT Training Program

Excel Introduction

Data Range Development, Formatting and Basic Formulas

Revised – Septmeber 18, 2019

Microsoft Excel Introduction 
    1 
TABLE OF CONTENTS 
Support for Microsoft Excel ........................................................................................................................................3 
Building Tables / Ranges .............................................................................................................................................4 
Range Creation .......................................................................................................................................................4 
Header Row ............................................................................................................................................................4 
Data Entry ...............................................................................................................................................................4 
Formatting ..............................................................................................................................................................4 
Cell/Cursor Movement ...............................................................................................................................................5 
Cell Selection ..............................................................................................................................................................6 
Selecting Entire Rows .............................................................................................................................................6 
Selecting Entire Columns ........................................................................................................................................6 
Multiple Range Selection ........................................................................................................................................6 
Adding Rows and Columns .........................................................................................................................................7 
Adding Rows ...........................................................................................................................................................7 
Adding Columns ......................................................................................................................................................7 
Adding Multiple Rows ............................................................................................................................................7 
Adding Multiple Columns .......................................................................................................................................8 
Deleting Rows, Columns, Cells ...................................................................................................................................9 
Deleting Rows .........................................................................................................................................................9 
Deleting Columns ...................................................................................................................................................9 
Deleting Multiple Rows ....................................................................................................................................... 10 
Deleting Multiple Columns .................................................................................................................................. 10 
Clear Cells ................................................................................................................................................................ 11 
Delete Cells .......................................................................................................................................................... 11 
Clear Content ....................................................................................................................................................... 11 
Clear Formats ...................................................................................................................................................... 12 
Clear All ................................................................................................................................................................ 12 
Sizing Rows and Columns ........................................................................................................................................ 13 
Sizing Rows .......................................................................................................................................................... 13 
Row Height .......................................................................................................................................................... 13 
Sizing Multiple Rows Identically .......................................................................................................................... 14 
Sizing Rows for the Entire Worksheet ................................................................................................................. 15 
Sizing Columns ..................................................................................................................................................... 16 

Microsoft Excel Introduction 
    2 
Column Width ...................................................................................................................................................... 16 
Sizing Multiple Columns Identically ..................................................................................................................... 17 
Auto Fit Column ................................................................................................................................................... 17 
Auto Fit Multiple Columns ................................................................................................................................... 18 
Sizing Columns for the Entire Worksheet ............................................................................................................ 19 
Show and Hide Rows and Columns ......................................................................................................................... 20 
Hide Row ............................................................................................................................................................. 20 
Hide Rows ............................................................................................................................................................ 21 
Show Row(s) ........................................................................................................................................................ 22 
Hide Column ........................................................................................................................................................ 23 
Hide Columns....................................................................................................................................................... 24 
Show Column(s) ................................................................................................................................................... 25 
Cell Formatting ........................................................................................................................................................ 27 
Cell Borders ......................................................................................................................................................... 27 
Cell Shading ......................................................................................................................................................... 28 
Format Painting ................................................................................................................................................... 28 
Cut, Copy, Paste ....................................................................................................................................................... 29 
Cut ....................................................................................................................................................................... 29 
Copy ..................................................................................................................................................................... 29 
Cut/Drag/Paste .................................................................................................................................................... 30 
Freeze Panes ............................................................................................................................................................ 31 
Freeze Left Column(s) .......................................................................................................................................... 31 
Freeze Top Row(s) ............................................................................................................................................... 31 
Freeze Row(s) and Column(s) .............................................................................................................................. 32 
Unfreeze Panes .................................................................................................................................................... 32 
Headers and Footers ............................................................................................................................................... 33 
Header ................................................................................................................................................................. 33 
Footer .................................................................................................................................................................. 34 
Remove Header or Footer ................................................................................................................................... 35 
Basic Functions and Formula Development ............................................................................................................ 36 
Formula Basics ..................................................................................................................................................... 36 
Formula Syntax .................................................................................................................................................... 36 
Using Common Formulas ........................................................................................................................................ 37 

Microsoft Excel Introduction 
    3 
Trim...................................................................................................................................................................... 37 
Average ................................................................................................................................................................ 37 
If ........................................................................................................................................................................... 37 
Printing .................................................................................................................................................................... 38 
Hiding Columns .................................................................................................................................................... 38 
View Page Breaks ................................................................................................................................................ 39 
Print Data Range .................................................................................................................................................. 39 
Repeat Rows on Each Printed Page ..................................................................................................................... 40 
Repeat Columns on Each Printed Page ............................................................................................................... 40 
Print Preview ........................................................................................................................................................... 41 
Shrink to Fit.......................................................................................................................................................... 41 
 
 
SUPPORT FOR MICROSOFT EXCEL 
You may contact the following office for assistance with Microsoft Excel: 
 
Training – Training Specialists 
[email protected]m 
(909) 386-2550 
   
   

Microsoft Excel Introduction

BUILDING TABLES / RANGES

RANGE CREATION

Place your headings in the first row and your data in the columns directly underneath. Excel has 1,000,000+

rows and 16,000+ columns.

HEADER ROW

Use bold font in the heading row. This will help Excel recognize headings when you use one of its functions,

such as sorting, filtering, etc.

DATA ENTRY

Be sure to enter data properly. For example, enter.54 for 54%.

FORMATTING

Keep formatting to a minimum. Less is more often better than more. Provide a few formats for the header row

(e.g. bold, shaded) and perhaps one for the totals row (e.g. shaded).

Note: When entering data into cell, if the cell already maintains an entry, that entry will be overwritten.

Microsoft Excel Introduction

CELL/CURSOR MOVEMENT

Using the mouse for cursor placement is often cumbersome and slow. Learn the following keystrokes to make

cursor movement much quicker.

Undo

Ctrl + Z

One cell in the direction of the arrow

Arrow Keys

One cell to the right

Tab

One cell to the left

Shift + Tab

To the edge of the current data region (the first or last cell that isn’t empty) in the

direction of the arrow

Ctrl + Arrow Key

To the last cell in the worksheet, in the lowest used row of the rightmost used column

Ctrl + End

To the beginning of the row

Home

To the beginning of the worksheet (cell A1)

Ctrl + Home

One screen down

Page Down

One screen up

Page Up

One screen to the right

Alt + Page Down

One screen to the left

Alt + Page Up

To the next sheet in the workbook

Ctrl + Page Down

To the previous sheet in the workbook

Ctrl + Page Up

Microsoft Excel Introduction

CELL SELECTION

SELECTING ENTIRE ROWS

1. Click and hold on a row number and drag down the row numbers of the rows you wish to highlight.

SELECTING ENTIRE COLUMNS

1. Click and hold on a column letter and drag across the column letters of the columns you wish to

highlight.

MULTIPLE RANGE SELECTION

1. Hold the Ctrl Key and click and drag across a range of cells.

2. Continue to hold the Ctrl Key and click and drag across another range of cells.

3. Continue to repeat this until you have all the ranges you wish highlighted.

Microsoft Excel Introduction

ADDING ROWS AND COLUMNS

ADDING ROWS

1. Right-click on the row number and choose Insert. A new row will appear above.

ADDING COLUMNS

1. Right-click on the column letter and choose Insert. A new column will appear to the left.

ADDING MULTIPLE ROWS

1. Highlight as many row as you wish to add.

2. Right-click on the highlighted cells.

3. Choose Insert.

The new rows will appear above the highlighted rows.

Microsoft Excel Introduction

ADDING MULTIPLE COLUMNS

1. Highlight as many columns as you wish to add.

2. Right-click on the highlighted cells.

3. Choose Insert.

The new columns will appear to the left of the highlighted columns.

Microsoft Excel Introduction

DELETING ROWS, COLUMNS, CELLS

DELETING ROWS

1. Right-click on the row number and choose Delete.

DELETING COLUMNS

1. Right-click on the column letter and choose Delete.

Microsoft Excel Introduction

DELETING MULTIPLE ROWS

1. Highlight as many row as you wish to delete. To do so, click on the 1

row number, hold down the Shift

key, and click on the last row number you want to delete.

2. Right-click on the highlighted cells.

3. Choose Delete.

DELETING MULTIPLE COLUMNS

1. Highlight as many columns as you wish to delete. To do so, click on the 1

column letter, hold down

the Shift key, and click on the last column letter you want to delete.

2. Right-click on the highlighted cells.

3. Choose Delete.

Microsoft Excel Introduction

CLEAR CELLS

DELETE CELLS

Right-click on a cell or a selected range of cells and choose Delete.

CLEAR CONTENT

1. To delete content (formulas and data) but not physically remove cells from the sheet, right-click in a

cell or selected range of cells.

2. Choose Clear Contents.

Microsoft Excel Introduction

CLEAR FORMATS

This will delete formats (including font, font size, text, bold and italic, color, and borders), but not the

content (formulas and data) from the selected cells.

1. Right-click in a cell or selected range of cells.

2. Click on the Clear button.

3. Choose Clear Formats.

CLEAR ALL

You can clear all to remove the cell contents (formulas and data), formats (including font, font size, text,

bold and italic, color, and borders), and any attached comments. The cleared cells remain as blank or

unformatted cells in the worksheet.

1. To delete cell content and cell formats, right-click in a cell or selected range of cells.

2. Click on the Clear button.

3. Choose Clear All.

Microsoft Excel Introduction

SIZING ROWS AND COLUMNS

SIZING ROWS

1. Click and hold and drag up or down to size the row as you wish.

ROW HEIGHT

On an Excel spreadsheet, you can set a row height between 0 to 255, with one unit equal to the width of

one character that can be displayed in a cell formatted with the standard font. Excel rows are 12.75 points

high by default.

1. Click on the row number you wish to make a specific height.

2. Right-click on the highlighted selection and choose Row Height.

3. Enter a specific row height and click OK.

Microsoft Excel Introduction

SIZING MULTIPLE ROWS IDENTICALLY

1. Click and drag across the row numbers of the rows you wish to size together.

2. Point to any one of the lines between the row numbers in your selection.

3. Click and drag up or down to adjust the row heights as you wish.

Microsoft Excel Introduction

SIZING ROWS FOR THE ENTIRE WORKSHEET

1. Click above row 1 and to the left of column A to highlight the entire worksheet.

2. Right-click on any cell and choose Row Height.

3. Enter the desired Row Height and click OK.

Microsoft Excel Introduction

SIZING COLUMNS

1. Point to the line to the right of the column letter you wish to size until you see a double arrow.

2. Click and hold and drag right or left to size the column as you wish.

COLUMN WIDTH

1. Click on the column letter you wish to make a specific width.

2. Right-click on the highlighted selection and choose Column Width.

3. Enter a specific column width and click OK.

Microsoft Excel Introduction

SIZING MULTIPLE COLUMNS IDENTICALLY

1. Click and drag across the column letters of the columns you wish to size together.

2. Point to any one of the lines between the column letters in your selection.

3. Click and drag left or right to adjust the column widths as you wish.

AUTO FIT COLUMN

1. Point to the line to the right of the column you wish to auto fit.

2. Double-click on the line.

Microsoft Excel Introduction

AUTO FIT MULTIPLE COLUMNS

1. Click and drag across the column letters to highlight the columns you wish to auto fit.

2. Double-click on any one of the lines between column letters in your selection.

Microsoft Excel Introduction

SIZING COLUMNS FOR THE ENTIRE WORKSHEET

This permanently changes the column width.

1. Click above row 1 and to the left of column A to highlight the entire worksheet.

2. Right-click on any column letter and choose Column Width.

3. Enter the desired Column Width and click OK.

Microsoft Excel Introduction

SHOW AND HIDE ROWS AND COLUMNS

You can hide rows or columns in your spreadsheet to make your spreadsheet easier to view or print.

HIDE ROW

1. Click on the row number to highlight the row you wish to hide.

2. Right-click on the highlighted selection and choose Hide.

Microsoft Excel Introduction

HIDE ROWS

1. Click and drag across row numbers you wish hide.

2. Right-click on the highlighted selection and choose Hide.

Microsoft Excel Introduction

SHOW ROW(S)

1. Find the double row icon. It indicates where your hidden row(s) are located.

2. Highlight the row above and below the hidden rows selection.

3. Right-click on the highlighted rows and choose Unhide.

4. The rows will appear.

Microsoft Excel Introduction

HIDE COLUMN

1. Click on the column letter to highlight the column you wish to hide.

2. Right-click on the highlighted selection and choose Hide.

Microsoft Excel Introduction

HIDE COLUMNS

1. Click and drag across column letters you wish to hide.

2. Right-click on the highlighted selection and choose Hide.

Microsoft Excel Introduction

SHOW COLUMN(S)

1. Find the double column icon. It indicates where your hidden column(s) are located.

2. Highlight the columns to the right and left of where the column(s) are hidden.

3. Right-click on the highlighted column(s) and choose Unhide.

Microsoft Excel Introduction

4. The column(s) will appear.

Microsoft Excel Introduction

CELL FORMATTING

CELL BORDERS

1. Highlight the range of cells you wish to create a border for.

2. Right-click on the highlighted range and choose Format Cells.

3. In the Format Cells window, click on the Border tab.

4. Click on the Style.

5. Choose the Color.

6. Click on the None, Outline, or Inside to choose if lines will be colored and which ones.

7. Click on the Border buttons until you find the way you wish the cell borders to look.

8. Click the OK button.

Microsoft Excel Introduction

CELL SHADING

1. Highlight the range of cells you wish to color.

2. Right-click on the highlighted range and choose Format Cells.

3. In the Format Cells window, click on the Fill tab.

4. Choose the color you wish to fill into the range of cells you have selected.

5. Click on OK.

FORMAT PAINTING

Format painting allows you to copy formatting from a range of cells and paste it somewhere else in the

worksheet.

1. Place your cursor in a cell which has the formats you wish to duplicate.

2. Double-click on the Format Painter button, so that the button remains highlighted.

3. Click on a cell or a range of cells you wish to apply the format to.

The cells will be formatted in the same way as the original cell you selected.

4. Click the Escape key to exit end the format painting. You can also click on the Format Painter button.

Microsoft Excel Introduction

CUT, COPY, PASTE

CUT

1. Highlight a cell or range of cells.

2. Right-click on the selected cells and choose Cut.

3. Place your cursor in the cell where the upper left of the range you have selected will fall.

4. Right-click and choose an option under Paste Options.

COPY

1. Highlight a cell or range of cells you wish to copy.

2. Right-click on the selected cells and choose Copy.

3. Place your cursor in the cell where the upper left of the range you have selected will fall.

4. Right-click and choose an option under Paste Options.

Microsoft Excel Introduction

CUT/DRAG/PASTE

1. Highlight a cell or range of cells you wish to move.

2. Place your mouse pointed directly over the border/edge of the cells selected.

3. Drag the mouse to move the cells.

4. Release the mouse button where you wish to drop the range of cells.

Note: The paste feature maintains a number of paste choices including Values, Formats, and Formulas.

Microsoft Excel Introduction

FREEZE PANES

When working with a large data range, you may often wish to move through the range while still being able to

see the far left column or the first row of column headings.

FREEZE LEFT COLUMN(S)

1. Click on cell A2 (assuming your row 1 is the Header).

2. Click on the View tab.

3. Click on the Freeze Panes drop down arrow and choose Freeze First Column.

As you scroll right through your range, you’ll see you have frozen the column at the left. It will remain

visible as you scroll far right.

FREEZE TOP ROW(S)

4. Click on cell A2 (assuming your row 1 is the Header).

5. Click on the View tab.

6. Click on the Freeze Panes drop down arrow and choose Freeze Top Row.

As you scroll down your range, you’ll see you have frozen the row(s) at the top. They will remain visible

as you scroll far down in your range.

Microsoft Excel Introduction

FREEZE ROW(S) AND COLUMN(S)

1. Click on the cell which falls directly under the row(s) and also falls immediately to the right of the

column(s) you wish to freeze (B2).

2. Click on the View tab.

3. Click on the Freeze Panes button and choose Freeze Panes.

As you scroll right and down through your range, you’ll see you have frozen both the row(s) and

column(s) to the left and at top. They will remain visible no matter where you locate yourself in this

sheet.

UNFREEZE PANES

1. From any cell within the sheet which has frozen panes, click on the View tab.

2. Click on the Freeze Panes button and choose Unfreeze Panes.

Microsoft Excel Introduction

HEADERS AND FOOTERS

You can add headers or footers at the top or bottom of a printed worksheet in Excel. For example, you might

create a footer that has page numbers, the date, and the name of your file. You can create your own, or use

many built-in headers and footers.

HEADER

1. Click on the Insert tab.

2. Click on the Header/Footer button in the Text group.

3. Enter Header text in the upper left, middle and/or upper right of the sheet.

Note: The Header & Footer button may not be visible. If so, click on the drop-down menu below the

Text button.

Microsoft Excel Introduction

FOOTER

1. Click on the Insert tab.

2. Click on the Header/Footer button in the Text group.

3. Scroll to the bottom of the spreadsheet and enter the Footer text in the bottom left, middle and/or

bottom right of the sheet.

Note: The Header & Footer button may not be visible. If so, click on the drop-down menu below the

Text button.

Microsoft Excel Introduction

REMOVE HEADER OR FOOTER

1. Click on the Insert tab.

2. Click on the Header/Footer button in the Text group.

3. Click in the left, center, or right header or the footer text box at the top or the bottom of the

worksheet page.

4. Press the Delete or Backspace key.

5. Click on the left edge of the sheet and click once to hide the empty header and footers.

6. Click on the View tab and click on the Normal button to exit the Page Layout view.

Microsoft Excel Introduction

BASIC FUNCTIONS AND FORMULA DEVELOPMENT

At its heart, Excel is a giant calculator. In fact, a simple way to think about Excel is to consider each cell in a

worksheet like an individual calculator. An Excel spreadsheet has over a million cells, which means you have a

million individual calculators to work with. Not only that, but you can create formulas that link different cells

together, e.g. add the value in this cell to the value in that cell.

FORMULA BASICS

All formulas begin with the = sign. This alerts Excel that the entry is a formula.

Example in Adding: =SUM(5,5) The resulting cell value will be 10

Example in Subtraction: =SUM(a4,-a3), Multiplication =SUM(a3,*a4), Division =SUM(a3,/a4)

FORMULA SYNTAX

A formula can also contain any or all of the following: functions, references, operators, and constants.

Functions: The SUM Function allows you to do basic arithmetic within it’s parentheses

References: D45 returns the value currently in cell D45, E45 returns the current value of cell E45

Constants: Numbers or text values entered directly into a formula, such as .0825

Operators: The * (asterisk) operator multiplies numbers.

Note: Wherever your cursor is located in the sheet, the Formula Bar will show you it’s true contents. This will

confirm whether or not the value in the cell is a static value entered in the cell or a value that is the result of

a formula otherwise hiding in the cell.

Microsoft Excel Introduction

USING COMMON FORMULAS

TRIM

Trim Removes all spaces in the cell except for spaces between words or values. Effectively removes errant

spaces entered before or after the typed data.

Formula: =TRIM(A1:J342)

AVERAGE

The AVERAGE function finds an average, or arithmetic mean, of numbers.

Formula: =AVERAGE(d2:d45)

The IF formula to ask Excel to test a certain condition and return one value or perform one calculation if the

condition is met, and another value or calculation if the condition is not met.

IF(A1 <= 25; "order", "in stock")

Microsoft Excel Introduction

PRINTING

When working with a large data range that is much wider and taller than your standard 8.5 x 11 print job,

you’ll need to go through some extra steps in order to properly print the sheet. The page setup feature in excel

maintains a lot of printing options.

HIDING COLUMNS

1. Hold the Control key and click on the column letters you don’t wish to display in the print job.

2. Right-click on one of the selected column letters and choose Hide.

3. Go to File > Print and choose Print Active Sheets.

Unhide Columns:

a. Click and drag across the column letters in order to highlight the columns to the left and right of the

column you’re hiding.

Now you have the column to left and right as well as the hidden column in-between selected.

b. Right-click anywhere on the selection and choose Unhide.

B is hiding

Microsoft Excel Introduction

VIEW PAGE BREAKS

Page breaks are dividers that break a worksheet into separate pages for printing. Microsoft Excel inserts

automatic page breaks based on the paper size, margin settings, scale options, and the positions of any manual

page breaks that you insert. To print a worksheet with the exact number of pages that you want, you can

adjust the page breaks in the worksheet before you print it.

1. Click on the View tab.

2. Click on the Page Break Preview button.

The page breaks are now visible and denoted with a blue line. This view will

give you an idea on how you wish to further use Page Setup features.

3. Click on the View tab.

4. Click on the Normal View button.

PRINT DATA RANGE

A data range is a solid block of data (known as a table or

range) that does not contain any empty rows or columns

anywhere within the range.

1. Click on the Page Layout tab.

2. Click on the

arrow in the

bottom right

corner of Page Setup (show/hide).

3. Click on the Sheet tab.

4. Click on the Data Range button to the right of the

Print area field.

5. Now, click and drag to highlight and select the

entire data range you wish to print.

6. Click on the Data Range button again.

You are now back in the Page Setup window and you have defined the cell range which you wish to

print.

7. Choose the Print Preview button to view the area you have selected.

Microsoft Excel Introduction

REPEAT ROWS ON EACH PRINTED PAGE

If a worksheet spans more than one printed page, you can label data by adding row headings that will appear on

each print page.

1. Click on the Page Layout tab.

2. Click on the Print Titles button and click on the Sheet tab.

3. Click on the Data Range button to the right of the Rows to repeat at top field.

4. Now, click or click and drag on the row number(s) that maintain the row(s) you wish to repeat at the

top of each printed page.

5. Click on the Data Range button again.

You are now back in the Page Setup window and you have defined which row(s) you wish to repeat on

each printed page.

REPEAT COLUMNS ON EACH PRINTED PAGE

This allows you to repeat specific columns on every printed page such as the far left column.

1. Click on the Page Layout tab.

2. Click on the Print Titles button and click on the Sheet tab.

3. Click on the Data Range button at the right of the Columns to repeat at left field.

4. Now, click or click and drag on the column letter(s) that maintain the columns(s) you wish to repeat at

the left of each printed page.

5. Click on the Data Range button again.

You are now back in the Page Setup window and you have defined which column(s) you wish to repeat

on each printed page.

Microsoft Excel Introduction

PRINT PREVIEW

Once you have completed your print setup with the above techniques, it is important to use the Print Preview

feature.

1. Click on the Page Layout tab.

2. Click on the Print Tiles button.

3. Click on the Print Preview button.

This is how your printed pages will be structured.

SHRINK TO FIT

You may shrink (scaling of entire data range) your data range in order to rid the print job of pages which are on

the far right and or along the bottom that carry only a column or two or a few rows.

1. Click on the File tab and click on Print.

2. Below all the print options, click on Page Setup.

3. Under Scaling, choose Fit to and enter the amount of rows and columns of pages you have ready to

print, minus 1.

This allows you to “clip” off the far right pages and the bottom pages which are maintaining very little

content.