Excel – Toolkit

Introduces several useful tools for formatting, displaying, and entering data.

Instructions:

Read through the set of learning objectives. This is what you should know when you finish the lesson.

Watch the YouTube videos before starting the lesson.

Complete every step in the assignment, in the order given. Do not skip steps.

When you have done all of the steps, fill out the Check for Understanding (CFU) form.

A completed CFU for every lesson, in-class and homework, is due to be turned in during your next class. 

The CFU forms are not to be submitted by email.

Where there are differences between software versions, the instructions for Excel 2010 are in Black, for the Mac are in green and for Excel 2013 are in blue.

Summary of Key Points:

Use the AutoFill Handle to quickly enter content into multiple cells.

Apply special date and number formats to cells.

Change column widths and wrap text.

Add comments to cells.

Work with multiple worksheets.

Work with ranges and create range names.

Use data validation to restrict the type of data that can be entered into a cell.

Freeze columns and rows so they remain on screen at all times.

Protect a worksheet.

 

Y/N I can use the AutoFill Handle to quickly enter content into multiple cells.

Y/N I can apply special date and number formats to cells.

Y/N I can change column widths and wrap text to make the worksheet easier to read.

Y/N I can add comments to cells.

Y/N I can work with multiple worksheets.

Y/N I can work with ranges and create range names.

Y/N I can use data validation to restrict the type of data that can be entered into a cell.

Y/N I can freeze columns and rows so they remain on screen at all times.

Y/N I can protect a worksheet.

 

Videos:

Hotkeys

AutoFill Handle

Special Formats

Wrap Text

Formula Bar

Freeze Panes

Protection

 

AutoSum Σ (Alt + '=')   1:58

AutoFill, Double-click on AutoFill Handle   1:17

How to use Flash Fill   2:15

 

Open a new Excel workbook. The names of the worksheets are at the bottom. We will work in Sheet1. Keyboard shortcuts for icons are shown in [brackets] and the keys are pressed in a sequence, not all at the same time.

Start by watching this excellent video about Hotkeys and this one about keyboard shortcuts https://www.youtube.com/watch?v=-JhLtw78ZYI

The AutoFill Handle

This tool helps quickly enter data. Let’s start by using the AutoFill handle to extend a series of numbers into some empty cells. Here’s a video: AutoFill Handle

  1. In a blank worksheet, enter a 1 in cell A1, a 2 in cell A2, and a 3 in cell A3.
  1. Click and drag to select all three cells. You’ll see a thick box border around the cells. The cursor will be a white plus sign. Move the cursor to the lower right corner of the border. It should change to a smaller black plus sign. This black plus sign is the AutoFill handle.
  1. Click on the AutoFill handle and drag down over cells A4, A5 and A6 and then release the mouse.  You will see the numbers 4, 5 and 6 in those cells.  The autofill handle can be used to extend any series of numbers that show a regular pattern.

The AutoFill handle will copy numbers into empty cells.

  1. Click on cell A1 and then move the cursor to the lower right corner to bring up the AutoFill handle. Click on the handle and drag to the right across cells B1 C1 D1 and E1.  Since only one number (cell) was selected, the autofill handle copies that number into every cell.

The AutoFill handle will copy text into empty cells.

  1. Enter your name in cell A7. Click to select the cell then move the cursor to the lower right corner to bring up the AutoFill handle. Click on the handle and drag to the right across cells B7 C7 D7 and E7. 

The AutoFill handle will copy formulas into empty cells.

  1. Enter the formula = A1 in cell A8. Click to select the cell then move the cursor to the lower right corner to bring up the AutoFill handle. Click on the handle and drag to the right across cells B8 C8 D8 and E8. Notice that the cell reference in the formula, A1, changed as you copied the formula into new columns.

The AutoFill handle will recognize days of the week and extend them series into empty cells. It will also recognize Week1, Week2, etc. or QTR1, Qtr2, etc. as series.

  1. Enter the word Monday in cell A9. Click to select the cell then move the cursor to the lower right corner to bring up the AutoFill handle. Click on the handle and drag to the right across cells B9 C9 D9 and E9.

