EXCEL FUNDAMENTALS

Spreadsheets have been in use on personal computers now for some years. They are used in nearly every office environment in the world for any number of reasons. In the past they were used only to perform basic math, such as adding, multiplying, subtracting and dividing. Today's spreadsheets can be significantly different and far more advanced. The leader in the spreadsheet field today is without doubt Microsoft Excel. The reasons for this will become apparent the more you learn about this very powerful program.

You will find that by following a few basic fundamental rules when starting a spreadsheet that you will be able to extract, change, modify, report, add and manipulate data in a way you never thought possible. Sadly most users of Excel never realise the full potential of what they are using. In fact all too many users simply use Excel as no more than a personal whiteboard, with the occasional calculation (formula) thrown in. This is a great shame as it is designed to do so much more than this.

We will show you how you can use Excel to achieve all your wants and needs.  As we progress you will also find yourself wanting to know more and it can become an unquenchable thirst if you allow it to be (believe me we know!).  By the completion of these lessons you will be able achieve a lot more than you can now, but even more importantly you will have the knowledge to be able help yourself.

You will notice throughout the course that we do NOT use screen shots to aid you. This is done intentionally, as we believe that by doing so forces the student to think a lot more about what they are doing.

Excel is spreadsheet program that hinges entirely on three very important aspects.

Remove any one of these aspects and Excel can no longer function.

Starting Excel

Before we go into any detail at all we need to know how to start Excel. Starting Excel is no different to starting any other Microsoft applications (Word, PowerPoint, Publisher, Access etc.) and can be done in any number of ways.

The three most common ways to start Excel are:

The first two may not be relevant to you if your computer is on a network, so if you don’t have these options available to you, select the third option. (If you wish to set up a shortcut to your desktop, or install the Microsoft Office Toolbar, please let us know and we will send you instructions on how to do so).

Lets now open Excel by selecting the Start button located at the bottom left hand side of your keyboard, then selecting Programs then Microsoft Excel and have a look at the screen.

The Title Bar

When you start Excel you should first see a light blue bar at the very top of your screen. This is called the Title Bar. The title bar is where you will see the name of your Workbook once you have saved it. (Note that if you have not yet saved a Workbook, the default will probably be Book1 - Microsoft Excel). As you are familiar with Windows, you will notice at the right of the Title Bar the Minimise (hyphen), Restore (two squares) and Close (X) buttons.  There are actually two sets of sizing buttons, the top set, to the right of the Title Bar apply to the program as a whole, whilst the lower set apply to the workbook only.

The Ribbon

The Ribbon in Excel is located directly underneath the title bar and is where all of Excel’s features can be accessed. It contains both text and icons (pictures). The Ribbon is designed to quickly help you find the commands you need to complete a task.  Command buttons are organised into logical groups and collected under tabs.  Each tab is specific to a type of activity for ease of use.  You can hover your mouse over any of the Commands to see what they do.  Some of the groups have a small arrow icon located at the bottom right hand side (known as a dialog box launcher) if this is clicked, even more options related to the group appear in what is known as a dialog box.  The ribbon can be minimised so that only the tabs are visible.  To do this, select the downward pointing arrow to the right of the Quick Access toolbar and select Minimise the Ribbon.

There are actually two other kinds of tabs, which appear only when they could be useful for the type of task you are currently performing.  These are:

Contextual tools

Contextual tools enable you to work with an object that you select on the page, such as a table, picture, or drawing. When you click the object, the relevant set of tabs appear in another color next to the standard tabs.

Program tabs  

Program tabs replace the standard set of tabs when you switch to certain authoring modes or views, such as Print Preview.  Try it, so you can see what we mean.  Go to the Office button>Print>Print Preview.

Dialog Boxes

Excel uses Dialog boxes for a lot of options, therefore there are loads of dialog boxes contained inside it.  Dialog boxes contain a range of options, located under relevant tabs.

The Quick Access Toolbar

The Quick Access toolbar is the only toolbar in Excel 2007 that you can customise.  It contains a set of commands that are independent of the tab that is currently displayed and it remains visible at all times.  You can add or remove commands from the ribbon by selecting the downward pointing arrow to the right of the toolbar and selecting from the menu accordingly.  If you can't see the option you are looking for, select More Commands.

Right Click Menu

We mentioned above while looking at Toolbars that there is another shortcut menu in Excel, the right click menu. The right click menu is also known as the context-sensitive menu.  This means that it will display options that are sensitive to the area in which you are clicked.  To see this simply select any cell and (you guessed it!) right click. You will see a list of common options available in the area you are clicked in. We won't go into any detail on these at this stage but we most certainly will in later lessons.   You will notice as we go into Excel deeper that it has many of these right click menus.

