Working with Data - Excel Bootcamp: Learn the Basics of Microsoft Excel (2016)

Excel Bootcamp: Learn the Basics of Microsoft Excel (2016)

Chapter 2. Working with Data

Moving

Since Excel offers a wide working area, you have to know how to place your data in different locations. You can move around Excel in two ways:

· Using the mouse– This option is ideal if you are working with small distances. Just click on the cell you want to work on and start typing.

· Using the keyboard– Excel allows you to use the arrow keys of your keyboard. Press the arrow keys until you reach your preferred cell. In this option, you may enter data into any cell without using the mouse.

Scrolling

Just like any program, MS Excel has scrollbars. You may use these scrollbars to view different parts of your workbooks. Here’s a screenshot:

Scrollbars require you to use your mouse. You may click on the left/right or up/down arrows to view certain cells. Alternatively, you may drag the bars to reach certain parts of your workbook.

Excel Worksheets

How to Move Between Worksheets

The system creates three sheets each time you generate a new workbook. These sheets are named“Sheet1,” “Sheet2,” and“Sheet3.” If you want to add a new sheet into your workbook, just look at the bottom of your screen and click on the“Insert Worksheet” option. Here’s a screenshot:

As you can see, sheets are displayed as tabs at the bottom of the program. In order to access a specific sheet, just click on its tab.

How to Move Inside a Worksheet

In Excel, you can hit F5 on your keyboard to reach a particular cell. Pressing the F5 key opens a dialog box where you can specify your desired cell.

How to Enter Data

To enter your data into a cell, just position the cursor on the targeted cell and type the information. Excel allows you to enter up to 32,000 characters into a single cell.

Excel programs accept letters and numbers. Additionally, these programs show data in different alignments– left for letters; right for numerals. However, you may use other alignments if you need to.

How to Cancel Data Entries

In some cases, people mistype entries into cells. Excel allows you to cancel mistyped entries, provided you haven’t pressed the Enter key on your keyboard.

To cancel a mistyped entry, just click on the“X” near the Formula Bar. Here’s a screenshot:

Alternatively, you may cancel an entry by hitting the Esc key of your keyboard.

How to Modify an Unconfirmed Entry

Excel allows you to edit unconfirmed entries. If you want to make a simple adjustment on an entry (e.g. delete/replace a letter), just double-click on the cell that contains it. Then, hit the backspace key or enter the right characters.

The AutoComplete Feature

Excel offers“AutoComplete” – a feature that allows you to save time and effort in entering data. Basically, Excel remembers the data you have entered in your workbook. Then, it will offer those entries as suggestions whenever you type on a new cell. This process is similar to the suggestions shown by Google when you run an online search. Here’s a screenshot:

How to Use this Feature

1. Highlight the next blank cell of a column.

2. Start entering the data. MS Excel will check if the new entry is similar with any item in the same column. If it is, the program will complete the entry automatically.

3. If you want to accept the suggestion, hit Enter to complete the process.

4. If you need to enter new data, just ignore Excel’s suggestion and continue typing. Hit Enter once you’re done.

How to Choose from a List

In some cases, Excel displays multiple suggestions. This situation happens whenever the user works with similar entries. Choosing from a list of suggestions is simple and easy. You can do it in two ways:

· Using a Keyboard:

1. Highlight the correct cell using the arrow keys

2. Press ALT and the“Down” arrow key simultaneously. This action will display a list of the possible entries.

· Using a Mouse :

1. Right-click on the cell

2. Select“PICK FROM LIST”

3. Select the item that you need to enter

Important Note: You can only use AutoComplete if your entries are continuous (i.e. no gaps in between). If a gap exists between two entries, Excel will disable the AutoComplete feature for those items.

How to Disable the AutoComplete Feature

In some situations, automatic suggestions can confuse (or even frustrate) you. If you don’t like to see these suggestions, you have to do the following:

· Access the“FILE” ribbon and hit“Options”

· Go to“ADVANCED” and uncheck the box that says“ENABLE AUTOCOMPLETE FOR CELL VALUES”

How to Edit a Cell