The AutoFill handle will recognize dates and extend the series into empty cells. Dates can be entered using either a dash 5-10 or a forward slash 5/5. Do not include an equal sign = when entering dates. The equal sign causes Excel to interpret the date as a math formula, which you don’t want.

  1. Enter the date “5-1” (Don’t include the quotation marks) into cell A10. Click to select the cell then move the cursor to the lower right corner to bring up the AutoFill handle (the small black cross). Click on the handle and drag to the right across cells B10 C10 D10 and E10.

The AutoFill handle will work in all directions.

  1. Enter the date “5/5” (Don’t include the quotation marks) into cell E11. Click to select the cell then move the cursor to the lower right corner to bring up the AutoFill handle. Click on the handle and drag to the LEFT, across cells D11 C11 B11 and A11.
  1. In some versions of Excel, but not in all, if you right click on the AutoFill handle before dragging it you will see a popup menu with several options. Try it.

Special Cell Formats

There are many special format options include in the cell formats dialog box. Watch the format cells dialog box video: Special Formats

We can format dates.

  1. Right click on cell A10 and select Format Cells. A dialog box opens with several tabs. 

In the number tab you’ll see a category group on the left with options to give cells a special format.

Select the Date option.

On the right scroll through the list of date options and select one that shows the year as part of the date. 

Click Ok to apply the format.

Or format phone numbers.

  1. Click on cell A12. Type the 10 digits of a phone number and press Enter. Do not include the 1 before the area code or any spaces, dashes, or parentheses.
  1. Click on cell A12 again. Press Ctrl +1 to bring up the cell formatting dialog box. (If Ctrl + 1 doesn’t work, right click and choose format cells) In the number categories section select Special and then select Phone number on the right. Press OK. Now parentheses, spaces, and a dash will be applied to the digits you entered into cell A12.  

Or format fractions

  1. Click on cell A13. Type in =3/5. In general format you would see the value 0.6. Right click in cell A13 and choose Format Cells. In the category box choose Fraction. Press Ok. Excel will show 3/5 in the cell.  You can also enter a fraction directly by starting with a space instead of the equal sign.

To enter mixed numbers and fractions into cells: type in the integers press Spacebar and then type in the fraction. Try that in cell B13 “ 3/5” (without the quotes). In cell C13 try entering “3 3/5” (without the quotes).

You can also format a cell to automatically add a descriptive label to a number.

  1. Click on cell A13 again and enter the number 12.

Right click in A13 and choose Format Cells.

In the category box choose Custom.

On the right you will see a list of custom options. Click in the input box above the list, below the word Type. Delete the entry there, General, and type ## “ days”.

The # sign is used as a placeholder for integers. The description is entered between quotation marks. Make sure to include a space in front of the word days.

Click on OK to apply the new format. You should see “12 days” in the cell.

Note: whenever you enter text into Excel it should be included within quotation marks. If you forget them you will probably see this error message #/NAME#?

  1. Now enter the number 17 into cell A13. You should now see “17 days” appear in the cell.
  1. What happens when you enter a three-digit or a single-digit number into cell A13? Try it.
  1. Click in cell A10. Type in the number 10 and press enter. You will see the date January 10, 1900 instead of the number 10. Excel keeps track of dates by giving a number to each one. The number is the number of days that have passed since December 31, 1899.  When you enter a number, or a formula that results in a number, into a cell that is formatted to show dates, you will see a date instead of the number you were expecting.

If you accidentally format a cell with a special format, you can return it to the default setting with the format paintbrush icon. Let’s remove the custom formatting in cell A13 and the date format in cell A10.

  1. Click on cell G10, to select it. In the Home tab of the ribbon you will see the word General in the numbers section of the ribbon. That is the default setting. If you don’t see General click on other cells until you find one with that format.
  1. Click on the Format Painter icon [ALT  H  F  P] on the Home section of the ribbon. In some MacBooks this icon is found above the ribbon instead of in the Home tab.) This selects the format of cell that you just clicked on. You will see a moving outline around cell G10 You will see a paintbrush as the cursor when you move the mouse pointer.
  1. Click on cell A13. This pastes the default General format into cell A13.  The word “ days” in cell A13 should vanish. Repeat the process to reformat cell A11. You should now see a five digit number. Excel converts dates into numbers automatically, using the number of days after January 1, 1900 as the value for the date. In the year 2016 the number showing is 42491. If your date was entered as a different year, you would see a different number.