The Formula Bar

The Formula bar in Excel is where most users would do any editing of formulas or any entries that are within a cell. The Formula bar is the long white bar located above columns B and C with an fx (insert function) icon to the left. 

Smart Tags

Smart tags are a feature available in Excel from 2002 (XP) onwards. Smart Tag indicators pop up when Excel recognises a particular data type You won't see any at the moment as we have no data on our screen.  A Smart Tag indicator is a coloured triangle (usually purple) that is located in the corner of a cell.   Hover your mouse over the purple triangle and you will see the Smart Tag Action button, making it easy for your to complete some of the most common actions for that data type.  To see the actions you hover your mouse over the Smart Tag and you will see a downward pointing arrow. Click the down arrow and you will see more options in a pop-up list. 

Workbooks

Every single time you start Excel you are, by default, creating a New Blank Workbook. There is one on your screen now. The name of your Workbook has the default name of Book1, until you save the Workbook as a different name.  A Workbook is like the outer shell of the spreadsheet and contained within it are the Worksheet(s) and cells.

Worksheets

All Workbooks must contain at least one Worksheet. The maximum number of Worksheets a Workbook can contain is limited only by the available memory of the PC. As a default, Excel places three blank Worksheets in front of you. Each one of these has the default name Sheet1, Sheet2, Sheet3 at the bottom.  By default all Worksheets are identical in that they contain Rows, Columns and Cells (all discussed below).

NOTE: You can change the default of three blank Worksheets by going to the Office Button>Excel Options/Popular and under Creating New Workbooks changing Include this many sheets: by typing in the number you require.

All Worksheets contains 16,000 columns and 1,048,576 rows.  Each separate rectangle within the Worksheet is called a Cell.  The definition of a cell is the rectangle where a column and a row intersect.  You will notice letters across the top of your Worksheet. These are the names of the columns and go from A through to XFD. The numbers down the left hand side of your sheet of graph paper symbolise row headings and go from 1 through to 1,048,576. 

Inserting Worksheets

Adding new Worksheets to a Workbook can be done in a number of ways.  The most common are:

There is another way that is not exactly inserting a new Worksheet, but rather making a copy of an existing Worksheet.

To do this, select the Sheet you want to copy then left click on the name tab and holding down the Ctrl key (bottom left hand side of your keyboard), then drag it to one side, then release. This is a great time saver when you have spent hours setting up a Worksheet with all the formatting you want. You will notice that Excel will place a number after the copied sheets name in brackets. This is because we cannot have two Worksheets of the same name in the same Workbook. Name and Renaming Worksheets is covered later.

Deleting Worksheets

The logical opposite to inserting Worksheets is Deleting them. This can be done in any of the following ways.

Whichever method we use, Excel will display a warning message letting us know that the deletion cannot be undone. This is not technically true as we could always close our Workbook WITHOUT saving and then re-open the Workbook, but of course this means we would loose any other changes we had made.

Naming Worksheets

We can also name our Worksheets with meaningful names (which is a good idea!). The only restrictions we have with Worksheet names is:

While it is not totally necessary, try to AVOID using spaces between words. For example, if you wanted to name a Worksheet Bills Hours use BillsHours instead! The reason for this is that some of Excels formulas have slight problems when they reference Worksheets that contain spaces.

To name a Worksheet we can do so with any of the most methods below:

Moving Worksheets

At times we may need or wish to move a Worksheet to another position in the Workbook or even to another Workbook.  We have already looked at how we can make a copy of a Worksheet, select the Sheet you want to copy then left click on the name tab and holding down the Ctrl key drag it to one side, then release.  If we do not want to copy but rather move the entire Worksheet to another position in the Workbook we can simply left click the Worksheet Name tab, then holding down the mouse button drag it to it's new position and release.  The other method is to use the Move or the Copy dialog box. This can be accessed in two ways.

It makes no difference which method you use!  You will see on this dialog box a To book: box with a small drop arrow to the right. The selection in this box will determine where our sheet will be moved or copied to.  The default name in this box will be the active Workbook name (the current Workbook we are in).  There will also always be another choice, if we click the drop arrow. This is (new book), if we choose this option Excel will automatically create a new Workbook for the sheet to be moved or copied to. Within this box will also be the names of any other open Workbooks, should there be any.

