Microsoft Excel 2019 for beginners Autor: Jan Zitniak Cover page: Peter Rybar ISBN 978-80-973190-2-1 © 2019 Jan Zitniak All Rights Reserved The information in this book is provided on an “as is” basis. The author shall have neither liability nor responsibility to any person or entity with respect to any loss or damages arising from the information contained in this book. No part of this book may be reproduced or transmitted in any form or by any means, electronic or mechanical, including photocopying, recording, or by any information storage and retrieval system, without permission in writing from the publisher. Content
Starting Excel and opening a new workbook
Description of the Microsoft Excel 2019 environment
Entering data into a worksheet
Entering numerical and test data
Calculations in the status bar
Changing the appearance of cell contents
Formatting columns, rows and sheets
Automatic copying and filling of data
Absolute reference – working with a constant
Inserting, deleting, renaming and changing the color of a sheet
Locking rows and columns in a table (Freeze Panes)
Inserting headers and footers, page numbering
Introduction
In the years since Microsoft Excel was released in 1985, it has won the hearts of a large number of users in the private, education and especially in the business sector. Excel is handy, effective and user-friendly software designed to work with spreadsheets, in particular, for both simple and more complex calculations and even for analysis and predictive modeling. As it says in the program, Excel's spreadsheets are usable in any direction and not just for calculations, but also to create and edit tables, produce user-friendly forms and to develop custom programs and functionalities through Visual Basic for Applications (VBA). This book focuses on practices suitable for users wishing to “brush up” on the basics of Excel. The steps outlined in this book (unless otherwise mentioned) also apply to Excel 2007, 2010, 2013, 2016 and 365, and come from the author's many years as a certified Microsoft Office Excel® Expert instructing at both smaller firms and at larger, transnational companies. This book is dedicated to my wife Beata, my daughter Lea, my son Alex, my close family and the many people who have had the opportunity to attend my courses. If you have any questions about the material in this book, please feel free to contact me at Táto e-mailová adresa je chránená pred spamovacími robotmi. Na jej zobrazenie potrebuješ mať nainštalovaný JavaScript.. You can download examples and find other information from this book at http://www.janzitniak.info/en/books. I believe that you will find this book of practical assistance useful and the tips inside will help you work effectively with Excel. If you like my book, please donate me clicking to https://www.paypal.me/janzitniak. Thank you! Jan Zitniak In case you are interested in intermediate level, I would like to recommend you my book Microsoft Excel 2019 for intermediates. The book is available on my website http://www.janzitniak.info/en/books, please click here. 
Starting Excel and opening a new workbook
Running Microsoft Excel 2019, you will notice that there have been less changes made from previous versions of Excel. The left side of the screen has the ribbon with the icons Home, New and Open, and others such as Account, Feedback and Options. Home, which is automatically defaulted, allows you to create Blank Workbook, this feature is located on the right side of the screen, and contains Excel tutorials for beginners (Welcome to Excel, Formula Tutorial, PivotTable Tutorial, Get more out of PivotTables). Templates are pre-prepared tables with specific content such as the earlier mentioned tutorials, as well as practical tables (such as invoices, calendars, Gantt charts, etc.) which can be found by clicking on More templates. Excel will then switch you to New and simultaneously display all available templates where you can even search (with the search box located at the top) or select based on a particular category, such as Business, Personal, Lists, Financial Management, Planners and Trackers, Charts and Budgets. To create a new workbook (or spreadsheet), click on Blank Workbook, which, you may recall, can be found in Home or New. Go back to Home. In addition to the templates, there is a list of workbook files divided into Recent, Pinned and Shared with Me. Recent shows the files that have been most recently opened. Pinned shows the “pinned” workbooks you wish to have always available. To pin a file, click on the pictogram 
, which you will find to the right of each file in the Recent list. Shared with Me, as the name implies, contains a list of the workbooks that have been shared with you (e.g. a colleague has sent you a file for editing). The Open icon found on the left opens the files you have saved on your computer, in the cloud or elsewhere (e.g. SharePoint). Accounts contain information about any users logged into Excel (365), the Office version in use and update information. In addition, you will find here the option to make visual changes to an entire program, news, license management, and more. Feedback allows you to send Microsoft your feedback about Excel. Options include the option to change Excel settings (e.g. author name, Excel language and environment, structure, influencing how Excel works) and to install additional applications (like Solver, an app well suited for calculating output data according to specified requirements). Important: While writing this book, the author used Microsoft Office 2019 preview respectively Microsoft Office 365, which is regularly updated once or twice a month and always brings new functionality. Therefore, you may find the descriptions slightly different from the version you are using. However, they should not fundamentally vary or affect the meaning of what is explained here. 
Important introductory terms
Microsoft Excel uses the word workbook instead of document. This is essentially the file created by Microsoft Excel. Every workbook contains pages, like the notebook you have in school (although, in Excel they are called “sheets”). A sheet, as appropriately named, is actually comprised of a large spreadsheet divided into cells (with 16,384 columns and 1,048,576 rows). Different numerals, text strings or formulas can be entered in the cells like in Microsoft Word and, of course, this data can be visually formatted. Formulas may be simple, such as =A1*2, to multiply the content of Cell A1 by 2, or more complex like =SUM(A2:A6), which adds the values of Cells A2 to A6 together. More complex formulas are called functions. The sheets may include a visual representation of the data in the form of a chart, or include a PivotTable to help you summarize, analyze and filter data quickly and efficiently.
Description of the Microsoft Excel 2019 environment
The principles of working with Microsoft Excel are similar to Microsoft Word. Every Microsoft Office application has a tab ribbon (Home, Insert, Page Layout, Formulas, Data, Review, View and Help). In addition to these tabs, there is a special tab (Backstage) with the name File containing options you may know from other applications (like Save as, Print and Open). For devices with a touchscreen (like tablets and mobile), a Draw tab may be available that allows you to easily write different notes on the table by drawing them. Important: The most frequently used tab, Home, contains the command icons most commonly used in practice. Important: The number of tabs in Excel and generally in all Microsoft Office applications is not fixed, but rather depends on whatever type sheet you are creating (and is marked). For example, when you create a chart, you also have Design and Format available to let you customize its content and appearance. Every tab is further divided into grouped commands. For example, Home contains a group with Clipboard, Font, Alignment, Number, Styles, Cells and Editing. A group contains command icons, each with a specific functionality. Important: To learn what a command icon contains, simply hover over it with the mouse and wait a moment. Excel then displays the help file with the explanatory description for the icon. Sometimes, it also shows the keyboard shortcut in brackets. Important: Some groups have a small arrow at the right. Clicking it shows additional commands and options not shown in the group. There are quick access commands in the upper-left hand area above the ribbon, where the defaults are Save, Undo and Repeat; in the case of a workbook stored on a server (e.g. OneDrive) AutoSave and, in touchscreen devices, also Touch/Mouse Mode. These are used for saving and either returning to the workbook or repeating actions in it. Other useful commands can be added by clicking on the “arrow”, ticking the necessary command and, in some cases, making an additional selection in More Commands... Note: It is recommended to add other useful commands such as New, Quick Print, Print Preview and Print. On the left side below the ribbon is the Name Box, which usually indicates the cell where the cursor is located (for instance, A1 at the beginning of the table), and to the right of it is the Formula Bar. It usually shows the contents of the cell indicated by the cursor and these can be edited, deleted or filled in. The spreadsheet itself is located below the Name Box and Formula Bar and is divided into a large number of columns (16,384) and rows (1,048,576). Letters are used to indicate the columns and number for the rows. Note: Such a large spreadsheet will ordinarily never be filled, but in practice it can be filled when data is imported from external databases or various information and accounting systems (e.g. SAP). The table contains fields that are called cells. Each cell has its own address, such as G12 or A1. The cell address is actually comprised of the column letter and row number. For example, Cell A1 is Column A and Row 1. The active cell is the one currently highlighted and common operations can be inserted into it, such as text, numbers and formulas. These values or, better said, data can be formatted (changing the color, size and also type, e.g. to a number, percentage, currency or other expression). The arrow keys let you move the cursor among cells, or you can use the mouse to hover the cursor over a cell and mark it. There are keyboard shortcuts (a combination of keys pressed at once, expressed in the table below with "+") that speed up movement around the workbook:
The status bar at the bottom displays Ready at the left to indicate the condition of the cell (e.g. Ready changes to Edit whenever a value is inserted into the cell or it is edited). The scroll bar is located on the far right and allows you to zoom in or isolate the contents of a sheet so they are legible to a reader. Current display modes such as Normal, Page Layout and Page Break Preview can be seen to the left of the scroll bar. In some situations (such as when non-empty cells are marked), Excel will show basic calculations such as the SUM, AVERAGE and COUNT in the middle of the status bar. These can be added to any other function by calling up the popup menu (right mouse button) above the status bar and clicking on the corresponding option. 
Entering data into a worksheet
Data entry can be divided into three parts:
- Entering numeric and text data (similar to Microsoft Word)
- Inserting a simple formula
- Inserting a function
Entering numerical and test data
Click on any cell, fill in the text and confirm by pressing the Enter key. Another way is to indicate any cell, enter the text in the Formula Bar and then press enter to confirm. 
Insert a simple formula
Any simple formula can be inserted into a cell, which makes Microsoft Excel in essence also a spreadsheet calculator. Just follow these steps:
- Click on the cell that will contain the result
- Insert the equal sign (=)
- After pressing the equals (=) key, click on the cell you want to include in the calculation (marked with a color)
- Press the appropriate operator key, e.g.:
+ (addition) – (subtraction) * (multiplication) / (division)
- On the keyboard, click on the cell you want to include in the calculation (the cell will be highlighted in a color)
- Confirm with the ENTER key or
Types of computational operators
The previous chapter showed how to insert a simple formula in the cell, where the content of Cell A1 was multiplied by 2. Here the operator “*” was used. But there are also other operators you can use and these are listed in the table below: 
The final column shows the execution priority. This is explained in detail with the following example: =2+1*3 If you put the above example into Excel and confirm it with Enter, you will get 5 instead of 9 as the result. This is because Excel (as well as mathematics) gives priority to multiplication over addition. If you wish to change it, use brackets to prioritize the selected part of the calculation before the other.
Inserting a function
In common practice, simple formulas are not enough. Functions are used when more sophisticated calculations are needed. Microsoft Excel has hundreds of functions that are divided into different categories according to their application, such as statistical, mathematical, logical, search, financial and more.
Inserting a simple function
Procedure for inserting a simple function:
- Click on the cell that will contain the result
- Find the AutoSum command in the Home tab AutoSum (located on the far right).
- Click on the arrows to select the appropriate function. Basic functions include:
- Once the appropriate function has been selected, Excel will insert it in the cell together with the automatically marked range to be included in the calculation (it usually prioritizes values above and then to the left). If the range is inappropriate, drag the mouse and press the left button at the same time to select a different one.
- Confirm with the ENTER key or
Note: In Step 3, instead of dragging the cursor, the cells can be overwritten “manually”. Example
| A | B | |
| 1 | Values | |
| 2 | 1 | |
| 3 | 2 | |
| 4 | 3 | |
| 5 | 4 | |
| 6 | 5 | |
| 7 | 15 | <- SUM |
| 8 | 3 | <- AVERAGE |
| 9 | 5 | <- COUNT |
| 10 | 5 | <- MAX |
| 11 | 1 | <- MIN |
Calculations in the status bar
Excel offers another option to display (controlled) calculations in the status bar (located in Excel at the bottom). Mark at least two (non-empty) cells on the sheet above. Excel then writes out the result in the status bar with regard to the used function. These include Average, Count and Sum, while more functions can be added such as Numerical Count, Maximum and Minimum by invoking the popup menu (right mouse button) above the status bar and indicating Numerical Count, Maximum or Minimum. Note: Count and Numerical Count are similar functions that determine the number of non-empty cells. Numerical Count determines the number of non-empty cells containing only numbers (including a number which is the result of a formula). Count determines the number of non-empty cells containing either numbers or text (even if it results from a formula). 
Cursor types in Excel
In Excel, it is very important how the mouse cursor looks. Based on the cursor’s appearance, different things can be done with the cells:
Working with data and sheets
Formatting data
You can find data formatting in the Home tab, in the Font group. Data formatting changes the appearance of cell contents and their size (e.g. 10, 12, 14), font (e.g. Calibri, Times New Roman, Arial), typeface (bold, italics, underlined), color, border, alignment and more, as well as the formatting of numbers in cells (e.g. inserting $ or € currency signs, see the chapter on Formatting numbers).
Changing the appearance of cell contents
You can change the appearance of a cell (“cell formatting”) by changing the font, font size, typeface (by setting it to bold, italics or underlined), alignment, background color of the cell (fill color) and font color. You can find cell formatting similarly as in Microsoft Word. Commands are located in the Font and Alignment groups. In addition, Excel provides basic formatting commands (by displaying a "mini-panel") when you press the right mouse button over a particular cell (or range of cells). 
Another way is to press the right button while hovering above a particular cell, where Format Cells allows you to select from any of the following options (shown in the groups below): 
Number group
Use Number to change the number format, such as to add the currency signs $ or € if you are using currencies (more in Formatting numbers). 
Alignment group
The second tab is Alignment. It has options for text alignment, wrapping test, wrapping, and tilting of contents. 
These are divided into the following groups: Text alignment - adjusts the horizontal or vertical alignment of the cell contents (i.e., the text will be centered inside the cell horizontally or vertically). Text control contains: Wrap text - wraps text into multiple rows in one cell (use it if you do not want to expand the column, but also want the full text to be displayed). Shrink to fit – shrinks the text to fit into the size of the cell). Merge cells – merges marked cells into a single cell (for instance, in table headings). Right-to-left – use this command for special languages such as Arabic where the text is written right-to-left. Orientation – shows the angle of tilted text. Note: You can find Alignment in the Home tab, in the Alignment group. 
Font group
This group formats cells in the standard way you know from Microsoft Word. 
Border group
You can add borders to a table and the cells inside it to visualize the data and print the table. The basic settings for borders in the tab consist of selecting a line style and color. 
The left side allows you to set the style and color of the line, which you then add by clicking on the right-hand diagram (or by applying the pre-set borders found in Presets).
Fill group
This fills the background of the cell with the selected color. You have the option of filling it with a single color and to add effects (Fill effects…) or a pattern (Pattern Style). 
Protection group
Excels allows users to lock individual cells (Locked) or prevent a formula from being displayed (Hidden). 
The contents of a cell are locked in order for spreadsheet users not to remove cells with important data either by accident or deliberately, see Chyba! Nenašiel sa žiaden zdroj odkazov..
Formatting numbers
Home tab (Number group) 
Data, and particularly the numbers you write in a cell, can be further formatted, for example, by displaying them with a specific number of decimal places or currency (€, $, £, etc.) or in percent, fractions or other forms. This is “number formatting”, which can be found either in the Home tab, in the Number group, or by right clicking the cell and selecting Format Cells. After selecting the specific format type shown in the table below, you will get the number formats below from numbers that are not formatted:
| Unformatted | Number format applied | Formatting type |
| 1 | 1 | General |
| 2 | 2.00 | Number |
| -3 | -3.00 € | Currency |
| -4 | - 4.00 € | Accounting |
| 5 | 08/31/2018 | Short Date (Date) |
| 6 | Friday, 31 August 2018 | Long Date |
| 7 | 0:00:00 | Time |
| 8 | 800,00% | Percentage |
| 0,9 | 8/9 | Fraction |
| 10 | 1,00E+01 | Scientific |
| 11 | 11 | Text |
| 97411 | 974 11 | Special |
| 2.897 | 2,9 ft | Custom |
Note: The difference between Accounting and Currency is how values are lined up and in the minus sign. Negative numbers in Currency can be denoted in red (for example, in the case of losses). Special provides preset number formats, such as ZIP codes or telephone numbers. When using Custom, you have to define a format mask, for example 0.0" ft” will provide you with the units in feet rounded to one decimal place (if you enter 2.897, it will show you 2.9 ft). If you have inserted a date in a cell and you want to display the name of the day, then click on mm/dd/yyyy, dddd (so if you enter 08/31/2018, it will show 08/31/2018, Friday). To return a cell back to no particular format, select General.
Formatting columns, rows and sheets
Home > Format
Often you need to change the width of a column or height of a row, or to hide a column or row (in order to hide columns with auxiliary calculations). In practice, the right button is frequently used to find the corresponding options in the popup menu, yet it is officially found in the Format command, which appears in the Home tab. 
The following options are available: Row Height – row height in points. You can change this either manually or by specifying the values in points. Note: 1 point equals approximately 1/72 inch or 0.035 centimeters Autofit Row Height – automatically adjusts the row height based on the largest font size in the row Column Width – column width in points Autofit Column Height – automatically adjusts the column height according to the longest text in the column Default Width… - sets the standard column width (in points) and affects all columns immediately. Visibility Hide & Unhide – unhides and hides column(s), row(s) and the sheet Organize Sheets Rename Sheet – renames a sheet Move or Copy Sheet… - moves or copies a sheet Tab Color – sets the color of the tab Protection Protect Sheet… – protects a sheet Lock Cell – locks a cell Format Cells… – formats cells Note: Double click on the line separating the columns or rows to automatically set column width and row height, respectively 
. Note: You can format columns, rows and sheets by calling up the popup menu (right mouse button) and selecting the corresponding option.
Copying data
The procedure for copying cell contents is the same as copying text or other content:
- Mark the range of cells you wish to copy
- Use CTRL + C (hold down the CTRL key for as long as you want and press the C key once). Dashed lines will “blink” around the range of cells
- Click on the cell where you wish to insert the copied cell
- Use CTRL + V (hold down the CTRL key for as long as you want and press the V key once)
Note: This allows you to also copy the contents of cells to Microsoft Word (or another program). You can also use the right mouse button to copy or paste the contents to a new location by selecting Copy or Paste, respectively.
Pasting data
Home > Paste
Right click with the mouse and select Paste special... When copying data, sometimes it needs to be modified before it is inserted into another (or the same) location (e.g., you need to remove the cell formatting or have values without formulas). After copying the data (e.g. with CTRL + C) use Home > Paste or right click where you wish to insert it and look at the commands in the Paste special... group. 
The list below explains what specific possibilities for copied values (called sources) exist and how they are inserted into the spreadsheet (the inserted values are called targets).
- Paste. Target and source are the same (includes formulas). Everything is copied except column width and row height.
- Formulas. Target and source are the same, but with no formatting (includes formulas).
- Formulas & Number Formatting. Target and source are the same without formatting except for number formatting (includes formulas).
- Keep Source Formatting. Target retains source formatting.
- No Borders. Target displayed without borders.
- Keep Source Columns Widths. Target retains source column widths.
- Transpose. Target and source are the same, but the columns and rows are rotated.
- Values. Target contains only source values (does not include formulas).
- Values & Number Formatting. Target contains only source values, but number formatting is kept.
- Values & Source Formatting. Target contains only source values, but source formatting is kept.
- Formatting. Target contains only source formatting.
- Paste link. Target values linked to source.
- Picture. Source inserted as a picture.
- Linked picture. Source inserted as a picture with a link. Note: In practice, either Values or Values & Number Formatting… is used. In this application, the pasted data contains no formulas. This is useful when you want to remove copied cells, such as those linked to other cells. The clear disadvantage here is that you lose formulas and can no longer return to them.
Automatic copying and filling of data
After having filled a cell with a formula or data, Excel will usually need to fill also other cells that have been inserted, for example, in the column (or row) either under (or over) the cell or to the right (or left) of it. If at least one cell is filled and you need its contents to be “taken” to other cells, just move the mouse cursor to the right bottom of the cell that contains the formula or value (or which has only been formatted). The mouse cursor turns into a black cross, which you now need only to hold and drag with the left mouse button until the target cell has been reached. Consider the following table: 
After marking Cell A2 (with the value of 1), automatic copying allows you to fill the same value in the column’s other cells (below). As soon as you mark Cells A2 and A3, drag the cursor to get the other values in sequence (3, 4, 5 ...). Cell B2 contains a date, so just “drag” the cursor to fill the cells beneath it with sequential dates (including the weekend). Drag the cursor beneath Cell C2, which contains a formula (=A2*2), to fill the cells under the formulas heading referring to the applicable cell (A2 in this case changes to B2, which changes to C2 and so forth). Cell D2 contains the name of the day, which Excel fills with the next day in sequence. The same also applies to Column E, with relation to the months. Copying Cell F2 copies the same text to the other cells. Cell G2 contains only formatting (cell fill), so only the formatting is copied. Column H will be left aside for the moment, while moving on to Column I in order to demonstrate dynamic filling of values. Here, e-mails (in the form Táto e-mailová adresa je chránená pred spamovacími robotmi. Na jej zobrazenie potrebuješ mať nainštalovaný JavaScript. need to be generated from employee names (Column H). Excel just needs a column with created names (Column H) to fill the first cell (I2) in the form you wish to have (Táto e-mailová adresa je chránená pred spamovacími robotmi. Na jej zobrazenie potrebuješ mať nainštalovaný JavaScript. has been entered). This e-mail is then copied to the other cells. Note that Excel has left other paste options at the bottom right for copying (Auto Fill Options and the "+" pictogram). Now, just go to this menu and select Flash Fill. Excel automatically matches the same values. Note: Flash Fill requires a sample value, which can then be copied. Selecting Flash Fill adds the values in the left column, taking the pattern into account.
Absolute reference – working with a constant
In some cases, you will need to use a cell in the formula to be referenced once the formula has been copied (e.g. to other cells in the column), for instance, to calculate VAT on Revenue in Column C of the following table.
| A | B | C | D | E | |
| 1 | Name | Revenue | VAT on Revenue | VAT | |
| 2 | Peter | 100 | 14 | 14% | |
| 3 | Thomas | 200 | 28 | ||
| 4 | Arthur | 300 | 42 | ||
| 5 | George | 400 | 56 | ||
| 6 | Barbara | 400 | 56 |
For this reason, the formula below is used in Cell C2 =B2*E2 The correct result is obtained, but copying the formula to other cells in the column yields 0 because Excel has adjusted the formula in Cell C3 to: =B3*E3 The formula has been similarly adjusted in the other cells, yet Cell E2 still needs to be referenced in the formula so the cell has to be locked in the formula (by creating an absolute reference). So you return to Cell C2 and write the same formula, but do not confirm it yet: =B2*E2 Before you enter it, E2 has to be locked, so press the F4 key (found at the top of the keyboard). The formula will be changed to appear as follows: =B2*$E$2 Note: If you press F4 several times, the formula will look like this: =B2*E$2, =B2*$E2, =B2*E2 and again, locking the row, column, unlocking them and so forth. In this case, just leave the formula as =B2*E$2, because the row with that cell needs to be locked. Note: Instead of F4, “$” can be written into the formula.
Moving data
Follow these steps to move a cell’s contents:
- Mark the range of cells you wish to move
- Use CTRL + X (hold down the CTRL key for as long as you want and press the X key once). Dashed lines will “blink” around the range of cells
- Click on the cell where you want to move it
- Use CTRL + V (hold down the CTRL key for as long as you want and press the V key once)
Note: You can also use the right mouse button to move data, selecting Cut.
Adding a column and row
You can add a new column or row to a sheet with the Insert command in the Home tab, although in practice the right mouse button is used more often. Here right-click on the column where you want to insert a new column and then select Insert in the pop-up menu. A new column will be inserted to the left of the current column (moving the current column to the right). Follow the same procedure to add a row (the new row will be inserted above the current row). 
Deleting a column and row
Select Delete by right-clicking on the marked row or column and select Delete. Removing a row shifts the other rows upward, while removing a column shifts the columns leftward. 
Inserting, deleting, renaming and changing the color of a sheet
Inserting a new sheet – the simplest way is to insert a sheet is to click on the pictogram "+" located to the right of the rightmost sheet, or go to the Home tab and use the command Insert and then Insert Sheet. 
Deleting a sheet – right click on the sheet and select Delete. 
Renaming a sheet – right click on the sheet and select Rename; the sheet tab is expecting a new name, so rename the sheet tab and then press Enter. 
Copying a sheet – right click above the sheet and then select Move or Copy… In the Move or Copy window above, select the target workbook where you wish to copy the current worksheet. The arrow allows you to select another file (if you happen to have another workbook open) or a new book. Before sheet shows you all sheets in a workbook and allows you to mark one of them to decide where to put the sheet to be copied (before selecting it). Remember to tick Create a copy at the bottom of the window. 
Note: You can copy a sheet by dragging its “tab” while holding the CTRL key. Moving a sheet – right click above the sheet and then select Move or Copy… In the Move or Copy window, select the options as you would when copying a sheet and do not tick the Create and Copy option at the bottom of the window. Note: You can also move the sheet by dragging its "tab". Changing the color of a sheet – right click above the sheet, select Tab Color and choose a specific color.
Locking rows and columns in a table (Freeze Panes)
View > Freeze Panes
Panes are frozen in order to view the data in tables with a large number of rows and/or columns either by locking the first row (usually the table header) and/or the first column, or you can decide which row or column to lock. The command to freeze panes can be found in View > Freeze Panes. If the first row of the table is going to be locked, select Freeze Top Row. Analogically, select Freeze Top Column to lock the first column. If you wish to lock several rows and/or columns, select Freeze Panes. In this case, you have to first click on the cell from where the rows and columns will be locked. Freeze Panes "freezes" all rows above and columns to the left of the marked cell. 
Printing recurring titles
Page Layout > Print Titles
Print Titles is useful for printing tables with a large number of rows and/or columns, where a table header on each side needs to be printed. In the Page Layout tab, go to Print Titles. 
Then in the Page Setup window, highlight in the Rows to repeat at top: field the row(s) you wish to print on each page and/or in Columns to repeat at left: the columns you wish to repeat: 
Scaling
Page Layout > Scale to Fit or View > Page Break Preview
Sometimes while printing there are pages with a single column or a few lines printed unnecessarily. The Scale to Fit group in Page Layout allows Excel to automatically adjust the width or length of a page. For example, you can change Scale. Similar options can be found at File > Print > Settings. 
Another available option is to adjust page breaks (View > Page Break Preview). Excel switches the appearance to a mode where you "move" with blue lines. The dashed line is only displayed at the end of the page. 
Inserting headers and footers, page numbering
File > Print > Page Setup
Like Word, Excel can add important data (company name, address, ID, TIN, contact details, logo, etc.) to a sheet for repetition on each page. This is the paper “header”. Either one of two methods can be used in Excel: File > Print or the Page Layout command found in View. In this example the first method will be used, so go to File > Print and find Page Setup at the bottom right. 
Click on the Page Setup window and select the Header/Footer tab. Where appropriate, select either Customer Header… or Custom Footer…, depending on where you wish to edit. After you confirm, Excel will open either the Header or Footer window. 
You can then select the part of the document where to insert the text or contents from among the buttons in the center of the window. These are divided as follows:
- Format text. Formats text as needed.
- Insert Page Number. Inserts the page number.
- Insert Number of Page. Inserts the page number of the specific sheet. It can be combined, for example, with Page Number.
- Insert Date. Inserts today’s date.
- Insert Time. Inserts the current time.
- Insert File Path. Inserts the current path to the open workbook.
- Insert File Name. Inserts the name of the workbook currently open.
- Insert Sheet Name. Inserts the name of the sheet currently open.
- Insert Picture. Inserts the picture to be searched in the PC.
- Format Picture. Activated when a picture has been inserted. It allows you to format (resize, reorient or crop) a picture, where appropriate.
Inserting comments
Review > New Comment or Right mouse button > Insert Comment
Sometimes you need to insert a comment in a cell with an Important for a colleague (e.g., to explain what values to insert or add, or not to remove anything extra). Comments are inserted into the cell with Review > New Comment or by right-clicking and selecting Insert Comment in the pop-up window. Cells with comments display a pictogram in the form of a small red triangle. 
Editing comments
Edit comments by right-clicking the mouse and selecting Edit comment.
Deleting comments
Delete comments by right-clicking the mouse and selecting Delete comment.
Viewing comments
Comments can be viewed by right-clicking the mouse and selecting Show/Hide Comments.
Printing comments
File > Print > Page Setup, Sheet tab Comments are not automatically printed, so go to File > Print, where Page Setup can be found at the bottom of the page. Go to the Sheet tab and change it under Comments in At end of sheets (if you wish to print the comments on a separate sheet together with a description of where they are located) or in As displayed on sheet (comments will be printed at the same location as where they are). 
Setting margins
Page Layout > Margins or File > Print
You can see the margins on every printed page, sheets and documents. There are upper, lower, left and right margins. Margins are specified with the Margins command in the Page Layout tab (or with File > Print), with Excel offering a choice of margins. To customize your margins, go to Custom Margins ..., where the Page Setup window sets the margins, the distance of the header from the top of the page and the distance of the footer from the bottom of the page. 
Changing the page orientation
Page Setup > Orientation or File > Print Orientation in the Page Setup tab changes the orientation of the current sheet to portrait (default setting) or landscape.
Changing the page size
Page Setup > Size or File > Print
The Size tab formats the page size. In European countries the default format is A4. Note: Margin settings, page orientation and size can also be found in File > Print.
Data filter
Home > Sort & Filter or Data > Filter
Data filter allows you to display only data that meets your criteria (e.g., you wish to display products that have a high priority or were delivered in a particular month of the year). The filter criterion may be a specific value, range of values, or formatting (e.g., a specific color). Filter can be found in Home > Sort & Filter (or in Data > Filter). For example, consider the input table below: 
After marking the data in the table, activate Filter, which is located in Home > Sort & Filter. Each column in the table displays an arrow for selecting the necessary data. For instance, you wish the Importance column to display products whose priorities are Medium and High. Therefore, click on the arrow in the Importance column to activate the filter window, which lists the unique values found in the column in addition to specific options. These are automatically ticked, which means that they are displayed in the list. In this example, uncheck only Low (Medium and High will remain checked) and then confirm with OK. Important: If the list is too long, you can search for desired values in the Search field. You can cancel the filter at any time by clicking the funnel icon (instead of the arrow) in the column where the filter was activated and selecting Clear Filter From “...” (e.g., Importance in this example). If you choose a large number of criteria (e.g. Quantity over 100), it is preferable to select Number Filters and, in this particular example, select Greater Than… (see the picture on the next page). The same menu has several filtering criteria you can select, including Top 10 ..., Above Average, Below Average, or you can set up Custom Filter… 
Not only data can be filtered, but also colors (Filter by Color). Besides filtering, data and colors can also be sorted (Sort by Color).
Searching for data
Home > Find & Select > Find...
You can search in a sheet for text, a number, a date, or even a cell with a specific format either in the worksheet or in the entire workbook. The search box can found by selecting Find & Select > Find… in the Home tab or even simpler with CTRL + F. In the Find & Replace window, write the search word or phrase in the Find what field. Clicking on Find Next commands Excel to search for the keyword(s), while Find All displays all the results found. 
For words where you are not sure about using a character (like words with diacritical marks), you can use wildcards such as: ? (question mark) - any character when you are looking for words with diacritics Example: m?d can be “mud”, “mad”, “mid” or similar words * (asterisk) – any number of characters. Use it to search for words that contain the search word. Example: *own" can be “owner”, “owner’s” or even “town”, “shallowness”, “downloading” or other words with “own” in them. ~ (tilde) – use it to find text where the character is located. Example: A sheet has text with the character “*”. To find it, enter “~*”in the Find and Replace window of the search box; if you are searching for a question mark, enter “~?” and so forth.
Advanced searching
Activating the Options >> button in the Find and Replace window provides you with additional search options: Format… - search by cell format (appearance) Within – search either in a sheet or the entire workbook Search – search for data in rows or columns Look in – search in formulas, values or comments Match case – searches for case-sensitive data (if you are looking for the word “Father”, data with the word “father” will not be displayed) Match entire cell contents - displays cells containing only the search string, but not those where the string is part of another string. Example: You are searching for the word "town". If the words “downlight” and “countdown” are in the list to be searched, then Excel will mark both words. When you select Match entire cell contents, only the word “town” will be marked.
Replacing data
Home > Find & Select > Replace...
There will be cases when text will need to be replaced (such as due to improper grammar, misspelled words or updating of data). To avoid doing it manually, replace the text by using either Home > Find & Select > Replace... or CTRL + H. In the Find & Replace window, write the word you are replacing in the Find what field. In the Replace with field, type the word you wish to replace it with. If you need to replace word by word (but not all the words), then you have to use the Find Next (to find the next word) and Replace buttons (to replace the word). If you are using Find what to replace all occurrences of a word, click on the Replace All button. More options for replacing text can be found by clicking on the Options >> button. The options menu is similar to the advanced search menu; see Advanced searching. 
Tell me what you want to do
New versions of Excel no longer have routine “help” (the small question mark in the upper right). Instead, there is a blank field called Tell me what you want to do, where you write exactly what you need either to know or find in Excel (such as how to insert a pivot table, chart or something else, or you have no idea where a particular command is). 
For example, if you write “Insert chart” in Tell me what you want to do, Excel will provide you with a list of the chart types you can use by directly clicking on one. 
Get help on… describes the functionality you are searching as if you were reading an electronic manual. Apart from the text itself, the manual contains complete procedures along with images, hypertext, links and videos to make it understandable for ordinary Excel users. 
About the Author
For over 11 years Jan Zitniak has been a professional instructor concentrating on Microsoft Office. He holds an international Microsoft Excel - Office Excel ® 2010 Expert certificate and has written several books discussing Microsoft Office. The information mentioned in the book comes from practical experience he obtained at such large companies as T-Systems, BSH Bosch and Siemens, Veolia, Magneti Marelli, Coavis, National Bank of Slovakia and many others. More information about the book and author can be found at www.janzitniak.info. Any questions about the author and book I would be happy to answer, so please direct them to Táto e-mailová adresa je chránená pred spamovacími robotmi. Na jej zobrazenie potrebuješ mať nainštalovaný JavaScript..
For over 11 years Jan Zitniak has been a professional instructor concentrating on Microsoft Office. He holds an international Microsoft Excel – Office Excel ® 2010 Expert certificate and has written several books discussing Microsoft Office.
