1 Starting with Excel
Excel is a program from the Microsoft Office package and it allows you to make calculations in calculation sheets (spreadsheets). This chapter is an introduction to spreadsheets in general and in Excel specific.
1.1 What is Excel?
A spreadsheet is a computer program with which you can put data (text, numbers, …) in sheets consisting of rows and columns. Besides capturing data, you can also perform a variety of operations on the data. Microsoft Excel is such a program.
The term spreadsheet is also used to refer to the file created by the program. And in Excel, an individual file is also referred to as a workbook. The standard file format in Excel is .xlsx. However, Excel can save a workbook in other file formats and can also read other file formats.
A workbook can have one or more worksheets. A worksheet is divided into rows and columns. The intersection of a row and a column is a cell. Individual cells are usually identified by a column letter and a row number. For example, B3 specifies the cell in column B and row 3.
In these cells, you can enter text, numbers, and formulas. With the formulas, you can perform calculations with numbers or operations on texts. In the formulas, references to other cells may occur, which gives you the possibility to set up large and complex calculation models. With each change, everything will be automatically recalculated.
This makes spreadsheets very useful for running the so-called What-if analysis. Here you change some values in the model and then look at what impact this has on the results in the model. You can then quickly calculate a number of different scenarios. In addition, it is not difficult to make nice graphs using Excel.
1.2 Starting and Closing Excel
The possibilities to start Excel depends on the way the system is installed. This course assumes a default installation of Microsoft Office 365 EN on a system with Windows 10. On almost all computers Excel can be started through the start button of Windows and this method is described here. The most convenient start is through a shortcut on the desktop or a button on the taskbar.
Task 1.1
- Choose Start > Excel.
The starting screen from Excel appears. In the left part, you can see the recently opened files and in the right part an overview of the available templates.
- Click on the template Blank workbook. A new workbook is displayed with the name Book1 and an empty worksheet with the name Sheet1.
Disabling start screen
Normally the start screen appears at startup. This screen displays the most recent documents and the available templates. This can be useful in some cases, but it is also possible to start Excel with an empty sheet.
First start Excel an then choose File > Options > tab General and deselect the option Show the Start screen when this application starts.
The first time you start Excel again, you get immediately a new workbook with an empty sheet.
There are two methods to stop working with Excel and closing the program window:
- Through the close button of the program window. This is a button with an X in the upper right corner of the window.
- Through the menu File > Close.
- Close Excel.
When something is changed in de workbook, you always get a dialog window asking whether the changes should or should not be saved.
1.3 The Excel window
The Excel program window has three parts: application control, worksheet, and status bar. You can see all three parts from top to bottom in the figure below.
- 1 - File
-
This button is in the upper-left corner of the program window. If you click this button, a drop-down list will appear with some basic commands like creating, opening, saving, and printing of files. Through this button, you also have access to the important command Options, with which you can set your preferences. This is also called the Backstage view.
- 2 - Quick Access Toolbar
-
The Quick Access Toolbar is a customizable toolbar for shortcuts to frequently used features and commands. It is also where you can add the shortcuts to Excel features that are not available on the ribbon. The default installation has three shortcuts:
Save
Undo
Redo
Form
- 3 - Ribbon
-
The ribbon is a panel, a wide toolbar at the upper side of the program window. The commands on the ribbon are organized in logical groups and collected together under tabs like Home, Insert, Page Layout, … Each tab has a specific task that it performs.
Some tabs, e.g. contextual tabs, are only displayed when they are needed. An example is the tab Chart Tools, that only appears when a chart is selected. Furthermore, the commands you will need often are on the left and the most specialized commands are on the right.
There is no way to delete the ribbon, but you can minimize it through the keyboard shortcut CTRL + F1. Then you see only the tabs. Pressing this shortcut again restores the ribbon.
- 4 - Tabs
-
At the upper side of the ribbon are tabs. On each tab, there are groups of commands. Some tabs are only displayed when they are needed.
- 5 - Groups
-
On each tab, there are groups of related commands. A group contains all the necessary commands for a particular task. Not all the commands of a group are mostly visible. When you want to see more options for the group you should click on the arrow
in the bottom right corner of the group. - 6 - Worksheet Name
-
Name of the workbook (the file)
- 7 - Command button
-
When you click on a command button then usually the change takes effect immediately. It is also possible that a choice list or a dialog box appears first.
- 8 - Active cell
-
The selected cell for the active worksheet is the active cell. The active cell is highlighted with a black border. In Figure 1.1,
A1is the active cell. - 9 - Name Box
-
The name box is located left to the formula bar above the worksheet area. It displays the cell address (cell reference) of the active cell. It will also show the name assigned to a cell or range of cells. The name box can also be used to assign names to cells or ranges of cells.
- 10 Formula Bar
-
The formula bar is located above the worksheet area. It displays the data or the formula stored in the active cell. The formula bar can be used to enter or edit the data or formula in the active cell.
- 11 - Worksheet
-
A workbook in Excel can contain multiple worksheets. When creating a new workbook, by default Excel creates an empty worksheet with the names Sheet1. A worksheet can be activated by clicking on the the tab of the worksheet at the bottom of the window (number 11 in Figure 1.1). A new worksheet can be created by clicking on
+(number 12 in the figure).
1.4 Creating New Workbook
Any time you can create a new workbook. This can be a blank workbook, but also a new workbook based on a particular template.
Task 1.2
Click on File > New. In the right part a list with available templates is displayed.
Click on Blank workbook. A new workbook with one worksheet is created.
1.5 Open workbook
Commonly used methods to open an existing workbook are:
- Double-clicking with Windows Explorer on an Excel file. This opens the file directly in Excel.
- Choose Open in the Excel start screen. You get the dialog window Open.
- Choose File > Open in Excel. You also get the dialog window Open.
With the choice This PC or Browse you can browse to the file. You also have the possibility to open files with other formats. To do this, click on the arrow in the bottom right corner at the file type list:
Task 1.3 File: Weatherleather.xlsx
If necessary, start Excel.
Open the file.
1.6 Saving Workbook
The most frequently used methods to save a workbook are through
- File > Save.
- Quick Access Toolbar > button Save.
When you re-save an existing workbook, the previous version of the workbook is overwritten. When you save a workbook for the first time, then Excel shows the dialog box Save as.
Task 1.4
- Save the workbook using one of the methods mentioned above.
There are now two possible situations, depending on whether the workbook has been saved before and therefore already exists.
- In the case of an existing workbook, the previous version of the workbook is overwritten.
- In the case of a new workbook the dialog box Save as appears. Then you must perform an additional step.
Select the desired storage location
Enter your file name in the box File name and click Save.
1.7 Workbook Saving as
Sometimes, it may be desirable to have multiple versions of a file. In that case, you can save the file under another name through Save as. Also, it may be necessary to save the file in an older Excel format which is also possible through Save as.
In addition to the name of the file, you can also specify the file format to save:
The possible file formats are shown in Figure 1.5.
1.8 File formats of Excel
An overview of the new and old file formats of Excel.
File formats for Excel 2007, Excel 2010, Excel 2013 and Excel 2016
- XLSX
-
This is the default format for workbooks without macros, created with Excel 2007, Excel 2010, Excel 2013, and Excel 2016. In this formats, macros are not allowed.
- XLSM
-
This is the file format for workbooks with macros.
- XLSB
-
This is a special binary file format for very large workbooks with tons of data.
- XLTX
-
This is the file format for an Excel template file without macros.
- XLTM
-
This is the file format for an Excel template file with macros. Template files can be used to generate new workbooks containing the same layout and content as the template.
Old file formats
- XLS
-
This is the file format for workbooks created with Excel 2003 and older programs. When you open this file format in a newer Excel version, it will be opened in the Compatibility mode. In this mode, you can make changes, but if you want to save the file, Excel checks whether the changes are supported in the old file format and displays a list of components that are not supported.
- XLT
-
The file format for templates created with Excel 2003 and before.