Directly below the To book: box is the Before sheet: box. Our selection in this box will decide which position our sheet will be moved or copied to. Last of all is the Create a copy checkbox.  If we check this box Excel will create a copy of our moved Worksheet. In other words our original sheet will remain where it is and a copy of it will be moved to our chosen location.  If we leave it unchecked (which is the default) the Worksheet will be moved and not copied. The best way (as with most of excel) is to jump straight in and try out different options - you cannot do any harm!

Let’s now have a look at the cells on our Worksheet in more detail.

Cells

These are without doubt the very backbone of Excel. On each Worksheet there are over a billion cells. These are divided into 1,048,576 Rows and 16,000 Columns, as mentioned earlier.  The number of Rows and Columns in a Worksheet are fixed, which means we cannot add more and we cannot take any away, so as a consequence the number of cells are also fixed. This should never become a problem, as we can add a lot of Worksheets if we need to, each with over a billion cells, so this should give us plenty!  A cell is defined as the intersection of where a column and row meet, just like the coordinates on a road map.

The method that Excel uses to reference these cells (as a default) is called the A1 style reference. When we refer to a particular cell we use it's cell Address (or location) to nominate the cell. The method Excel uses to reference cells is a very simple, but highly effective method, often referred to as the grid pattern method, exactly the same as a road map.

Click in cell C6.  It should be defined as your active cell, or the cell in which you are clicked, by the border of the cell or rectangle being different in some way, either bolded, or maybe a different colour.  Now have a look at the column heading letter.  It should be defined in some way, either bolded, or highlighted in a colour, it may even look pushed in like a button.  Now look at the row number on the left of your screen which should also be defined in some way.  This is telling you your cell reference.  Now look above the column heading A and you will see the reference C6 in what is known as the Name box (discussed later).

In the situation above we have selected one cell only. We can select many more cells at one time if we wish. When we do so, we have selected what is known as Range of cells. A range can be one cell, or many cells.

To select a range of cells we simply select any one cell (position the cursor so a white cross appears), then holding down our left mouse button, drag in the direction we wish to select. It is important to note here that although we have more than one cell selected (a range) there is only one Active cell and this will always be the first cell we select.  The Active cell is the cell that data entry would appear in if we started typing.  There is no limit to the number of cells we can select on a Worksheet. 

To select all cells on your worksheet either push Ctrl + A or the Select all button, (the empty blue rectangle with a white triangle in the lower right corner) in the upper-left corner of a Worksheet where the row and column headings meet. We can also select entire columns or rows by simply left clicking on the column letter or row number and dragging in the desired direction.

Entering/Editing Data

To enter data into a cell, simply select the cell you want to enter data into, enter your data and select Enter on your keyboard.  You can also click your left mouse button to enter data into a cell, or use the Tab key on your keyboard.  Enter is the usual method as usually we are entering data down a column.  click in cell A1, type in your name and select Enter on your keyboard.

If you make an error, or you wish to edit a cell, there are a few different ways you can do it:

Moving About the Worksheet

There are many ways that can be used to move around a Worksheet and the method used is purely user preference.

At the far right of your Worksheet window is what is known as a Vertical scroll bar.  This bar has an upward pointing arrow at the top of it and a downward pointing arrow at the bottom of it.  You can vertically scroll up and down through your document in any one of the common following ways (there are more!):

To get quickly back to the top of your Worksheet, push Ctrl + Home. This will always take you to cell A1.

At the bottom of your Worksheet window to the right is your Horizontal scroll Bar. The use of this is the same as with the Vertical scroll bar, except of course you move horizontally and you cannot use Page Up and Page Down.

Go To Dialog

If you happen to know the address of the cell you wish to go to we can use the Go To dialog box.

What has popped up in front of your now is the Go To dialog box.  Note that your cursor is flashing in the Reference section of the box. This is where you type the cell reference that you wish to Go to.  The big box (Go To) is where Excel stores the last references used.  If the reference is in here you would simply select it and click OK

Type in HS3156 in the Reference box and select OK (or hit Enter on your keyboard if you prefer). Excel will take us straight to the cell reference we typed.

To get back to the top left of the Worksheet, push Ctrl + Home. The Ctrl + Home method is simply a short cut key for typing A1 in the Reference box of the Go To dialog box.

While the Go To dialog box can be handy for moving straight to any cell, there is an easier way!  To see what I mean left click in the Name box (left of the Formula bar, above column A), and type any cell address (such as G960) and push Enter.

The Name box has other uses as well, we will cover this in detail later.

Moving Through the Worksheets

