Entering Data in Microsoft Excel Worksheets

In this lesson, you will learn about entering data.

Lesson Goals

• Enter text in Microsoft Excel worksheets.
• Add or delete cells in worksheets.
• Add an outline for your data.
• Enter a hyperlink in a worksheet.
• Use AutoComplete.
• Enter numbers and dates in Microsoft Excel worksheets.
• Use the Fill Handle to add data to cells.

Microsoft Excel worksheets are made up of rows and columns. Rows are defined by numbers and columns are defined by letters. When you open Excel, cell A1 is automatically highlighted. Anything you type will show up in this cell. To enter text into a different cell, simply select the cell by clicking on it and then begin typing.

Before entering text, it is helpful to be aware of the three shapes your cursor will take and what each one means:

1. The thick white cross. This is used for cell selection.
2. The thin black cross. This is used for autofilling data and for copying formulas, both of which will be covered later in this course.
3. The four-headed arrow. This is used for moving cells or other items.

Entering Text

To enter text in Microsoft Excel:

1. Select the cell into which you wish to enter text by clicking on it.
2. Begin typing.

Note that in addition to showing up in the cell, the text you are typing also shows up in the Formula Bar:

If you are entering a lot of text, it is sometimes easier to type directly into the formula bar. To do this, simply select the cell by clicking on it and then click in the Formula Bar and begin typing.

Expand Data across Columns

You can also easily expand data across columns by hovering the cursor over the lower-right corner of the cell and when it turns into a thin black cross, dragging. This will copy the data to multiple columns.

Adding and Deleting Cells

You can add and delete cells when working with a worksheet:

To add a cell to a worksheet:

1. Select the cell where you want to insert a new cell.
2. Right-click and select Insert. 
3. In the Insert dialog box, select an option and click OK.

To delete a cell in a worksheet:

1. Select the cell you want to delete.
2. Right-click and select Delete. 
3. In the Delete dialog box, select an option and click OK.

Adding an Outline

You can group your data in Excel by using outlines. An outline allows you to group and limit that data that you are viewing You have two types - Auto and Manual. Auto Outline works well if you have used Summaries (formulas to tally rows). Manual works well if you just have a list and you wish to choose the groups.

To add an outline to your data:

1. On the DATA tab, from the Outline group, select the Group drop-down arrow. 
2. Select Auto Outline. 
3. You can now expand or collapse sections using the + and - signs on the side of the worksheet. 

Using AutoComplete

When you are typing data into a list, Microsoft Excel will attempt to guess what you intend to type based on the data in the cells above the one in which you are typing. The example below illustrates this. Only the letter "B" has been typed into cell A4. Excel is guessing that the user intends to type "Ball":

If the user does intend to enter "Ball", he or she can press Enter as soon as Excel has correctly guessed.

Things to be aware or regarding the AutoComplete feature:

1. If there are multiple words in a list starting with the same letter, Excel won't guess until enough letters have been typed that only one match remains:
2. If there is an empty cell in the middle of a list, Excel will assume the data above and below the empty cell constitute different lists, and AutoComplete will not recognize words from the other list:

Entering Text and Using AutoComplete

Duration: 5 to 10 minutes.

Before the end of this course we will build a spreadsheet showing the quarterly profit & loss statement for a fictitious company called Dave's Lemonade Stand. This is the first of these exercises. The spreadsheet will ultimately look like the below:

In this exercise, you will enter income and expense categories in column A of a new worksheet.

1. Open a new workbook and enter text, using AutoComplete whenever possible, in column A so that your worksheet looks like the following:
2. Save the workbook as Dave's Lemonade Stand.xlsx in your Excel2013.1/Exercises folder.

Solution:

1. Open a new workbook.
2. Select cell A2, type "Income", and press Enter.
3. Enter the required text in cells A3:A7.
4. In cells A8 and A9, type "S" and press Enter.
5. Enter the required text in cells A10 and A11.
6. Save the workbook in your Excel2013.1/Exercises folder.

Adding a Hyperlink

To add a hyperlink to a cell in Microsoft Excel:

1. Select the cell to which you want to add the hyperlink.
2. From the INSERT tab, in the Links section, select Hyperlink. 
3. In the Insert Hyperlink dialog box, select the text to display as well as the link address, and then click OK. 
4. The link now appears in the sheet

Add WordArt to a Worksheet

You can insert WordArt in a worksheet in Excel 2013.

To add WordArt:

1. On the INSERT tab, in the Text section, select the WordArt arrow. 
2. Select a WordArt style from the list. 
3. A text box appears where you can enter your WordArt text. 

Entering Numbers and Dates

In the next lesson we will cover formatting numbers to include commas, decimals, currency symbols and more, and formatting dates in various ways. In this lesson, however, we will simply enter dates and numbers in the most basic format, and use autofill to quickly add numbers that follow a pattern.

To enter numbers in Microsoft Excel:

1. Select the cell into which you wish to enter a number by clicking on it.
2. Begin typing a number.

Things to be aware of when entering numbers:

1. There is no need to enter commas. If you wish to display commas, you can format your numbers to display them. This will be covered in the next lesson.
2. By default, trailing zeroes are not shown. For example, if you enter "5.00" into a cell and press Enter, the value shown will change to just "5". We will cover displaying decimals in the next lesson.

To enter dates in Microsoft Excel:

1. Select the cell into which you wish to enter a date by clicking on it.
2. Type the date in the following format: mm/dd/yy (e.g., 12/21/12) or m/d/yy (e.g., 1/1/00).

Using the Fill Handle

If your data follows a pattern, you can use the Fill Handle to quickly and easily add data to multiple rows and columns.

To use the Fill Handle:

1. Enter enough data to establish a pattern and then select the cells containing the pattern:
2. Click on the Fill Handle, which is located in the bottom right cell of the selected cells:
3. Drag the Fill Handle for as many rows or columns as desired:

Examples of scenarios in which it might be useful to use the Fill Handle include:

1. Entering a numbered list:
2. Entering days of the month:
3. Entering months of the year:
4. Entering yet to be named items which need to be differentiated:
5. Copying formulas throughout a row or column. This will be covered later in this course.

Entering Numbers and Dates

Duration: 5 to 10 minutes.

In this exercise, you will enter the four quarters of the year at the top of your worksheet and will enter projected quarterly numbers for the two income categories.

1. Open or go to Dave's Lemonade Stand.xlsx, which you created in the previous exercise.
2. Follow the instructions below to add data to rows 1, 3, and 4 such that your worksheet looks like the following:
   1. Enter dates and numbers in columns B and C
   2. Use Autofill to add dates and numbers to D and E
3. Edit cells A7:A9 by replacing "Some Expense" with "Payroll", "Marketing" and "Supplies", as shown in the above image.
4. Save the workbook.

Solution:

1. Open or go to Dave's Lemonade Stand.xlsx.
2. Enter "3/31/13" in cell B1.
3. Enter "6/30/13" in cell C1.
4. Enter "3000" in cell B3.
5. Enter "3100" in cell C3.
6. Enter "2000" in cell B4.
7. Enter "2200" in cell C4.
8. Select cells B1:C1, click Autofill and drag to column E.
9. Select cells B3:C3, click Autofill and drag to column E.
10. Select cells B4:C4, click Autofill and drag to column E.
11. Edit cells A7:A9 by replacing "Some Expense" with "Payroll", "Marketing" and "Supplies".
12. Save the workbook.

The Ribbon

The Ribbon is the display you see at the top of the Microsoft Excel window. It is your primary interface with Excel. It allows you to access most of the commands available to you in Excel. The Ribbon is composed of three parts: Tabs, Groups and Commands. In this lesson, you will learn about all three.

Lesson Goals

• Learn about tabs.
• Learn about groups.
• Learn about commands.
• Learn which groups and commands are found on which tabs.

Tabs

Microsoft Excel is a powerful program which is used to analyze and present data, perform calculations, and much more. Microsoft Excel has hundreds of commands for working with different scenarios. To make it easier for users to find the specific commands they are looking for, commands are organized onto eight main tabs:

1. HOME. The HOME tab includes commands for formatting worksheets, cells and data and commands for inserting and deleting columns and rows.
2. INSERT. Use the INSERT tab to insert tables, illustrations, charts, links, sparklines, headers & footers, custom text and symbols, and more.
3. PAGE LAYOUT. Use the PAGE LAYOUT tab to change your margins, change the page background, change the page orientation, and more.
4. Formulas. Use the FORMULAS tab to browse and select formulas and functions, to define names, to audit formulas, and more.
5. DATA. Use the DATA tab to access external data, to sort & filter, to access data tools, to group cells together, to add subtotals, and more.
6. REVIEW. Use the REVIEW tab to check spelling, add comments, protect your worksheet or workbook, and more.
7. VIEW. Use the VIEW tab to change your workbook view, show or hide gridlines, headings, the formula bar and the ruler, arrange windows, freeze panes, zoom in or out, and more.
8. ADD-INS. You may or may not see the ADD-INS tab. This tab shows up after you install and activate your first Add-In. Add-Ins are beyond the scope of this course.

Note that the FILE menu is not the same as a tab. The File menu takes you to the Backstage view, where you manage, rather than make changes to, your workbook. The Backstage view is covered in the next lesson.

Tool Tabs

In addition to the main tabs, there are numerous tool tabs which include less commonly used commands. Individual tool tabs are covered in detail in our intermediate and advanced Microsoft Excel classes. For now you should know:

• That they exist. Some of the most commonly used tool tabs are:
  • SMARTART
  • CHART
  • DRAWING
  • PICTURE
  • PIVOTTABLE
  • PIVOTCHART
  • HEADER & FOOTER
• That they will appear when you select commands which have related tool tabs. For example, when you insert a PivotTable, two PivotTable-specific tool tabs (ANALYZE (Options in Excel 2007/2010) and DESIGN) will appear:

Groups

To further organize the many commands available in Microsoft Excel, commands are organized in groups on each tab. Each group contains three or more related commands. The following table lists the groups found on each tab:

The group options may differ slightly if you are using Excel 2007 or 2010.

In some groups, you will see a button in the lower right corner, next to the group name. This is the Dialog Box Launcher. Opening the group's dialog box will give you access to additional commands associated with that group:

Commands

Commands are controls that enable you to accomplish specific tasks, such as bolding a word, wrapping text, changing the format of a number to percent, or adding a column.

Exploring the Ribbon

Duration: 10 to 20 minutes.

In this exercise, you will explore the Ribbon to answer the following questions:

1. How many tabs are there?
2. How many groups are there on the VIEW tab?
3. Which tab contains the most commonly used groups and commands?
4. What is another name for the FILE menu?
5. On what tab are the following groups found:
   1. Sort & Filter
   2. Zoom
   3. Charts
   4. Page Setup
   5. Function Library
   6. Number
   7. Formula Auditing
6. On what tabs and groups are the following commands found?
   1. Font Color
   2. New Window
   3. Sort
   4. Trace Precedents
   5. Wrap Text
   6. Group
   7. Print Area

Solution:

If your installation of Microsoft Excel has been customized, some of your answers may differ.

1. How many tabs are there? Answer: 7
2. How many groups are there on the VIEW tab? Answer: 5
3. Which tab contains the most commonly used groups and commands? Answer: Home
4. What is another name for the FILE menu? Answer: the Backstage view
5. On which tab are the following groups found:
   1. Sort & Filter - Answer: DATA
   2. Zoom - Answer: VIEW
   3. Charts - Answer: INSERT
   4. Page Setup - Answer: PAGE LAYOUT
   5. Function Library - Answer: FORMULAS
   6. Number - Answer: HOME
   7. Formula Auditing - Answer: FORMULAS
6. On what tabs and groups are the following commands found?
   1. Font Color - Answer: HOME > Font
   2. New Window - Answer: VIEW > Window
   3. Sort - Answer: DATA > Sort & Filter
   4. Trace Precendents - Answer: FORMULAS > Formula Auditing
   5. Wrap Text - Answer: HOME > Alignment
   6. Group - Answer: DATA > Outline
   7. Print Area - Answer: PAGE LAYOUT > Page Setup

The Quick Access Toolbar

In this lesson, you will learn how to work with the Quick Access Toolbar.

Lesson Goals

• Add common commands to the Quick Access Toolbar.
• Add additional commands to the Quick Access Toolbar.
• Move the Quick Access Toolbar.

Adding Common Commands

To add commonly used commands to the Quick Access Toolbar:

1. Click the dropdown arrow on the far right of the Quick Access Toolbar.
2. Select a command from the list that appears:

Adding Additional Commands with the Customize Dialog Box

To add additional commands to the Quick Access Toolbar:

1. Click the dropdown arrow on the far right of the Quick Access Toolbar.
2. Select More Commands... towards the bottom of the list that appears:
3. Select a command from the list circled below and click Add > >.
4. Click OK to make your changes.

Adding Ribbon Commands or Groups

If you find yourself using the same commands or group of commands frequently, you can quickly add them to theQuick Access Toolbar:

1. Right click on the command or on the group name.
2. Select Add to Quick Access Toolbar:

Placement

You can choose to keep the Quick Access Toolbar in the top left corner of Microsoft Excel or move it below theRibbon. To move the Quick Access Toolbar below the Ribbon:

1. Click the dropdown arrow on the far right of the Quick Access Toolbar.
2. Select Show Below the Ribbon.
3. To move it back, click the dropdown arrow again and select Show Above the Ribbon.

Customize the Quick Access Toolbar

Duration: 10 to 20 minutes.

In this exercise, you will customize the Quick Access Toolbar.

1. Add the following commands to the Quick Access Toolbar:
   1. New
   2. Open
   3. Quick Print
   4. Paste
   5. Find
2. Remove Find from the Quick Access Toolbar.
3. Move the Quick Access Toolbar below the Ribbon.

Solution:

1. To add commands to the Quick Access Toolbar:
   1. New: click the drop-down arrow to the right of the Quick Access Toolbar and check New.
   2. Open: click the drop-down arrow to the right of the Quick Access Toolbar and check Open.
   3. Quick Print: click the drop-down arrow to the right of the Quick Access Toolbar and check Quick Print.
   4. Paste: click the drop-down arrow to the right of the Quick Access Toolbar and select More Commands.... Highlight Paste in the left column and click Add > >.
   5. Find: click the drop-down arrow to the right of the Quick Access Toolbar and select More Commands.... Highlight Find in the left column and click Add > >.
2. To remove Find from the Quick Access Toolbar:
   1. Click the drop-down arrow to the right of the Quick Access Toolbar and select More Commands.... HighlightFind in the right column and click < < Remove.
3. To move the Quick Access Toolbar below the Ribbon:
   1. Click the drop-down arrow to the right of the Quick Access Toolbar and select Show Below the Ribbon.
   2. Optional: to move the Quick Access Toolbar back above the Ribbon, click the drop-down arrow to the right of the Quick Access Toolbar and select Show Above the Ribbon.