This course has been created to support students who are new to Microsoft Office products and in this particular case new to Microsoft Excel.
If you are aiming to use your computer to do calculations and deliver reports then this is the right place.
I have been working with Microsoft Excel since 1990. I saw all the big evolution of this amazing tool and so far I consider Excel as the most complete tool to use for any electronic document from a normal preparation of spreadsheet and report to the delivery of a full management document and to the delivery of professional presentations to an audience.
In this course you will learn how to manage the main functionalities of the tool so that you will know where to find what you need and what you can use for your scopes.
You will understand why and how to use Microsoft Excel, how an an Excel file is structured and how to achieve results using tools available in the ribbons, how to use an Excel file, how to input data and content, how to format your spreadsheets and how to calculate your file results using basic formulas and calculations.
At the end you will be able to generate a report where data from different worksheets are linked together in formulas and the final result is displayed on another summary worksheet. We will also format the data as table and add charts to view the data graphically.
Why and how to use Microsoft Excel is the scope of this course.
In this video you have an overview of what you are going to learn in this course and how the course is structured.
Microsoft Excel is the tool to use any time you need to report content which information has to be elaborated using formulas and calculations.
Any time you need to display data in numeric format and you need to display tables and graphs then you need to use Microsoft Excel.
Ribbons are the modern way to organize and display tools and functionalities to use in Microsoft Office products.
Instead of windows menu Microsoft Office products use ribbons.
In this video you have an overview of the ribbons used in Microsoft Excel.
The Quick Access Toolbar is a toolbox located above the Ribbon in Excel
It contains some of the most used functionalities: Save, Undo and Redo.
If you use an Excel command frequently, you can add it to the Quick Access Toolbar. Or if you do not use you can remove it
You can even add commands to the Quick Access Toolbar that are not in the ribbon.
In this video we will see how to undo or redo an action/command in Excel using the Quick Access toolbar
In Excel you can open and work on more files or workbooks simultaneously.
You can even reference two or more workbooks, e.g. by referencing to cells in different workbooks into a cell or range or formula into another one.
The Quick Access toolbar can also be used to work with Add-ins.
You can add and manage add-ins in this toolbox.
An add-in is software that adds new features into Microsoft Excel.
They are used to automate processes in order to avoid errors and do repetitious work shorter than if done manually.
An Excel file is a Workbook which is composed of one or more sheets.
By default the sheets of a workbook are Worksheets or spreadsheets, but you can add also other components e.g. macros, charts and other pre-built Excel spreadsheets or templates.
The Worksheet is the spreadsheet in an Excel file. More than one worksheet compose an Excel workbook.
One Worksheet is composed of cells. Cells are the minimum elements of the spreadsheet. They are identified by the row number and column number or identification. For example A1 means cell in column A and row 1.
In fact in Excel cells are identified by letters and rows by numbers.
You can also define ranges which are a set of data. An example of range is A2:D5, which means the set of cell starting from cell A2 (second column and second row) to cell D5 (4th column and 5th row).
A2:D5 is also known as the address of the range.
A1 is the address of cell at column 1 and row 1.
In the Info section in the Excel Application there are several options.
For each Excel workbook or file the options have specific values.
When you create a new file the values of these options are the default ones but the user can change them.
Each Worksheet in an Excel Workbook is identified by the tab at the bottom of the application.
The tab displays the name of the worksheet. By default the name if e.g. Sheet1 for the Worksheet number 1. You can also change the name of the worksheet: right click on the name, click on "Rename" in the popup menu and then edit the name (paying attention the updated name is not already in use for another worksheet in the same workbook).
You can also delete one worksheet. You need to select the tab, right click with the mouse, click on "delete" on the popup menu.
You can also assign a color to the tab of a worksheet. You need to select the tab, right click with the mouse, click on "Tab color" on the popup menu and select one color from the color palette on the right.
Colors are useful if you want to distinguish tabs from one another.
In an Excel cell you can input a text which can be a sentence or a paragraph.
If you do not do any formatting a text will be displayed in one line and this is not good of course.
In order to apply a multi-line effect to a paragraph in an Excel cell you need to use the "Wrap text" function or to break each line using the combination of commands ALT+ENTER.
Ranges and cells can be resized by dragging the line of the row or column of the cells you want to enlarge or reduce in size.
You can also automatically resize a column or row by double-clicking on the line corresponding to the row or the column you want to
change the font name and font size of text into cells
change the font style: set a font to bold, italics, underline, double underlined and combine different styles
You can change the color of the text in an Excel. This can be done for the entire text/paragraph or for part of it.
You can also change the color of a cell, also known as background color.
You can set the border to a cell or to a range of cells.
You need to select the cell or the range of cells and then in the Home ribbon, in the Font group you can select the grid icon which will display a set of possible grids to apply.
You can also remove the borders by clicking on the relative icon in the icon group.
You can also customize the borders of a cell or range, e.g. set different border style for one or more borders of the range. You need to select the range, right-click with the mouse and select "Format cells...". In the border tab of the popup window you can define the border style for each border of the selected cells.
Access the inline format cell menu and functions
Change the font styles (more options)
Change the border styles and colors
Change the background color and pattern
To set the number format in one or more cells, you have to select the cells and click on the appropriate Number format in the Home ribbon in the Number group.
By applying different formats to numbers, you can change the appearance of a number without changing the number itself. A number format does not affect the actual cell value that Excel uses to perform calculations.
The actual value is displayed in the formula bar.
To see all available number formats, click the Dialog Box Launcher next to the Number group in the Home ribbon.
The following list provides a summary of the number formats that are available
The default number format that Excel applies when you type a number. For the most part, numbers that are formatted with the General format are displayed just the way you type them. However, if the cell is not wide enough to show the entire number, the General format rounds the numbers with decimals. The General number format also uses scientific (exponential) notation for large numbers (12 or more digits).
Used for the general display of numbers. You can specify the number of decimal places that you want to use, whether you want to use a thousands separator, and how you want to display negative numbers.
Used for general monetary values and displays the default currency symbol with numbers. You can specify the number of decimal places that you want to use, whether you want to use a thousands separator, and how you want to display negative numbers.
Also used for monetary values, but it aligns the currency symbols and decimal points of numbers in a column.
Displays date and time serial numbers as date values, according to the type and locale (location) that you specify. Date formats that begin with an asterisk (*) respond to changes in regional date and time settings that are specified in Control Panel. Formats without an asterisk are not affected by Control Panel settings.
Displays date and time serial numbers as time values, according to the type and locale (location) that you specify. Time formats that begin with an asterisk (*) respond to changes in regional date and time settings that are specified in Control Panel. Formats without an asterisk are not affected by Control Panel settings.
Multiplies the cell value by 100 and displays the result with a percent (%) symbol. You can specify the number of decimal places that you want to use.
Displays a number as a fraction, according to the type of fraction that you specify.
Displays a number in exponential notation, replacing part of the number with E+n, where E (which stands for Exponent) multiplies the preceding number by 10 to the nth power. For example, a 2-decimal Scientific format displays 12345678901 as 1.23E+10, which is 1.23 times 10 to the 10th power. You can specify the number of decimal places that you want to use.
Treats the content of a cell as text and displays the content exactly as you type it, even when you type numbers.
Displays a number as a postal code (ZIP Code), phone number, or Social Security number.
Allows you to modify a copy of an existing number format code. Use this format to create a custom number format that is added to the list of number format codes. You can add between 200 and 250 custom number formats, depending on the language version of Excel that is installed on your computer.
Align horizontally and vertically
text into cells can be displayed diagonally and vertically
As specified before you can also create a paragraph out of your text in a cell using the Wrap text function.
You can also display a text from right to left and from top to bottom and vice-versa.
To copy a cell (content and formats) you need to select a cell or a range and execute CTRL+c.
The Paste option pastes everything.
1. Select the origin cell, right click, and then click Copy (or press CTRL+c).
2. select cell destination cell, right click, and then click Paste under 'Paste Options:' (or press CTRL+v).
If you want to paste only the content or the format or the formula only then you need to do a change for step 2:
2. select cell destination cell, right click, and then click the relative icon (Values (V) or Formatting (R) or Formulas (F)) under 'Paste Options:' (or press CTRL+v).
You can also paste comments only, validation criteria only, use the source theme, all except borders, column widths, formulas and number formats, values and number formats. You can also use the Paste Special dialog box to perform quick operations, skip blanks and transpose data.
the Format Painter on the Home tab is the tool to quickly apply the same formatting, such as color, font style and size, and border style, to multiple pieces of text or graphics. The format painter lets you copy all of the formatting from one object and apply it to another one
It is basically a copying and pasting functionality for formatting.
As explained before, you can also paste the content of a cell into another one with the formats of ever paste the formula only.
For example. If you have a formula in a cell and you paste it as value in another cell, you will see only the final result in the destination cell and not the formula.
Vice-versa if you paste the formula and not the value, if you change the parameters of the formula in the destination cell, the final result will be different that that in the origin cell because it is recalculated.
In both cases the format of the destination cell are not pasted from the origin cell and will be the current ones for the destination cell.
In details the process for the 2 cases is:
The Values option pastes the result of the formula.
1. Select cell B5, right click, and then click Copy (or press CTRL + c).
2. Next, select cell D5, right click, and then click Values under 'Paste Options:'
Note: to quickly replace the formula in cell B5 with its own result, select cell B5, press F2 (to edit the formula) and press F9.
The Formulas option only pastes the formula.
1. Select cell B5, right click, and then click Copy (or press CTRL + c).
2. Next, select cell F5, right click, and then click Formulas under 'Paste Options:'
A cell, a range of cells or another object which has been added into an Excel worksheet can be copied and pasted anywhere else as a picture: in the same Worksheet or same Excel Workbook, or in another workbook or even in another Microsoft Office file (e.g. PowerPoint presentation or Word document).
Cells and ranges can be added and deleted from an Excel worksheet.
When you want to insert a cell, first of all you need to select the cell or range of cells above which you want to insert a new cell, right click with the mouse and click on Insert. On the popup menu you can choose if to insert cells below or above or even an entire row or column.
The same when you want to delete.First of all you need to select the cell or range of cells above which
you want to insert a new cell, right click with the mouse and click on Delete. On the popup menu you can choose if to shift cells left or up or even delete the entire row(s) or column(s) of the selected cell or range of cells.
To resize a cell you have to position the mouse pointer to the row heading bottom or top border or the left or right border of the column. If you want to increase the height of a cell you need to drag up the top border or down the bottom border of the row. To increase the width of a cell you need to drag the left or right border of the column heading.
To change the column width, do one of the following:
To use your mouse, rest the cursor on right side of the column boundary you want to move until it becomes a resize cursor , and then drag the boundary until the column is the width you want.
To change the width to a specific measurement, click a cell in the column that you want to resize. On the Layout tab, in the Cell Size group, click in the Table Column Width box, and then specify the options you want.
To make the columns in a table automatically fit the contents, click on your table. On the Layout tab, in the Cell Size group, click AutoFit, and then click AutoFit Contents.
To use the ruler, select a cell in the table, and then drag the markers on the ruler. If you want to see the exact measurement of the column on the ruler, hold down ALT as you drag the marker.
To change the row height, do one of the following:
To use your mouse, rest the pointer on the row boundary you want to move until it becomes a resize pointer , and then drag the boundary.
To set the row height to a specific measurement, click a cell in the row that you want to resize. On the Layout tab, in the Cell Size group, click in the Table Row Height box, and then specify the height you want.
To use the ruler, select a cell in the table, and then drag the markers on the ruler. If you want to see the exact measurement of row on the ruler, hold down ALT as you drag the marker.
Select the columns or rows you want to make the same size. You can press CTRL while you select to choose several sections that are not next to each other.
On the Layout tab, in the Cell Size group, click Distribute Columns Evenly or Distribute Rows Evenly .
To create a new Excel file you go to File tab and click on New.
You can also pre-set the number of worksheets in a Workbook in the Options, so every time a create a new Excel file, by default it will contain always the same number of worksheets.
In the File tab you have the functions to open an existing Excel file, save it or change the name of an existing one with the save as function. With the save as function you just create a copy of an existing file with a different name. SO you can preserve your original document and change the new copy.
An Excel file can also be shared on the Internet or on a local network to other users.
It can also be exported/printed in PDF format.
An Excel file can also be printed. You can print one worksheet, a part of a worksheet or even a set of worksheets or all the worksheets and components of a workbook. You can also print several copies of the selected set of worksheets.
Before printing an Excel document you can also setup the page layout: change the page orientation, the margins, the information in the header and the footer.
As in every Microsoft Office document you can insert images and objects into an Excel file. You go to the Insert ribbon and click on the picture icon.
You can then browse on your computer or accessible network and choose the image to insert in all image formats (JPG, PNG, GIF, BMP, ...).
Once on the worksheet, the Picture tools ribbon will be visible and you are able to format the picture. In details you have the following functionalities:
The same as for pictures you can do for any other object.
You can insert and format different default shapes or paste shapes from other sources.
You can group shapes and create new objects out of the grouping.
When you select an object or shape automatically the Format Tools ribbon is visible and you can format your objects as you can do for the images/pictures.
Another elements available in the Insert ribbon are the WordArts.
These are text which have special effects/formats.
These objects can be edited and formatted as the other objects and images.
With SmartArt graphic objects, you can quickly and easily construct fancy graphical lists and diagrams in your worksheet. SmartArt lists and diagrams are available in different configurations: organizational charts, various process, flow diagrams, etc.
You can also edit the objects and apply your own text with the predefined graphic shapes.
To choose and insert a SmartArt diagram, follow these steps:
On the Insert tab, click the SmartArt button in the Illustrations group.
The Choose a SmartArt Graphic dialog box appears.
Select a category in the navigation pane on the left followed by the list’s or diagram’s thumbnail in the center section.
The right pane describes the selected graphic.
Excel inserts the basic structure of the list or diagram into your worksheet along with a Text pane (with “Type your text here” on its title bar) to its immediate left and [Text] in the shapes in the diagram itself. Also, the SmartArt Tools Design tab appears on the Ribbon.
After you insert the basic SmartArt graphic, it’s time to add the text. Follow these steps:
If you don’t see the Text pane (with “Type your text here” on its title bar) to the left of the SmartArt graphic, click the Text Pane button on the SmartArt Tools Design tab.
If the Text pane was previously closed, Excel doesn’t automatically display it when you insert a new SmartArt graphic.
Type the text for the first object at the top of the Text pane.
Click in the first text box in the Text pane, if necessary.
Press the down arrow key.
The insertion point moves to the text box for the next object in the graphic.
Type the text for the next object in the Text pane.
Continue entering text in the Text pane.
If you need more lines when you get to the end of the list, press Enter.Adding text to a new SmartArt graphic.
When you’re finished entering text, click a cell in the worksheet outside the SmartArt graphic.
The Text pane closes and the text you typed appears in the SmartArt graphic.
Icons are similar to images and shapes. You can add them to an Excel worksheet by clicking on the relative thumbnail in the Insert ribbon.
Insert a screenshot in an Excel worksheet is the same process as when you insert and image or a shape.
In Excel you can take a screenshot. You just need to select in the Insert ribbon a screenshot by clicking on the screenshot thumbnail
Charts and diagrams can show trends, averages, high and low points, and more. Not only do they make your worksheets more visually appealing, they also serve a definite function. They make it easier for your intended audience to sort out and understand the information you are presenting to them. This is especially true when dealing with data.
There are many different kinds of charts available in MS Excel--pie charts, bar charts, line charts, even organizational charts. What's more, MS Excel provides you with several tools to help you create them.
To insert a chart you need to:
There are different types of chart.
In this course we will see the bar charts and the line charts.
Before inserting a chart you need to identify a set of data in a worksheet and select it.
The data in form of matrix will be the set of data series to plot on the chart.
You can also change the data in an Excel table which a chart refers to and automatically your chart will modify.
You can change the data series and also the set of data in a data series.
You can do this you use the drag function to change the data in a data series or to change the data series to plot.
You can also format the layout of a chart.
You can change the background color and the bar colors.
You can show/hide axis and their titles
You can hide/show the title of the chart
You can show/hide the grids and the labels of the points of each data series.
Before printing an Excel file it is important to setup the page layout.
The page layout is available in the Page Layout ribbon or in the Print section within the File tab.
There are different features to define in the page layout section:
You can set the margins of one or a group of worksheets to print.
After you select the worksheet(s), you need to do the following:
On the Page Layout tab, in the Page Setup group, click Margins.
Do one of the following:
To use predefined margins, click Normal, Wide, or Narrow.
Tip: If you previously used a custom margin setting, that setting is available as the predefined margin option Last Custom Setting.
To specify custom page margins, click Custom Margins and then, in the Top, Bottom, Left, and Right boxes, enter the margin sizes that you want.
To set header or footer margins, click Custom Margins, and then enter a new margin size in the Header or Footer box. Setting the header or footer margins changes the distance from the top edge of the paper to the header or from the bottom edge of the paper to the footer.
Note: The header and footer settings should be smaller than your top and bottom margin settings, and larger than or equal to the minimum printer margins.
To center the page horizontally or vertically, click Custom Margins and then, under Center on page, select the Horizontally or Vertically check box.
To preview the new margins, click File > Print. To adjust the margins in Print Preview, select the Show Margins box in the lower right corner of the preview window, and then drag the black margin handles on either side or at the top or bottom of the page.
Note Page margins that you define in a given worksheet are stored with that worksheet when you save the workbook. You can't change the default page margins for new workbooks.
When you decide to print an Excel document you can define which part of the document you want to print.
That means that you can print only certain parts of a worksheet.
To to this you need to set the Print Area.
To set the Print Area you need to:
On the worksheet, select the cells that you want to define as the print area.
Tip: To set multiple print areas, hold down the Ctrl key and click the areas you want to print. Each print area prints on its own page.
On the Page Layout tab, in the Page Setup group, click Print Area, and then click Set Print Area.
Note: The print area that you set is saved when you save the workbook.
To see all the print areas to make sure they’re the ones you want, click View > Page Break Preview in the Workbook Views group. When you save your workbook, the print area is saved too.
When you want to print a document and you realize that the printed document consists of more than one page you may decide to change the parts to be printed on each pay and keep some on the same page with others.
To do this you need to introduce page breaks to avoid that some areas are printed with the previous ones and you do not want them together but rather with the rest of the document.
When you print, page breaks appear automatically where content on a sheet "breaks" or continues to the next page. In normal view, they appear as dashed horizontal and vertical lines; in page layout view, they appear as empty spaces between the pages. You can adjust where automatic page breaks occur and add your own page breaks manually. You can also remove manual page breaks, but you can't remove automatic page breaks. However, you can adjust column widths or margins, or scale the sheet size to fit the content on fewer pages.
Click the worksheet that you want to print.
On the View tab, in the Workbook Views group, click Page Break Preview.
You can also click Page Break Preview on the status bar.
Do one of the following:
To insert a vertical page break, select the row below where you want to insert the page break.
To insert a horizontal page break, select the column to the right of where you want to insert the page break.
On the Page Layout tab, in the Page Setup group, click Breaks.
Click Insert Page Break.
You can also right-click the row or column below or to the right of where you want to insert the page break, and then click Insert Page Break.
If manual page breaks that you insert do not take effect, it may be that the Fit To scaling option has been selected on the Page tab of the Page Setup dialog box (Page Layout tab, Page Setup group, Dialog Box Launcher ). To use the manual page breaks, change the scaling to Adjust to instead.
In the Page Layout ribbon by clicking on the background thumbnail you select an image to set as background of the selected worksheet.
Define the gridline and heading options
Include or exclude gridlines to view or to print
Include the headlines of an Excel Worksheet to view or to print
Position object with the Bring to Front and Send to Back functions
Manage objects with the Selection pane
Align objects vertically and horizontally with respect to other objects or Excel ranges
I have 20+ years experience in IT project management, software development and software architecture.
I have led several teams of software developers as project manager, quality manager and as team leader in different companies and different countries in sectors e.g. telecommunication, research & development, travel business, Internet marketing and Internet business, consultancy and services.
I have been responsible to collect the requirements of projects, applications and processes, design the databases and specify the final objects.
During my career I have accumulated a lot of experiences dealing with processes of any kind and the need to define the tools to manage them and collect the information.
I have organized all my experiences in courses which are not only a list of functions and methods but contain also my practical experiences and considerations about how to cope with the different situations and which solutions to suggest.