The only area of navigation we need to look at now is the Worksheets themselves. This can be done in one of two ways. The most popular and probably the simplest method is to simply left click on the sheet name tab of the sheet you want. This will automatically activate the appropriate sheet.

The second method is probably best suited to when you have a lot of worksheets in the same Workbook.

You will notice to the far left of the sheet name tabs, you have four arrows. Place your mouse pointer over any one of these and then right click. You will see a pop up menu containing the names of all the Worksheets within the Workbook. You simply select the one you want with your left mouse button. The four arrows to the left of the sheet name tab are called Tab Scrolling Buttons. These four arrow buttons are to allow you to scroll through the Worksheet name tabs should your workbook contain more Worksheets than are currently visible. If you click the arrow with the vertical line after it, you will scroll so that your far right hand Worksheet name tab is visible. If you click the arrow without the vertical line, you will basically scroll one Worksheet tab at a time.

Saving Workbooks

There are many formats that we can save our data in Excel as, but by far the most common is the default *.xlsx. (The asterisk represents your chosen file name). Some of the other popular formats are the *xls (if you wish a file to be used with prior versions of Excel) *.xltx (Template) *.txt (Tab delimited), *.xml (Extensible Markup Language - available in newer versions only) and *.htm (a web page). 

To see what we mean go to the Office Button>Save As... and Excel will display the Save As dialog box (dialog boxes will be discussed later). This is where you initially give your Workbook a name (File name:), nominate a file type (Save as type:) and a location in which to save your workbook (Save in:).  We say "initially" as once you have saved a Workbook for the first time it will, by default, always save as that File name, type and location you have nominated each time you save.  In other words, each time you save you are just updating the copy of your workbook that is saved in your computer.

If you select the drop arrow to the right of the Save as type: box you will see a list of all different types of formats an Excel Workbook can be saved as. You may notice that there are many different choices of the .xlsx types. These different types are for backward compatibility with previous versions of Excel and other various formats you can choose to save your workbook as.

When you are saving a Workbook for the first time Excel will display the Save As dialog box no matter which method we use to save. There are a number of ways to save a workbook, the most common ways that can be used are:

If we close a Workbook that we have made changes to, Excel will ask us (via a message box) if we wish to save the changes we have made. If we close a Workbook without making any changes, no message will appear and Excel will close without saving.  Sometimes, even if you have done nothing in a workbook, but you have moved around in it, Excel will see this as you making a change and you will be prompted to save.  We will look at closing a Workbook below.

Creating More New Workbooks

Just because we have one Workbook open does not mean we cannot create more Workbooks, in fact the only limit to the amount of Workbooks we can have open is restricted by the available memory of the PC.

 There are also a number of methods that can be used to create more Workbooks.

Whichever method we use Excel will create a new Workbook and the name (by default) will be Book2 or the next number in the sequence. As soon as we have created our new Workbook it will automatically become what is known as the Active Workbook (or the workbook you are going to work in).  This means the workbook in which you are currently clicked.  The other Workbook will still be open, but NOT Active! This is because there can only ever be one Active Workbook at any one time and you can only work in one workbook at a time.

There are two ways to switch between open Workbooks.  The first is simply to click on the workbook name you require on your Task bar (this is right at the bottom of your screen where the Start button is located), or you can go to View on the ribbon and then click Switch Windows under the Window group and you will see the names of all open Workbooks. To activate one of the other open Workbooks, simply select its name and it will become the active workbook. Close Book2 now so that we only have one Workbook open. As mentioned above you will not be asked if you wish to save, unless changes have been made.

Exiting

There are two ways to exit Microsoft Excel and close the program down. These are:

Select either of these options to close down Excel. If we wished to Exit Excel and we were working in a Workbook that we have made changes to, Excel will ask us (via a message box) if we wish to save the changes we have made. If we wished to Exit Excel and we had a Workbook that we had not made any changes to, no message will appear and Excel will Exit without saving.

Summary

So in summary, we have discussed the three most important aspects of Excel, these are:

We have looked at the definition of each of these aspects in detail. We have also covered:

We have looked at the many ways of moving through the cells within a Worksheet, using a multitude of options, including the Go To option located in the Editing group under Find & Select on the Home Tab.

We have looked at our Excel screen in detail, the ribbon, options, tabs, dialog boxes the Quick Access toolbar and the Formula bar And finally, we learnt how to start and Exit Excel in the correct way.

 

 

 

Microsoft ® and Microsoft Excel ® are registered trademarks of Microsoft Corporation. OzGrid.com is in no way associated with Microsoft.

OzGrid.com accepts no responsibility for any adverse affect that may result from undertaking our training.