In Excel, you have different options in changing or removing data. These options are:

Typing Overwrites Highlighted Entry

This feature is available in all Microsoft Office programs. It makes sure that the characters you type will replace a highlighted entry. For instance, if you highlighted“Apple” and typed“Banana,” the first entry will be replaced by the second one.

This feature can be extremely useful, particularly when you have to make quick/simple changes on stored data.

Here are the things you need to do to use this feature:

1. Use the mouse or arrow keys to highlight the correct cell

2. Enter the new data (the previous entry will disappear once you begin typing)

3. Hit Enter to complete the process

The Edit Mode

Sometimes, overwriting an entire word isn’t needed. For example, you just have to add or delete a character from your entry. Excel solves this problem through“Edit Mode” – a writing mode that allows you to edit data quickly and easily. You can activate this mode using these approaches:

· Editing inside the cell:

1. Double-click on the cell that you need to edit.

2. Place the cursor on the right position using your arrow keys.

3. Make the necessary changes and hit“Enter.”

· Editing through the Formula Bar:

1. Highlight the cell that you want modify.

2. Click on the formula bar once the content of the targeted cell appears there.

3. Edit the entry as necessary and press the“Enter” key.

How to Select Cells

If you need to edit something that affects multiple cells, you have to choose the cells first.

When choosing a group of cells, MS Excel shows the active cell by giving it a white color– the other cells are in black. You may choose specific cells on your worksheets through the following approaches:

· Using the mouse– When choosing cells using a mouse, you have to ensure that Excel shows the“selection pointer” (i.e. the white“+” symbol).

The following table shows the selection techniques that you can use with a mouse:

To choose

You must

A specific cell

Click on the cell or use your arrow keys.

A group of cells

Click on the initial cell and drag your mouse to the final cell in the group.

All of the cells in the worksheet

Hit the button that says“Select All”

Disconnected Cells

Choose the first cell, press down the CTRL key and choose the remaining cells.

A long line of cells

Highlight the first cell of the group, press down the Shift key, and click on the last cell of the group.

A whole row

Click on the row’s number (i.e. the numbers at the leftmost side of the screen).

A whole column

Click on the column’s letter (i.e. the letter at the top of the worksheet).

Adjacent columns/rows

Choose the first row/column, press down the Shift key, and click on the last row/column.

Nonadjacent row/columns

Highlight the first row/column, press down the CTRL key and click on the remaining rows/columns.

· Using the keyboard– The keyboard offers excellent control when it comes to cell selection. Here are the keys that you can use in Excel:

To choose

You must

Two adjacent cells

Highlight the first cell, press down the Shift key, and press the appropriate arrow key.

The current cell to the edge of the worksheet

Press down the CTRL and Shift keys. Then, press the correct arrow key.

The entire column

Highlight a cell that belongs to column you want to select, press down the CTRL key, and hit the spacebar.

The entire worksheet

Press down the CTRL and A keys.

The current area

Press down the CTRL key and hit the asterisk (from your keyboard’s number pad).

How to Clear Data, Comments and Formats

You have to clear a cell in order to remove its contents. Excel offers various methods in clearing a cell– the method that you should use depends on what you need to remove. You may remove data within cells using your keyboard or the Excel ribbons. This approach is easy and simple, although it can only remove text, dates, numbers, and formulas.

Important Note: Deleting the content of a formatted cell leaves the format in place. That means you won’t have to reapply the format on the same cell. You can simply enter the new data, if any.

Clearing the Content of a Cell

· Highlight the cell/cells you need to work on.

· Right-click on the cell or group of cells.

· Left-click on the option that says“Clear Contents.”

Here’s a screenshot:

Clearing the Format

1. Select the correct cell/s.

2. Access“Home” and click on the“eraser.”

3. Choose“Clear Formats” from the dropdown menu.

Here’s a screenshot:

Clearing a Comment

1. Highlight the cells that you want to work on.

2. Go to“HOME” and click on the“Clear” option (i.e. the white eraser at the right-hand section of the ribbon).

3. Click on the option that says“Clear Comments.”

The image below will serve as a useful guide: