An introduction to Excel’s interface
To be able to work effectively in any program, you need to have an idea of both which functions are available and where to find them. Excel is an advanced program with hundreds or even thousands of functions. But they are clearly not prone to noise; by learning the structure, you can find more features yourself when you need them.
Just like in Word, Excel uses a layout where many, if not most, functions are found in the so-called ribbon area: the toolbar at the top of the program’s window, divided into tabs such as Start, Insert and Data.
Unlike Word, you don’t necessarily have to mess around with these tabs, and many users have barely looked at tabs other than Start and Insert. However, there are quite a few to pick up if you start exploring them.
- Start: Basic formatting and shortcuts to commonly used functions such as AutoSum and Filter.
- Insert: When you want a chart, table, image or text box.
- Draw: Basically only useful if you have a touch screen.
- Page Layout: Only if you are going to print.
- Formulas: One of Excel’s most important components is all the built-in math formulas, and this tab helps you find formulas based on areas like finance, text, and math.
- Data: Mainly for connecting to external data sources such as databases, but also has some functions useful for anyone.
- Review: Will someone other than you work in a document, or will you continue to use the same document for a long time? Then comments and tracking are very smart, and you’ll find that here.
- Show: In addition to previewing for print, here is the ability to lock the first row and/or column, which can be very handy in a large table. There are also settings here to show the open spreadsheet in several windows so you can look at several places at the same time.
What we think you will notice when you become more at home in Excel, however, is that you rarely need to leave the Start tab. Almost all the functions in the other tabs either have keyboard shortcuts or are formulas you can learn.
Re-stow in the menus
Do you find the order of commands in the menus impractical, or are you missing some practical function? Excel has good options for setting what should be included and in what order it should be.
Right-click in an empty space anywhere in the ribbon and select Customize the menu tabor select File -> Options and click on Customize the ribbon.
On the right side of the dialog box, you’ll find the existing content and layout of the ribbon. There you can add new tabs and groups inside tabs, but also change the order of what is already there. For example, say you don’t use Formatfunctions in the Start tab as often but in contrast the Editing functions such as AutoSum and Sort and Filter. Then you can simply grab and drag Format so that it is last in the list under Start. Press OK and Excel will save the change.
In the list on the left you will normally find “regular commands” but if you are looking to add practical functions that are not there, you can choose Commands not in the ribbon in the drop-down menu below Choose commands from.
The spreadsheet
The bulk of the work in Excel is done in the grid of cells in rows and columns that make up the spreadsheet itself. This is where you build tables, fill in formulas, and rearrange, sort, and make the data understandable. Make friends with this grid – it makes everything easier.
The grid starts at the top left with row 1, column A. You don’t have to start there, but can place numbers and other things in any cells you want. New documents are created with a blank sheet called Sheet1 – you add new ones by pressing the plus button at the bottom left of the program window. Each sheet can handle a maximum of 1,048,576 rows and 16,384 columns. Each cell can contain a maximum of 32,767 characters and 253 line breaks. You always have plenty of space to say the least.
No matter where you are in the grid, you can see row and column numbers, and these have several important functions.
Right-click one of these row or column numbers. In the context menu that appears, you will find, for example, the functions add and Remove. Add tabs inserts a new row or column above or to the left of what you clicked, and moves all content one step down or to the right. Formulas that refer to a particular cell are automatically changed when you do this – you don’t have to go in and change anything yourself.
If you have copied or cut out one or more cells before you right-click, Add to will be changed to Paste copied/cut cells. Depending on how you copied, this may move only certain rows/columns or all of them.
Click on one of the numbers and that entire row or column will be highlighted. You can use that to, for example, copy all the content in it, or to change the format of the content. A typical area of use is when you have to paste numbers, text and dates into a column and do not want Excel to format these differently but just as plain text.
This is how number formats work in Excel
A cell in Excel can contain different kinds of data, which in turn can be formatted in different ways. You can see what format a cell has by selecting it and looking in the section Speech in the Start tab. It is important to understand the difference between what the content looks like and what it actually is.
Take dates for example. When you enter a date in an empty cell that has the “general” number format, Excel recognizes it and automatically changes the number format to “date”. Select the cell and click on the drop-down menu where it now says date. You can see here that if you change to the “text” format, the content will be displayed as a number. For example, 25 March 2020 is 43915.
This is because dates are internally stored as numbers where “1” means January 1, 1900. You can’t use dates earlier than that in Excel, so genealogists and others have to work around it by, for example, using text format instead of date format. It is still possible to sort in date order as long as you follow the ISO standard of year-month-day, but you cannot use such cells in calculations of, for example, how many days pass between two dates. Such problems can also be worked around but it is too advanced for this guide.
Formulas
You can of course use Excel only to manually enter text, dates and numbers into tables. But the utility of the program comes from writing formulas that retrieve data from other cells, perform calculations of various kinds, and put the results in their cell.
As long as you don’t create circular references (where the contents of one cell depends on the contents of a cell whose contents depend on the contents of the first cell), you can have formulas that refer to formulas that refer to formulas—and so on. Make a change at the beginning of the chain and the results change all the way to the last step.
A formula always starts with an equal sign. After that, you do not write mathematical formulas according to Excel’s special syntax. It is reminiscent of regular math, but is still unique. So this is what it might look like:
=BETALNING(B6/12;B8*12;B4)
=OM(H8>250000; (H8-250000)*0,05; 0)+OM(H8>175000;OM(H8>250000;1500;(H8-175000)*0,02);0)
References to cells are written with the column letter followed by the row number, for example B6 or H8. An important thing to learn is the difference between a relative reference and an absolute one.
Say you are building a table with prices, quantities and costs, for example Cucumber – SEK 10/piece – 3 pieces – SEK 30. You may fill in the first three columns manually, but the last one is suitable for a formula. If it says Cucumber in A2, SEK 10.0 in B2 and 3 in C2, you can fill in the following formula in D2: =B2*C2.
If you then fill in A3: Tomatoes, B3: 39.90 (krona/kg), C3: 1.23 (kg), you can copy D2 and paste in D3 and Excel will automatically fill in the correct formula: =B3*C3. This is called a relative reference.
Placing dollar signs on both sides of the column letter (for example, $B$2) makes the reference absolute. This means that if you copy the formula and paste it into another cell, that reference will still be to the exact same cell. For example, it could be a cell where you fill in an interest rate to be used in many places in a table. You can mix relative and absolute references in a formula.
In a later part of the school, we will go a little deeper into how to use advanced mathematics in Excel, for example to calculate with roots and exponents, which is common in economics. A simple example: calculate what the daily interest is if the annual interest is 2.5 percent with the formula =(1+A1)^(1/365)-1 (in A1 you have filled in 2.5% or 0.025).
How to use data from Excel in Word documents
Excel certainly has its own functions for making a simple layout, but if you are going to write a report or similar and want to use data from large Excel tables, it is convenient to be able to connect the two Office programs.
If you have very simple data or simply prefer to format tables in Excel, you can highlight what you want and simply paste it into Word. But that means you have to make any changes manually in Word. If you want to be able to have updated numbers and diagrams, it is much more practical to link or embed.
- Start by selecting what you want to paste into Excel.
- Open the Word document and place the pointer in the correct place.
- Choose Paste -> Paste special.
- Choose Like: Microsoft Excel spreadsheet object.
- Choose either Paste or Paste link to the left.
The above means that a copy of the content is inserted directly into Word and you can edit it there. The latter means that you create a link to that part of the Excel sheet and to make changes you have to do it in Excel.
Linked content can be very handy if you have a large, complex spreadsheet in Excel and want to paste only the results of all calculations, either a table or a chart. Then you can go in and add, delete or change data in Excel and suddenly get updated results in Word.
Learn keyboard shortcuts
Excel is full of keyboard shortcuts, and if you watch a veteran user at work, you’ll probably find that they very rarely reach for the mouse. You can do almost anything you can think of with just the keyboard, and it’s often faster than rummaging through menus with the mouse.
We don’t have space to go through all the handy keyboard shortcuts, but here is a small selection of the most useful commands that are unique to Excel (copy, paste, select all and the like you probably already know):
- tab – when you start filling in a formula, Excel displays a list of suggestions. Use the arrow keys to select the correct one and press tab to fill it out. For example, you can more quickly write a long formula name such as NET WORKING DAYS.
- F4 – toggles between relative and absolute references for the cell reference you have the pointer in, so you don’t have to type dollar signs. The order is relative -> absolute row and column -> absolute row -> absolute column.
- F2 – edit the selected cell.
- Alt-return – insert a line break in a cell.
- Ctrl-D – first select a number of cells in one or more columns. Ctrl-D then copies the contents of the top cell of each selected column to the selected cells below it.
- Ctrl-Home followed by Shift-Ctrl-End – mark the entire used area in the open sheet.
- Ctrl arrow key – jump to the last cell with content before an empty cell, in the direction the arrow points.
- Shift-ctrl-arrow key – select all cells that have content starting from the cell you have now selected, in the direction the arrow points.
- Ctrl-H – open Find and Replace where you can quickly replace, for example, incorrect points with commas and the like. Select a number of cells, rows or columns before to limit the function to these.
There are hundreds more. When you hover over a feature in the menus, you can see that feature’s keyboard shortcut, and you can also check out Microsoft’s list here.