Chapter 1 Make a Start with Excel

A first look at Excel

In the first part of our Excel tutorials, we'll explain what you will see when the programme first starts. We're not going to explain what a spreadsheet is, and assume that you already know. If you don't, we have an introduction here:

To create a new spreadsheet in Excel 2013, click one of the templates. For this course, we'll be using the Blank Workbook temple. Click on that option from the list available. You'll be taken to the main spreadsheet area.

If you've ever used a previous version of Excel then you'll notice that the new version looks quite different! Even if you have never used Excel, the software looks quite intimidating. But we'll make a gentle start, so that you can get used to the way everything works.

The first thing to notice is the Ribbon running right across the top. The Ribbon is supposed to be more intuitive than drop-down menus. Emphasis is placed on the tools and options appropriate to what you're doing. Here's a closer look at the Ribbon when Excel first starts (it's split in half, here):

Left Side of the Ribbon in Excel 2007

Right Side of the Ribbon in Excel 2007

Left Side of the Ribbon in Excel 2010 and 2013

Right Side of the Ribbon in Excel 2010 and 2013

Notice that in both versions the Home tab is the one that is selected. Other tabs are: Insert, Page Layout, Formulas, Data, Review and View. (In the image above, there's also a Developer tab. If you don't do any programming then you don't have to worry about this tab.)

In the top left of the Ribbon in Excel 2007 there's a big circle. This one:

Click this, and you'll see all the file operations: New, Open, Save, Exit, etc.

To see the same menu in Excel 2010, you have to click on the File tab, which is on the far left. (It looks as though it's already selected, but it's not.):

Excel 2013 also has a File menu like the one above. When you click the File menu in Excel 2013, however, you'll see this instead:

To get back to your spreadsheet in Excel 2013, click the left-pointing arrow in the top left:

We'll go through all these menu options later, along with the various options on the Ribbon tabs. But the best place to start with Excel is getting to grips with all those numbers and letters. These are the Grid Coordinates.

Excel Rows and Columns

Spreadsheets are displayed in a grid layout. The letters across the top are Column headings. To highlight an entire Column, click on any of the letters. The image below shows the B Column highlighted:

If you look down the left side of the grid, you'll see numbers, which start at number 1 at the very top and go down to over a million. (The exact number of rows and columns are 1,048,576 rows and 16,384 columns. You've never going to need this many!) You can click a number to highlight an entire Row. If you look at the image below, you'll see that Row 5 has been highlighted (The image is from Excel 2007. Later are version are the same except less colourful!).

Spreadsheets are all about individual Cells. A Cell is a letter combined with a number. So if you combine the B column with Row 5, you get Cell B5. Combine Column D with Row 5 and you get Cell D5.

To see this for yourself, click inside any of the cells on your spreadsheet. In the Images below, we have clicked inside cell A1 and cell C3.

     

The first picture is Column A, Row 1 (A1), and the second picture is Column C Row 3 (C3). Notice that the cells we clicked on have a black border around them. This tells you the cell is active. The cell that is active will have its Column letter and Row number displayed in the top left, just above the letters A and B in the pictures. When you click into a cell, you can then type text and numbers.

To move around the spreadsheet, and make other cells active, you can either just click inside a Cell, or press the arrow keys on your keyboard. Try it now. Click inside a Cell and notice the Cell reference appear above the letters A and B. Press your arrow keys and notice how the active cells moves.

Before going any further, make sure you understand how the spreadsheet grid works. If you are asked to locate Cell H2, you should be able to do so.

How to Enter Text and Numbers in a Cell

To make a start, we'll create this really simple spreadsheet:

All we're going to be doing here is entering some text and some numbers. We're not adding anything up yet.

Before you tackle this first exercise, though, you may want to take note of the Undofeature, just in case you make a mistake. The Undo option is the left curved arrow, right at the top of your screen. This one for Excel 2007 users:

And this one for Excel 2010 and Excel 2013 users:

Click the left curved arrow to Undo something, and click the right curved arrow to redo it. The Undo arrow also has a dropdown box. Click the small arrow next to Undo to see the following:

This list is for multiple Undo's. Move your mouse down the list and click to undo several steps at once.

But back to the spreadsheet.

Click inside of cell A1 on your spreadsheet, and do the following:

• Type the word "Numbers" (with no quotation marks)
• Hit the Enter key on your keyboard
• The active cell will move down one, to cell A2
• Type the number 3, and again hit the Enter key on your keyboard
• The active cell will move down one, to cell A3
• Now put the number 6 into cell A3, and the number 9 in cell A4
• After you have typed the number 9, and hit the Enter key, you should see that cell A5 is now the active cell

You should now have a spreadsheet that looks like ours above.

The text we entered in cell A1 is known as a Heading. It's there just to tell you what the numbers mean. Except our heading doesn't tell us a great deal. Let's change it to something else. Click the link below to learn how to edit text in a cell.

How to Edit Text in a Cell

To change the text in cell A1, you can just click inside of the cell and start typing. Anything you had there previously would be erased. But if you just want to edit the text (if you've made a spelling mistake, for example), then this is no good. If you want to keep most of the text, and just make minor changes, then you need to do something else.

In the image below, you can see what's known as the Formula Bar. The Formula Bar is like a long textbox that you can click inside and start typing. Here's what it looks like in Excel 2007:

And here it is in Excel 2010 and 2013:

To edit a Cell in Excel, first click inside the cell you want to edit (A1 for us). Then click inside the formula bar. Notice where your cursor is now:

The image above shows that the cell A1 is active, but the cursor is inside of the formula bar.

With the cursor in the Formula Bar, try changing the text "Numbers" to "Add these Numbers". Press the Enter key when you've made the changes. Your spreadsheet should look like ours below:

Notice that the active cell is now A2, and that the Formula Bar has a 3 in it.

However, there's a problem. There's not enough room in cell A1 for our new text. Part of it seems to be in the B column.

The solution is to widen the whole of Column A. Try this:

• Move your mouse up to the start of the A Column
• The pointer will change shape and now be a black arrow

• Move your mouse over the line that separates Column A and Column B
• Your mouse pointer will change shape again, this time to a cross with arrows

• When you see the new shape, hold down your left mouse button
• Keep the left mouse button held down, and drag your cross to the right
• Once you have all the text in the A column, let go of the left mouse button. The images below show the process in action (the top two are from Excel 2007 and the bottom two are from Excel 2010/2013):

      

      

You can make the height of the Rows bigger or smaller by using exactly the same technique.

The numbers, however, don't look very tidy. We'll now see how to centre them, and the text as well.

How to Centre Text and Numbers

You saw that by clicking inside of a cell it makes it active, so that you can make changes. We want to centre all our numbers and the text. Here's the spreadsheet we have:

So we need cells A1, A2, A3 and A4 to be active. In Excel, you can do this by highlighting the cells.

• Place your mouse over cell A1
• Your pointer should now be in the shape of a white cross
• When your pointer changes to the white cross, hold your left mouse button down and drag to cell A4
• Let go of the left mouse button when cells A1, A2, A3 and A4 are highlighted
• The image below shows what you are aiming for

The cells highlighted in the image above have a different colour to the normal white colour of a cell. When you highlight cells, you can do things to all the cells as a group.

To centre the text and numbers in our highlighted cells, try this:

• From the Excel Ribbon at the top of the screen, locate the Alignment panel. Here's the Alignment panel in Excel 2007:

And here it is in Excel 2010 and 2013:

You can see the various alignment options laid out. These ones:

Hold your mouse over each alignment icon and you'll see an explanation of what they do. Click each icon and see what they do to your highlighted cells.

You can also click the arrow in the bottom right of the Alignment panel to bring up the Format Cells box (the one circled below).

When you click the arrow, you'll see this dialogue box:

Notice the Text Alignment section at the top of the Alignment tab. It has two drop down menus, one for Horizontal alignment and one for Vertical alignment.

• Click the arrow on the Horizontal drop down menu, the one with Left (Indent) on it
• You'll see the following:

As you can see, you have plenty of options to choose from in Excel. But click onCenter. Do the same for the Vertical drop down menu. Then click OK at the bottom of the Format Cells dialogue box.

The text and numbers in cells A1, A2, A3 and A4 should now be centred, and your spreadsheet will look like the one below:

Before moving on to other types of formatting you can do in Excel, have a try of this:

• Highlight the cells A5 and A6 on your spreadsheet
• Bring up the Format Cells dialogue box, just as you did above
• Make the alignment changes from the Horizontal and Vertical drop down menus
• Click OK to get rid of the dialogue box
• Now click inside of cell A5 on your spreadsheet and enter any number you like
• Hit the Enter key

The number you just entered should also be centred. So even if a cell is empty you can still apply formatting to it.

Font Formatting

If you've been following along with the previous tutorials, you should now have a spreadsheet that looks like this:

Excel 2007 to Excel 2013 have a much wider range of formatting options than previous versions, and it's relatively easy to turn a dull spreadsheet into something that really shines. We'll start with changing the font.

Choosing a Font in Excel 2007/2013

You can pick a different font for the data you enter into cells, as well as choosing the size you want. The colour of the font, and the cell background, can be changed, too. From Excel 2007 onwards, Themes have been introduced, so that you can format your spreadsheets more easily. You'll meet these later. First, we'll see how to change the font type.

• Highlight cell A1 on your spreadsheet by simply clicking into it
• Locate the Font panel on the Excel Ribbon at the top of the page:

The font in the panel above is set to Calibri. To see more fonts, click the black down arrow:

The good things about Excel 2007 and 2010/2013 is that when you move your mouse over one of the fonts on the list, the text in your selected cell (A1) will change automatically. This is just a preview, though. When you have decided on the font you want, click it with the left mouse button.

You can change the size of the font in the same way - just choose a new font size from the list of numbers in the drop down box.

If you want to change the font via the Format Cells dialogue box, as you did in previous versions of Excel, you can click the small arrow in the bottom right of the Font panel (the one circled below):

When you click the arrow, you'll see the Format Cells dialogue box. You can choose various options from this dialogue box: Font size, style, size, etc. The dialogue box looks like this:

You can also set the font colour from here, and add text effects. Click OK when you have made your choices.

When you have changed the font and font size, your A1 cell might look something like this:

 The background colour of your cells can be changed, too. We'll see how to do that in the next part of this tutorial.

 

How to Change the Colour of a Cell

In the previous lesson, you saw how to change the font and font size in Excel. In this lesson, we'll look at how to change the colour of a cell.

Change the Background Colour of a Cell

To change the background colour of cells, you first have to highlight the ones you want to alter. We'll start with the cells A2 to A5. So highlight these cells on your spreadsheet.

• With the cells A2 to A5 highlighted, locate the Font panel on the Ribbon at the top of the Excel: (The image below is from Excel 2007. Later versions are the same but less colourful!)

• Locate the Paint Bucket, and click the arrow just to the right of it. You'll see some colours appear:

Move your mouse over any of the colours and the cells will change automatically. You can then see what the new colour looks like. Click with the left mouse button to set the colour you want. If you don't like any of the colours displayed, click on "More Colors".

Once you have the number cells formatted in a different colour, click on the cell A1. Now do exactly the same thing, only this time choose a contrasting colour for the background of this cell. Your spreadsheet should then look something like the one below.

Change the Text colour

To change the colour of the text itself, click the down arrow just to the right of the letter A, which is just to the right of the Paint Bucket on the Font panel.

Select a colour just like you did for the background colour of the cell. Here's what your spreadsheet might look like with the background cell colour changes, and the text colour:

 

So with just a few clicks of your mouse, you can create a quite attractive spreadsheet.

How to save your work in Excel

Now that your spreadsheet is coming along nicely, you'll want to save your work. To save your spreadsheet, do the following.

• If you have Excel 2007, click the round Office button in the very top left of Excel 2007. This one:

When you click the Office button, you'll see the options list appear:

The Office button used to be a file menu in previous versions of Excel. In Excel 2007, you perform all the File operations by clicking the round Office button. Clicking Close, for example, will close the current Excel spreadsheet, but won't close down Excel itself. To close down Excel, click the "Exit Excel" button in the bottom right of this dialogue box. If you want to open a recent Excel document, click its name under the Recent Documents heading.

For Excel 2010 and 2013 users, you don't have a round Office button. Click the File tab instead to see the menu options as above:

In Excel 2013, your spreadsheet will disappear when you click File and you'll see this screen:

To save your work, click the Save option in all versions. Excel 2013 users will stay on the screen above.

Under Save As in Excel 2013, you'll see three options: SkyDrive, Computer, and Add a Place. The first option is SkyDrive. This saves it to servers operated and controlled by Microsoft. This is very useful if you want to work on your Excel document from other locations. For example, you may be working on a spreadsheet in your office. Saving it to SkyDrive means you'll also be able to open it when you get home from work. When you click the SkyDrive option you'll be able to Sign In, Sign Up, or simply Learn More. We'll be saving to the Computer, though, so click this option. Then click the Browse option:

You'll then see a dialogue box like this one: (Excel 2007 and 2010 users will see this straightaway.)

In the image above, we're saving our Excel spreadsheet to a New Folder we've created in theLibraries > Documents folder.

If you have Windows XP, you'll see this at the top of your dialogue box:

Save in means "Where would you like to save your spreadsheet?" In the image above, we're saving it to a folder called excel.

Notice the blue down-pointing arrow on the Save in drop down list. Click the arrow to reveal more locations:

Choose a new location from the list, if you prefer. The large white rectangle on the Save as dialogue box will then show you all the files already in the location.

When you're happy with your file location, type a name for your file in the area at the bottom of the dialogue box :

Notice the "Save as Type" box below the file name. The type is a XLSX file, and this is new from Excel 2007. The old ending was XLS. Excel 2007 and 2010 can open older XLS files, but previous versions of Excel can't open XLSX files.)

Remember to save you work on a regular basis, by clicking either the round Office button in Excel 2007 or the File menu in Excel 2010/2013. Then click the Save option. A quicker way is to just click the disk icon on the Quick Access Toolbar in the top left of Excel (all versions):

Coming up shortly is a Review, so that you can test your new knowledge of Excel. First though, you'll need to know about currency options.

Currency Symbols in Excel

Take a look at the following spreadsheet, which you'll shortly be creating:

The C column has a heading of "Price Each". The prices all have the currency symbol. To insert the currency symbol, do this:

• Enter some prices on a spreadsheet (any will do), and highlight the cells
• With the cells highlighted, locate the Number panel on the Excel 2007 to 2013 Ribbon bar (on the Home Tab):

Click the drop down list that says General. You'll then be presented with a list of options:

Click the Currency item to add a pound sign. But if you're not in the UK, you'll see the default currency for your country.

To see other currencies, click on More (or More Number Formats in Excel 2013).The Format Cells dialogue box appears. In the Category list, click on Currency. Select a Currency sign from the Symbol list. The dialogue box will then look like this:

Click OK to set the pound sign as the currency.

How to Merge Cells

Study the spreadsheet below:

If you look at Row 1, you'll see that the "Shopping Bill" heading stretches across three cells. This is not three separate cells, with a colour change for each individual cell. The A1, B1 and C1 cells were merged. To merge cells, do the following.

• Type the words Shopping Bill into cell A1 of a spreadsheet
• Highlight the cells A1, B1 and C1
• On the Alignment panel of the Excel Ribbon, locate the "Merge and Center" item:

• Click the down arrow to see the following options:

Click on "Merge and Center". Your three cells will then become one - A1, to be exact!

Review One

Reproduce the simple spreadsheet below, from a junk-food addict! You can pick your own colours for the cells and data, but try to include everything that's in the image.

As well as centred text and numbers, you need to widen the columns. To get the currency symbol, see a previous section. Also in a previous section, you can seehow to merge cells for the "Shopping Bill" heading. This should be one cell, and not three.

When you have produced the same spreadsheet as ours, you can move on to the next section, which is all about basic formulas in Excel.