There are several symbols you can use to format the way your output appears. Here are some useful ones.

  1. Click on cell A13 again and enter the number 123456789.

Right click and choose Format Cells.

In the category box choose Custom.

Click in the box above the list, below the word Type.

Delete the entry there, General, and type $#.00,, " m" 

·         The $ sign is a label that will appear next to your number. 

·         The # sign is used as a placeholder for integers.

·         The decimal point and the zeroes tell you how many decimal places to show.  Each comma tells Excel to hide the smallest three digits of the number, so by using two commas we are seeing the value in millions.

·         “m” adds the label m to the number to show that the value displayed is in millions.

Click on OK to apply the new format. You should now see $123.46 m in the cell.

You don’t see $123.45 because the next numeral after the 5 was a 6, so Excel rounded up. 

Resizing Columns and Wrapping Text

When you see this error message: “#######”. It means the column is too narrow to display the number. You can widen the column to fix this.

  1. Put your cursor between A and B in the top row of the worksheet. You will see a vertical line with arrows pointing left and right. Drag halfway across column A. Do not drag all the way to the left as that will hide the column. Release the mouse. You should see railroad tracks (####) in one or more cells. If you don’t see railroad tracks, drag a bit more to the left.
  1. Put your cursor between A and B in the top row of the worksheet. Double click on the vertical line with arrows to resize the column. You should see all of the cell contents in column A.
  1. Press Ctrl + Z to make the column too narrow again. Click in cell A1.
  1. On the home tab, click on the Format icon [ALT  H  O]. For the MAC, there is no format icon. It’s in the menu bar at the top of the window. You will see options here to change row heights and column widths.  Use the column width option to make column A wide enough to remove the railroad tracks.

Note that different versions of the software measure column width in different units. Some versions use inches, come use space widths. 

Wrapping Text

You can use the wrap text icon to automatically break up a long string of text into shorter segments. Video:  Wrap Text

  1. Click on cell A16. Type “I know how to wrap text now.” (Don’t include the quotation marks) and press Enter. Click on cell A16 again.
  1. Click on the Wrap Text icon [ALT  H  W] on the Home tab. If you can’t find the Wrap Text icon maximize the worksheet. WARNING! If you press all three keys at the same time your worksheet will disappear. Press Alt first, then H, then W.

After you have applied the Wrap Text format you might have to make the column narrower and/or increase the row height to see the difference.

Put the cursor between the row numbers 16 and 17 on the left side of the worksheet; you will see a horizontal line with the arrows pointing up and down. Click and drag down to make this row about three times as tall as the other rows.

Put your cursor between A and B in the top row of the worksheet. Click on the line with arrows and drag to the left. The message will start to wrap around and continue on lines two and three.

Note: You can also manually create your own text breaks. Just click anywhere in the text where you want the line to break and press ALT + ENTER.

Messages and Labels

There are many ways to add labels and messages to the cells in the worksheet. Perhaps the easiest is to use the ampersand symbol (&) as part of the formula. The ampersand symbol is used to combine text with numbers. Note that text in a formula must always be enclosed in double-quotation marks.

  1. Click on cell A14. Type this formula =A4&” is the number in cell A4.” Make sure you include the space before the word “is”. Press Enter. Cell A14 should say “4 is the number in cell A4.” Note: Excel can’t read formulas if they are copied and pasted directly into a cell, but it can read them if you click on the arrow below the Paste icon. In Excel 2013 use [ALT  H  V  S] and choose the “text” option before pasting the formula.
  1. Try changing the number in cell A4. Did the message change in cell A14?

 

Another way to add a description to a cell is through the use of a Comment.

In Excel 2010, adding a comment is super easy and the same as 2013. Just click on the Review tab and you’ll see all the commenting tools. Note: The Show Ink icon only works with a tablet PC.

Click on the cell you want to add the comment to and then click on New Comment.

review new comment

A small dialog window will appear where you can begin typing in text. You’ll also notice the small little red triangle at the top right of the cell, which is there to indicate that cell has a comment.

comment added

You can quickly see all comments on a worksheet by clicking on the Show All Comments button. This comes in handy if the sheet has a bunch of comments. Click on Previous and Next to cycle through all of the comments one by one.

It can be quicker to use the pop-up menu when working with individual comments.

  1. Click on cell A15. Right click and select Insert Comment. Type “This is cell A15” in the text box and click in the worksheet to enter the comment.
  1. When you click on another cell the comment disappears. A small red triangle appears in cell A15. This is to let you know a comment is there. Mouse over cell A15 again to see the comment. 

You can  use the pop-up menu to edit or delete a comment, move and resize the comment textbox, to  show the comment all of the time, or to show the comment only when the cell is selected.

  1. Click on cell A15. Right click and you will see an option to Show/Hide the comment. You will also see an option to edit the comment. Click on the edit option. Remove the words “This is”. Don’t enter the change yet, stay in edit mode.
  1. Resize and then move the comment box. Now enter the edited comment. Click in any part of the worksheet to finish.
  1. Click on cell A15, right-click and choose Delete comment.

You can also use Data Validation to format a cell with instructions to the persons using the worksheet.  Let’s do that for the cell with the phone number format.

  1. Click on cell A12. On the Data section of the ribbon, click on the Data Validation icon [ALT  A  V].  In Office 2013 the keyboard shortcut is [ALT A V V]. For the MAC use the Data icon option on the menu bar. Select data Validation. A dialog box opens. Select the middle tab, “Input Messages”
  1. In the main message box type “Type in the ten digits of your phone number without spaces, dashes, or parentheses.” and enter it by clicking OK.
  1. Click on cell A12. Does the message appear? Enter a different phone number. Try typing something other than ten digits.

 

Using more than one worksheet

The worksheet names appear in tabs at the bottom of the screen. Note that Excel 2013 opens with just one worksheet. You can press the + icon to the right of “Sheet1” to add more worksheets.

You can switch between worksheets by pressing Ctrl + PageUp or Ctrl + PageDown (For the MAC press Command plus FN) or by using the four icons to the left of the worksheet names, above the word Ready. 

·         The first icon will move you to the first worksheet in the workbook.

·         The second icon will move you to the next worksheet on the left of your current worksheet.

·         The third icon will move you to the next worksheet on the right of your current worksheet.

·         The fourth icon will move you to the last worksheet in the workbook.

·         Note in Excel 2013 you only see two icons. These icons are inactive unless you have more worksheets open than can be shown at one time. Right-click on one of these icons to see a list of worksheets. Click on the one you want to work with.

  1. Practice using both the keyboard method and the icon method to move between the worksheets.
  1.  Click and drag on the worksheet tabs to change their order.

To write a cell references in a formula that refer to a cell in a different worksheet, simply include the name of the worksheet as part of the cell reference. For example =Sheet2!B4 or =Sheet3!C5.

  1. Select the Sheet2 worksheet.  Click in cell A1 and enter =Sheet1!A2.  You will see a 2. If you see #NAME?# you probably left out the exclamation mark.  If you see #REF! Excel couldn’t find a worksheet named Sheet1.
  1. The icon to the right of the worksheet names (the worksheet names are on tabs at the bottom of the Excel window) can be used to insert additional worksheets. Click on it to insert a new worksheet. Right-click on the new worksheet’s tab and rename it  “Ranges” Do not include the quotation marks.

Ranges

  1. Select the Ranges worksheet. Enter the numbers and text from the table into cells A1:D4 of the worksheet. Remember, you can copy and paste if you use the “paste special” and select the ‘text’ option. WARNING! The Safari browser does not copy and paste tables correctly.

Sales

Jan

Feb

Mar

Topeka

79677

3856

87594

Atlanta

18104

63204

99574

Dallas

13265

97307

10919

 

Ranges (rectangular shapes) can be described by the cell references of their two corners. A1:D4 is the 4x4 group of sixteen cells you just entered.

  1. A keystroke combination can be used to quickly select an entire range. Click in any cell in the A1:D4 range Press CTRL + * (Note, don’t type the +. It is included in the instructions to show you that you need to press both keys at the same time.)  If you are using the asterisk above the 8 key, you would need to press Ctrl + Shift + 8 .  (Note: this keystroke combination may not work on a Mac or a laptop keyboard. If you have that problem, type A1:D4 in the Name Box and press Enter.)
  1. Hold down the Ctrl key and press the period. This moves the cursor into a corner of the range. Do this three more times to move the cursor to each of the corners.
  1. Click outside the range. Click on cell A1. Hold down the Shift key and click on cell D4. This method, clicking ion opposite corners, is another quick way to select a range.
  1. Press Shift and then press the arrow keys to expand or reduce the range of selected cells.

Moving the cursor to the edge of a range

  1. Click on cell A1. Move the cursor over the cell border line between Sales and Topeka, Your cursor will change into a 4-way arrow. For as Mac it changes into a hand. Double-click. The selected cell is now Cell A4.
  1. Click on cell A4. Move the cursor over the cell border line between Dallas and 13265. Your cursor will change into a 4-way arrow. For as Mac it changes into a hand. Double-click. The selected cell is now Cell D4.
  1. Click on cell D4. Press the END key and the UP arrow. The selected cell is now Cell D1.
  1. Click on cell D1. Press the END key and the LEFT arrow. The selected cell is now Cell A1.

Enter information into several cells in a range:

  1. Mouse over the range A6:C8.in the Ranges worksheet. Don’t press Enter yet! First type the number 1 and then press Enter.  The 1 should appear in cell A6 and the cursor should move into cell A7. Your range should still be selected. Type 2 and Enter, type 3 and Enter.
  1. The cursor jumps up into cell B6. When the data entry point reaches the last cell in the range, pressing the Enter key moves the cursor to the top cell in the next column in the range. Type 4 and Enter. The 4 should appear in cell B6. Continue entering the numbers 5 through 9 to fill up the range.  Now type 10 and Enter to see what happens.
  1. Mouse over the cells C6:D8. Press Ctrl+R. The R stands for right. The contents of cells C6:C8 will be copied into the range D6:D8.
  1. Mouse over the cells A8:C12. Press Ctrl+D. The D stands for Down. The contents of cells A8:C8 will be copied into the range A9:C12.
  1. Mouse over the cells A14:C16. Type your first name. Hold down the Ctrl key and press Enter. Your name should appear in all nine cells. The Ctrl + Enter keystroke combination is a fast way to enter text, dates, numbers and formulas into multiple cells.

Range Names

If your data is laid out in a table with descriptive labels for each column or for each row, you can convert those labels into range names. The range names can be used in formulas.

  1. In the Ranges worksheet select the range A1:D4. From the Formulas tab click on the “Create from selection” icon [ALT  M  C]. For a MAC you would click on Insert, (in the menu bar at the top of the window) then on Name, and then on Create. MacBook users should have the “Create from Selection” icon on the Formulas section of the ribbon.

We want to create row names and columns names so select Top Row and Left Column. Click OK.

  1. Click on the arrow in the name box to see a list of the range names you just created. The name box is the short box to above the A1 cell.

 

  1. To select one of the ranges click on its name. Click on Feb. The cells C2:C4 will be selected. 
  1. Mouse over the range A18:E21. In the name box type in the word Hello and press Enter. This is another way to create range names.
  1. You can see the range names on the worksheet if you shrink the display to under 40%. Try it.  You should see the word Hello. Increase the display size back to 100%.

Data Validation and Error Messages

You can format a cell to limit the kinds of data that can be entered into the cell.  Let’s do that for the cell with the date format. We will set it up so only dates in your lifetime are allowed.

  1. Select the Sheet1 worksheet.  Click on cell A10. On the Data section of the ribbon, click on the Data Validation icon [ALT  A  V]. In Excel 2013 use [ALT  A  V  V].

This dialog box opens.

Select the Settings tab and change the allow option to Date.

Three date option boxes appear.  Set the data option to between, the start date to your birthday. Type it with dashes 01-15-1994.

Use =TODAY() as the end date.  (Note: =TODAY() is a function that gives the current date.) Don’t change the dialog box.

Select the third tab in the dialog box, “Error Messages.” In Excel 2013 this tab is labeled Error Alert.  In the main message box type “This date was not in my lifetime.” and enter it by clicking Ok.  this will close the dialog box.

Now click on cell A10 and type in a date from next year.  Does the message appear?

Freezing Panes

Freeze Panes video: Freeze Panes

Freeze Panes    0:50

Split Screen and Freeze Cells    1:13

 

If you have a row of labels across the top of a worksheet, or a column of labels on the left side of a worksheet, the labels disappear if you move your cursor too far down or to the right. To keep the labels permanently displayed you can “freeze” one or more of the top rows or left-hand columns.

  1. In the Ranges worksheet, click in cell B2. This is below row 1 and to the right of column A, so row 1 and column A will stay visible when you freeze the labels.
  1. On the View tab of the ribbon click on the down arrow on the Freeze Panes icon [ALT  W  F].

For the MAC. This icon is found in the lower-right-hand corner of the Home section of the Layout ribbon. Click on Freeze Panes.

Use the arrow keys to move the cursor to the right.  Everything in column A will stay on the screen. Move the cursor back into cell B2.

Use the page down keys to move the cursor down the page.  Everything in row 1 will stay on the screen. Move the cursor back into cell B2.

Note, using the Ctrl + Home keyboard shortcut will not move the cursor into cell A1 when the Freeze Panes option is turned on. Instead it moves the cursor into the upper left corner of the unfrozen area.

  1. Now remove the feature. On the View tab of the ribbon click on the down arrow on the Freeze Panes icon [ALT  W  F]. Click on Unfreeze Panes.

Protection

Excel has a feature, called protection, to prevent someone accidentally removing or changing formulas and labels.  Protection can also be useful to help enter data into a form.

Video: Protection

Locking Cells and Worksheets    6:14

 

Before you protect a workbook or worksheet, start by selecting the cells in the worksheet where you want to allow users to make changes and unlock them.  After the worksheet is protected everything will be frozen except for the unlocked cells.

  1. Mouse over and select cells A1:B16 in Sheet1
  1. Open the Cell Formatting dialog box (Ctrl + 1) and click on the Protections tab.

Remove the checkmark next to the Locked option, and click OK.

  1. Find the Protect Sheet/Unprotect sheet icon [ALT  R  P  S] on the Review tab of the ribbon.  If it says Protect Sheet click on it. In the dialog box make sure that the first option “Select locked cells” is not checked. Click on OK to protect the worksheet.

Now the content in the range of cells you unlocked can be altered, but nothing else can be.

  1. Click on cell B2. Type the word Unlocked and press Enter.  You should be able to enter text and data in this cell.

 

  1. Click on cell C2. Type the word Locked and press Enter.  You should be blocked from entering text and data in this cell.
  1. Click on the Protect Sheet icon [ALT  R  P  S] on the Review tab of the ribbon to clear the protection. You can now change the contents of all of the cells in the worksheet.

 

 

Review – Fine Points

 

If you would like to learn more about Excel features, I have a follow up lesson here:

http://bus91l.altervista.org/Assignments/Toolkit2.htm

 

Instructions for the Check for Understanding (CFU) form.

A CFU form is turned in for every assignment.

Starting with the line for your name, copy and paste the CFU form into a Word document.

Fill in your name, date, section number

Circle (or indicate by highlighting) a Y or N response for each statement. I do not subtract points for No answers.

Below the list of Y/N statements, insert ONE screenshot of any part of your work. I just need to see something to prove that you did the assignment. I don’t need to see all of the assignment.  Resize the screenshot, if necessary, so both the form and graphic can fit on one page.

A copy of the CFU is due to be turned in during the next class session.  I do not accept CFU’s through email. 

 

Name _______________________________      Date___________       Section _______

Check for Understanding – Excel Toolkit

Y/N I can use the AutoFill Handle to quickly enter content into multiple cells.

Y/N I can apply special date and number formats to cells.

Y/N I can change column widths and wrap text.

Y/N I can add comments to cells.

Y/N I can work with multiple worksheets.

Y/N I can work with ranges and create range names.

Y/N I can use data validation to restrict the type of data that can be entered into a cell.

Y/N I can freeze columns and rows so they remain on screen at all times.

Y/N I can protect a worksheet.