1.1 Workbooks and spreadsheets
An Excel file is called a workbook and consists of one or several spreadsheets. A spreadsheet is built up by many cells that may contain text, numbers or formulas that refer to other cells. Each cell has an address defined by a column number (from 1 to 1 048 561) and a row number (from A to XFD).
The nice thing about Excel is that you can perform operations on the numbers or the text written in the cells. You can do calculations in a budget, complex mathematical calculations, operations on a text, etc.
In figure 1.1 the numbers 2 and 3 are written in the cells A1 and A2 respectively. A formula in Excel always starts with a “=”. In cell A3 we have written the formula “=A1+A2” so that the content in the cell becomes 5. When writing the formula we do not have to write “A1”. It is much easier to click on the cell A1 to get the cell address written in the formula. Formulas will be discussed further in chapter 2.
Figure 1.1 A formula in Excel.
The screen
When you start Excel, the first spreadsheet in a workbook opens with menus and options shown on a ribbon (see figure 1.2). The ribbon is divided in several tabs (File, Home, Insert, etc.) where each tab contains a number of groups. Figure 1.2 shows the different groups (Clipboard, Font, etc.) under the tab Home. Each group offers several choices. The group Font, for instance, offers different choices for formatting. To the right and at the bottom of this group, we find an arrow. If we click on it, a dialogue box with more choices appears on the screen.
Figure 1.2 The ribbon in Excel 2010.
The tab File at the left of the ribbon is green and is something new in Excel 2010. When we click this tab, we enter a so-called backstage view where we can save, print and administer our files.
When we click Options in the backstage view, the Excel Options dialogue box appears as shown in figure 1.3. Here we can change the contents of the ribbon, choose what we want to appear on the Quick Access toolbar, etc. We will return to this toolbox and to the ribbon later in this and in other chapters.
Figure 1.3 The Excel Options dialogue box.
The Quick Access toolbar is shown under the ribbon in figure 1.2. It’s a good idea to include the most commonly used commands in this toolbar. We can do that from the dialogue box in figure 1.3 or by clicking the arrow symbol on the Quick Access toolbar.
Below the Quick Access toolbar we find the formula bar which shows the content of the active cell. At the left on this line, we find a window with the address of the active cell.
Figure 1.4 illustrates the lower part of a spreadsheet. The three spreadsheet tabs (Sheet1, etc.) represents three different spreadsheets. The line at the bottom is called the status bar and offers different choices. We may for instance increase or decrease the size of the cells.
In figure 1.4 the cells A2:A5 contains some values. When these cells are marked, the average, the number of values, and the sum of the values appears on the status bar. We can change the status bar by right-clicking on it and choosing the desired information from the menu that appears.
Figure 1.4 Spreadsheet tabs and status bar.
Organizing your workbooks
Every time you open Excel, a new workbook with empty spreadsheets appears on the screen. Excel also offers several templates, which are workbooks designed for different purposes. To enter one of these templates, click the File tab and then New. It is also possible to create your own templates in Excel.
The height of rows and the width of columns can easily be changed by clicking and pulling the lines between two row or column headings. If you right-click on a series of marked row or column headings, a menu appears where you can hide the row or column, choose a desired height or width, etc.
If you want to look at one part of a spreadsheet and move (vertically or horizontally) on another part, mark a row or a column by clicking the heading, click the tab View and choose Freeze Panes in the group Window. Then the cells over the marked row (or to the left of the marked column) will be frozen while you scroll through the rest of the spreadsheet. If you start this procedure standing in a single cell, the cells over and to the left of the cell will freeze.
If you want to work with various workbooks at the same time, open the desired workbooks, click the tab View, and choose Arrange All in the group Window. Then you will get the dialogue box in figure 1.5, where, among other things, you can choose to place the workbooks side by side.
You can also open multiple copies of the same spreadsheet. To do this, first choose the tab View and then New Window under the group Window. Then you can sort the copies of the spreadsheet as described above.
If you need to make drastic changes to a spreadsheet, right-click the spreadsheet tab (at the bottom of the screen). Then you will get a menu where you can delete the spreadsheet, add new spreadsheets, move the spreadsheet to a different document, etc. You can also change the order of the spreadsheets in a workbook by clicking and pulling the spreadsheet tabs. If you hold down the Ctrl-button while clicking and dragging a sheet tab, a copy is made of the spreadsheet.
If you want to delete more than one spreadsheet, hold down the Ctrl-key and click the spreadsheets’ tabs of current interest to mark them. Then right-click one of the spreadsheet tabs and choose Delete.
Figure 1.5 Arrange windows.
Large spreadsheet models must make it clear where the user should put in data and where the results are shown. Calculations should be kept in a separate part of the spreadsheet or in separate spreadsheets. Data may be accessed over several spreadsheets with commands getting data from other spreadsheets.
Suppose you have a workbook with three spreadsheets. Sheet2 contains the number 5 in cell B3 and Sheet3 contains the number 8 in cell B3. Your position is cell B3 in Sheet1, and you want to sum the two mentioned numbers. To do this, type “=” and click the tab for Sheet2 before you click on cell B3 where the number 5 is located. Then type “+” and click the tab for Sheet3 before you click on cell B3 where the number 8 is located. When you press Enter, the total of 13 will appear in cell B3 in Sheet1. The formula in this cell will be “=Sheet2!B3+Sheet3!B32”.
Objects
You can also add different objects to a spreadsheet, such as Word documents, equations and pictures. To do this, click the Insert tab and Object under the group Text, so that the dialogue box in figure 1.6 appears. Under the tab Create from File you may choose a filename for a Word document, a pdf-file, or another file that you want to include in your spreadsheet. If you don’t tick the Link to file option here, the file will be included in the Excel file, increasing its size accordingly. If you do choose the Link to file option, a link will be established between the two files, resulting in a minimal size increase of the Excel file. Do keep in mind that you are working with two separate files in this case, and that both files must be copied or moved if you want to keep the link between them when you copy or move the Excel file. When you add an object to a spreadsheet, the object will appear in a box that you can move around in the spreadsheet.
Under the tab Create New in figure 1.6 we find a menu where we can choose between different objects such as Word documents or pictures. One of the choices is Microsoft Equation 3.0 (or MathType, if this program is installed on your computer). This tool makes it possible to include mathematical expressions in our spreadsheet.
Figure 1.6 The Object dialogue box.
Assume we want to write the following expression into a spreadsheet:
If we choose Microsoft Equation 3.0 in the dialogue box in figure 1.6, the equation editor in figure 1.7 appears. The different choices here make it possible to write mathematical symbols, operators, brackets, arrows, etc.
Figure 1.7 The Microsoft Equation editor.
In figure 1.7 we have written the requested expression. If we click somewhere in the spreadsheet, the expression will appear in a box that can be moved around in the spreadsheet.
A mathematical expression can also be made by choosing the tab Insert and Equation in the group Symbols. Then the new tab Equation Tools appears on the ribbon as shown in figure 1.8. This editor has even more choices than the one in figure 1.7.
Figure 1.8 Equation tools in Excel 2010.
We have seen that it is possible to include a Word document into a spreadsheet. In the same way, it is also possible to include an Excel workbook into a Word document (or a PowerPoint presentation). Such operations are quite straightforward since all these programs are included in Microsoft Office.
Printing
To print parts of a spreadsheet we press Ctrl+p or choose the tab File and then Print. In the backstage view that appears, we get several options and a preview of the print.
Before we do this, we might want to prepare the spreadsheet by adding headers or footers, to declare that gridlines should be printed, etc. To do this we choose the tab Page Layout and Print Titles in the group Page Setup (or we press the arrow at the right bottom in this group). Then the dialogue box in figure 1.9 appears. Here we can choose the area we want to print, add headers and footers, define margins, and so on. In the group Page Setup we can also adjust margins, choose orientation, etc.
Figure 1.9 Page Setup.
If you want to work more efficiently with Excel, it can be a good idea to learn more about shortcut keys. You may know the commands Ctrl+c for copy, Ctrl+v for paste, and Ctrl+z for undo. Ctrl+c means that you hold down the Ctrl key while you press “c”. Let’s take a short look at some other examples.
If you want to select all cells in a spreadsheet, press Ctrl+a. To format the content of a cell to per cent with no decimals, press Ctrl+Shift+%. To minimize the active workbook, press Ctrl+F9, and so on. Excel offers lots of shortcut keys. Do also notice that screen tips appear as you move the mouse pointer over some of the buttons on the ribbon.
If we press Alt or F10, some key tips appear on the ribbon. On the tab Home, for instance, we see an “H”. If this tab is open and we press Alt or F10 and then the key “h”, other key tips appear on the different choices. On the per cent button we see a “P”. This means that we can format the content of a cell by pressing the sequence Alt-h-p.
1.2 Working with data
All Excel users need to build their spreadsheet models and to process their data in a secure and efficient way. Various challenges occur when we work with spreadsheets, such as: How should we sort our data? What is the highest value in a range of cells? Is it possible to include a Greek letter in a text? In this chapter we will take a closer look at a few selected methods and examples to illustrate some of the possibilities we have when working with a spreadsheet.
Under the tab Home we find several choices for formatting cells. In the group Font, bold letters (or numbers) can be chosen by clicking the B button, the colour of the cell’s background can be changed by clicking the bucket symbol, etc.
In the group Alignment, the content of the selected cells can be adjusted horizontally and vertically. If we click on Wrap Text, the text in the selected cells will be written over several lines if the cell widths are less than the length of the text. In the Alignment group we also find the choice Merge and Center to merge cells both vertically and horizontally.
In the group Clipboard we find choices for copy, cut and paste. These commands can also be performed with the shortcut keys Ctrl+c, Ctrl+x and Ctrl+v respectively. If we want to include special symbols in a text in a cell, such as σ, ∞, or β, this can be done by choosing the tab Insert and Symbol. Then a dialogue box with a number of character sets appears where we can find the symbols we need. You should check out the other tabs with all their groups and different choices on your own.
Remember that it is possible to change the content of the different groups on the ribbon. This can be done in the dialogue box that appears when we choose the tab File and then Options and Customize Ribbon.
Naming cell ranges
In Excel we often use cell ranges (for instance A3:C7). It might be convenient to give often used cell ranges their own names. Then we mark the cell range of interest, and choose the tab Formulas and Define Name. In the dialogue box that appears, we write a name (for instance Dataset1) and click OK. Now the name of the cell range can be used in formulas, dialogue boxes, etc.
An alternative way to name a cell range is to mark the cell range of interest, and then write the new name in the name box (to the left of the formula bar) and press enter. We get a list of all such names if we click on the arrow in the right side of the name box. We get a dialogue box with a detailed list if we choose the tab Formulas and Name Manager or press Ctrl+F3. This dialogue box also allows us to edit, delete and define names of cell ranges. For pedagogical reasons we have chosen not to use names for cell ranges in this book.
Sorting and filtering
Excel offers several methods for sorting and filtering data. Let’s take a brief look at some of them.
To the left of figure 1.10 we find a list of sales to four different customers. If we click on one of the cells B3:B6 and choose the tab Home and Sort & Filter, a menu appears where we can choose for instance Sort Smallest to Largest. Then our list will appear as illustrated to the right of figure 1.10. Notice that the names in column A are moved around together with the sales figures in column B.
Figure 1.10 Sales figures.
If we click on one of the cells A3:A6 and choose the tab Home and Sort & Filter, the menu will offer the choice Sort A to Z (among others). If we choose this, the list will be sorted alphabetically. If we click Filter in the same menu, arrow buttons will be added to the first cell with content in each column. When we click one of these buttons, a menu for sorting and filtering appears. Such arrow buttons can also be added to a column by choosing the tab Data and Filter.
Another choice under the tab Data is Sort. If we click on Sort (while standing in a cell that is not empty), the dialogue box in figure 1.11 will appear with several choices for sorting.
Figure 1.11 Sorting data.
Autofill
To be able to use Excel efficiently, you should learn about some of the shortcuts and other tools this program offers. An example is the autofill function, which means that Excel gives you different ways to fill cells in a spreadsheet efficiently with systematic data.
An order of numbers can be filled in down a column (or across a row) by filling in the two first cells, marking these cells, and then clicking and pulling the black cross in the bottom right corner of the cells. If we have written 0 and 5 in the cells A3:A4, we can mark these and pull the black cross down column A. Excel will then autofill the empty cells in column A with 10, 15, 20, and so on. If the column next to the empty cells contains data, we can just double-click the black cross instead of pulling it down. The empty cells will then be filled down as long as there is data in the next column.
You may also fill cells as described above using the content of lists (such as weekdays, months, and names). In such cases it is enough to fill the first cell and pull down with the black cross. If we for instance write Monday in one cell and pull down with the black cross (in the lower right corner), the cells will fill with Monday, Tuesday, etc. If the Ctrl-key is pressed while the black cross is pulled down, all cells will be filled with the content of the first cell, in this case Monday. Note that the button for autofill options appears when you use the autofill function. This allows you to choose if you want to complete the series, use the same formatting on the series as in the first cell, etc.
There are many variants of the autofill function that are worth exploring. If you write the number 3 in one cell and pull down the black cross, the cells will fill with the number 3. However, if you hold down the Ctrl-key and pull down the black cross, the cells will fill with the numbers 3, 4, 5, etc. If you want to fill an area of a spreadsheet with identical values, mark the area and then type in the value of choice. (This value will be placed in the upper left cell of the area.) If you then press Ctrl+Enter, the cells in the area will be filled with the value.
Custom lists
Excel includes lists with the weekdays and the months. In addition we can also define our own lists by choosing the tab File and Options. In the appearing dialogue box we choose Advanced and scroll down to the group General where we click Edit Custom Lists. Then the dialogue box in figure 1.12 appears.
In this case we have a list of customers in the cells A3:A10. If we put these cell addresses in the window marked Import list from cells and click Import, a new list with the names Anderson–Olson will be included in our Excel program. We can now use this list by autofill in other workbooks.
Comments
Excel allows us to add comments to the cells in a spreadsheet. If we for instance want to write down explanations to a formula or comments to a result, this can be a helpful tool.
To add a comment to a cell, we click the right mouse button and choose Insert Comment, or we choose the tab Review and New Comment in the group Comments. Then a box will open next to the actual cell so that we can write any comment we want. This is illustrated in figure 1.13.
To close the box with the comment, we click somewhere in the spreadsheet. A cell with a comment will appear with a little red flag in the upper right corner. If we hover over a cell with a red flag, the comment will appear next to the cell. To show all the comments in a spreadsheet, we click Show All Comments (in the group Comments). To hide the comments again, we click Show All Comments once more.
Figure 1.12 Custom Lists.
Figure 1.13 Comment.
Formatting
The content of the cells in an Excel spreadsheet can be given different formats, colours, alignments, etc. If we choose Cell Styles under the tab Home in the group Styles, we get a menu that offers many different predefined formats with combinations of colours, fonts, etc.
If we want to take full control over the formatting, we can choose Format and Format Cells under the tab Home in the group Cells, or click the right mouse button and choose Format Cells. Then the dialogue box in figure 1.14 appears where we can choose between different formats, alignments, fonts, etc.
Figure 1.14 The Format Cells dialogue box.
The date format requires some further explanation. A date in Excel is represented by a number in such a way that 1 represents 01.01.1900, 2 represents 02.01.1900, and so on. (In Excel for Macintosh, 1 represents 01.01.1904, and so on.) This means that if you write the number 40,000 in a cell and choose the date format, the number changes to 06.07.2009 (which is 40,000 days after 01.01.1900). On the other hand, if we write 06.07.2009 in the cell, a date format will be chosen automatically. This is the easiest way to use a date format in a cell.
Under the tab Home we find the choice Conditional Formatting in the group Styles. If we mark the cells A1:C6, as shown in figure 1.15, and choose Home and Conditional Formatting, a menu appears. If we choose Highlight Cells Rules and Less than (in the next menu), the dialogue box in figure 1.15 appears. If we write 20 under Format cells that are LESS THAN:, the cells containing numbers less than 20 (in the area A1:C6) will be formatted as light red fill with dark red text. (The red colour appears as grey in the figure.)
Figure 1.15 Conditional formatting.
Data validation
Sometimes we want to set restrictions regarding which values the user should be allowed to write into some of the cells in a spreadsheet. Take a look at figure 2.1 and assume the users of the spreadsheet only should be allowed to write prices larger than 0 in cell B2. While standing in cell B2, we choose the tab Data and Data Validation in the group Data Tools. In the appearing menu we choose Data Validation so that the dialogue box in figure 1.16 emerges. Under Settings we choose Decimal, greater than or equal to, and 0. This will allow only numbers greater than or equal to 0 in cell B2.
Figure 1.16 Data Validation.
Under Settings in the same dialogue box, we also find the choice List (under Allow). For such validations we can declare some cells as Source that contains a list with valid data. If we also tick In-cell dropdown, an arrow button and a menu with the list will be shown each time we click on the cell in question.