Back to Basics: What Do You Know Already?

Before you really get going on any journey, it's a good idea to step back and check what you know already and what tools you already have. This chapter is essentially a refresher chapter. It reminds you about (or reacquaints you with) the basics — the stuff you really need — so you can get your bearings as you head out into the Excel wilderness.

The goal of this chapter is to remind you of what you know already and to fill in any gaps in your basic Excel knowledge. It begins with getting text and numbers into Excel and then moves on to basic functions and some other worksheet basics. If you think you have forgotten any of this material, now is the time to dust it down out of the Excel attic.

This is meant to be a hands-on book, so to play along, open up a blank Excel workbook and get ready to walk through all the step-by-step procedures presented in this chapter.

Note This book shows Excel 2010 in use, but what this chapter covers is true for all versions of Excel (even if you are rocking an Excel 2003 version).

Data Entry Tips and Tricks

Before you read any further, here are some tips that will make your Excel life a lot easier:

• If you plan to do any calculations with a number, do not put any text in the cell with it. When you mix text with numbers and then try to do calculations, Excel gives you a #VALUE! message.

• Try to keep your entries separate. For example, if you are entering names and addresses, and you think that in the future you may want to sort the names by surname, put first name in one cell and surname in the cell beside it.

• If you need to enter phone numbers or any other numbers that have leading zeros (e.g., 00353111155555), type in an apostrophe (') before entering the number. This is how you tell Excel to treat it as text so that it keeps the first two zeros. If you don't do this, Excel keeps removing the first two zeros so you get 353111155555. And like a dog playing fetch, you will spend the rest of the day trying to add those two zeros at the beginning.

Note Have I mentioned that I'm Irish? My spelling and some of my examples may be a bit foreign to those of you from the United States and other places. I'm told that U.S. phone numbers never start with two zeros but that a zip code could, in fact, start this way. You may also find other situations where you need to preserve leading zeros.

• Place entries side by side and underneath each other. If you don't, you create a lot of extra work for yourself. Why? Later on, if all your entries are together and you need to sort or filter your list, Excel will naturally sort and filter all items in the list.

• Before you can format numbers or text, you need to highlight them. The quickest way to do that is to click on the first cell. Then, keeping your left mouse button pressed down, drag the selection down until you have highlighted the cells you want to apply your formatting to.

Creating a Text Series

1. Click in cell B2 and type January. Note that the cursor is flashing at the end of the word.

2. Rest your mouse pointer in the bottom-right corner of the cell, on the fill handle (see Figure 1-1). (I think of it as a soap opera character named Phil Handle.) It should change to a black cross, but if it doesn't do so, hover the mouse pointer over it until it does.

3. Holding down the left mouse button, drag the cell down to the cells underneath. You can stop at December, but if you continue, January will reappear.

4. Release the mouse. Holy mackerel! It's a miracle! Excel has filled in the other months. Yep, that's how we do it in the Excel world.

Note You can do the same thing with the days of the week: Type Monday in another cell, grab the fill handle, and drag down. Excel fills in the days of the week for you! Sigh, I love Excel.

You can also create your own lists other than the months of the year and days of the week by using the Custom Lists option: Just select File | Options | Advanced | Edit Custom Lists and specify the type of list you want to create.

Creating a Numeric Series

1. Click in cell D2 and type 1.

2. Click in cell D3 and type 2.

3. Highlight both cell D2 and cell D3 by clicking in the centre of D2. Your mouse pointer changes to a white cross.

4. Drag down the mouse until you see a black line around D2 and D3.

5. Rest your mouse pointer on the bottom-right corner of the cell. (Remember Phil Handle?) It should change to a black cross, but if it doesn't do so, hover the mouse pointer over it until it does.

6. Holding down the left mouse button, drag down to cell D11. Holy-moly! Excel does the same neat trick with numbers that it does with words. This time, it fills in the numbers 1–10 for you.

Note Practise with other number sequences, such as 10, 20, etc. and 2, 4, etc. Remember that you have to highlight both numbers, or Excel doesn't know you want a sequence of numbers and just copies the first number, such as 10 or 2, all the way down.

Highlighting More Than One Group of Cells at a Time

1. Highlight the first group of cells you want highlighted (e.g., A2:A4).

2. Press the Ctrl key and keep it pressed down while highlighting the second block of cells (e.g., C2:C4).

3. Release the mouse and the Ctrl key. You now have two ranges highlighted, and when you apply formatting, it applies to both ranges.

Note I refer to tabs frequently in this book. By this I mean the set of icons at the top of the screen. For example, File, Home, and Insert are all tabs on the Excel ribbon.

Formatting Text Entries

1. Highlight the text to which you want to apply formatting.

2. On the Home tab of the ribbon, click on the font formatting you want to apply: B (Bold), I (Italic), U (Underline), etc. (see Figure 1-2). Excel immediately applies the selected formatting to the highlighted text.

Note It is worthwhile at this point to have a look at some of the formatting options on the Home tab. Note that when you rest your mouse on each icon, Excel tells you what that icon does. See, Excel is on your side.

Formatting Number Entries

1. Enter some numbers into some cells.

2. Highlight those cells.

3. On the Home tab of the ribbon, click on the numeric formatting you want to apply: Currency, Percent Style, Comma Style, etc. (see Figure 1-3).

Copying and Pasting

Note There are multiple ways to copy and paste: You can use keyboard shortcuts, right-click with the mouse and use the menu that pops up, or use the Home tab of the ribbon. I...