Below you will find useful information, including Excel spreadsheet tutorials, tips and tricks, how-to articles etc. The content of our Microsoft Excel learning resources is updated on a weekly basis. We also offer training on Microsoft Excel.
The Excel window is illustrated in Figure 1-4. The bulk of the screen is occupied by the worksheet window. This grid provides a convenient workspace where you can enter and manage your data. Surrounding the worksheet window are several command interfaces, each of which allows you to receive information about, or apply functions to, the data on the worksheet. Read more
To work with Excel effectively, you need to know several basic skills and concepts; how to start Excel from the Windows desktop, how to make the most of the Excel interface, how worksheets are used, and how to select cells and ranges. Read more
When you first start Excel, a blank default workbook is loaded. You can use this blank workbook to start a new worksheet. But what if you have already used (saved and closed) the default workbook? At this point you have a couple of options. Read more
The first step in creating a useful worksheet is entering data. By entering data, you are inputting the information that you want Excel to display, calculate, and store. Data can be entered into a cell or a range of cells. You can even set up a sequence of data and let Excel fill in the remainder of the sequence based on your first few entries. Read more
On occasion, you will discover that the text or values you have entered into a cell are not completely visible. This will occur when the number of characters entered exceeds the width of the column and when data appears in the cell to its right, as shown in column B of Figure 1-8. Read more
You may want to print your worksheet when you’ve finished working with it. This will give you a hard copy of your data to look over and to share with others. Best of all, you can get a quick printout of the active worksheet very easily. Read more
The backbone of Excel is its ability to perform calculations. There are two ways to set up calculations in Excel: using formulas or using functions. Formulas are mathematical expressions that you build yourself. Read more
The spot color effect is when everything on the screen is in black and white except for a certain color.
Read more
This tutorial introduces Excel functions, which are a little like templates for common formulas. There are many different types of functions. First, we will look at the SUM function. You will learn about using AutoComplete for entering formulas. We'll look at other basic common functions such as AVERAGE . Read more
Before you can enter data, you have to select a cell. Before you can change the data, you have to select it. In this section, you'll learn effective, easy techniques that enable you to select cells, ranges, and nonadjacent cells. With one click, you can even select your entire worksheet, which is definitely a timesaver when it comes to making global changes. Read more
In many cases, you might find it helpful to work with different sections of your worksheet at the same time. You might, for example, want to keep the labels in row 4 visible while you scroll down to look at information located in row 35. You do this by applying either split bars or freezing panes. Read more
You use a workspace when you need to save a configuration of open workbooks on your system. In other words, let's say you've opened two or three workbooks, have arranged them satisfactorily, and then discover that you're out of time. Instead of repeating all the arranging the next time you start Excel, you simply save the arrangement as a workspace. Read more
Inserting a new worksheet into a workbook is easy. Excel inserts a new worksheet before the currently selected worksheet. Method to insert a new worksheet: From the Insert menu, choose Worksheet Read more
Analyzing and consolidating large amounts of data is one of Excel’s strongest features. By combining several related worksheets into a single workbook, you can restructure your data and organize it more efficiently. By default, a new workbook contains three worksheets; however, a workbook can contain as many as 255 worksheets or as few as one worksheet. Read more
Sometimes you need to transfer data from one workbook to another, perhaps as a way to consolidate information about one client or product. In order to do this efficiently, you must open and view multiple workbooks. Read more
You can move and copy data between worksheets just as you do within a single worksheet. Instead of specifying the paste destination in the source worksheet, you specify the paste location in a second worksheet, called the destination worksheet, which can be in the same or another workbook. When you move or copy cells or ranges that contain formulas and functions, you must take into account whether or not they employ relative or absolute references. Read more
A list is a sequence of rows of related data. You can use lists whenever you need to organize large amounts of similar data, such as a database of names and addresses. You create a list in much the same way you create a worksheet. You enter information into a list by entering data into cells. Although you can change list elements after you have created a list, it is best to spend time planning your list before you begin entering data. Read more
Excel provides an easy-to-use tool called the data form to maintain lists. Hardened spreadsheet users generally prefer to edit data in lists in the worksheet cells. In other words, in-cell or in-line editing. But many Excel users, particularly those fairly new to Excel, like the Data Forms or simply ‘Form’ feature in Excel. Read more
Filtering can give you more control over your list, particularly if your list contains a large number of records. For example, suppose you operate a small grocery store and have a master inventory of all the items in the store. Your list would include everything from dairy products to fresh vegetables to cookies. What if you suddenly needed to know how many types of cheese were on the shelf? You could scroll through the entire list, counting the cheeses as you go, but it would make more sense to filter the list so that it displays only dairy products, or better yet, only cheeses. Read more
When working with a list, you often need to know the bottom line figures or totals. A list of sales records may include the names of all salespersons, the different products they have sold, and the units of each product sold. To get a better idea of each person’s performance by product, you can use the Subtotals command to get a subtotal for each product sold by each salesperson. Read more
You can edit any data that appears in an edit box in a data form. If record data appears with no edit box, then you cannot edit this data in the data form because the field contains a formula. Read more
The following tutorial introduces you to the Information tab on the Backstage View. You’ll learn about marking a workbook as final, which makes the workbook read-only. You’ll also learn about permissions - both encrypting the workbook with a password and restricting permissions. This tutorial explains how to protect both the current sheet and an entire workbook’s structure. Read more
To share a workbook, use the following procedure. Select the Review tab from the Ribbon. Select Share Workbook. In the Share Workbook dialog box, check the Allow changes by more than one user at the same time box. Read more
To add a digital signature to a workbook, use the following procedure. Select the File tab from the Ribbon to open the Backstage View. Select Protect Workbook. Select Add a Digital Signature. Excel may display an informational message. Select OK. In the Sign dialog box, select the Commitment Type from the drop down list. Read more
If you perform certain operations over and over in Excel, you can record a macro which stores all the steps. When you run the macro it will automatically perform each step automatically saving you a lot of manual repetitive work. Read more
Once you have recorded a macro, you can play back the keystrokes and command selections anywhere in the workbook. Many of the macros you record will run so quickly that they seem to work almost instantaneously. Read more
If you need to make simple changes to a macro, such as inserting text, deleting a command or applying a specific format to a cell, you can edit the macro. You edit a macro in the Visual Basic Editor, shown in Figure 2-6. The elements of the Visual Basic Editor are described in the table below. Read more
When you develop workbooks for others to use, it may be wise to restrict the access they have to certain cells, worksheets, or even the entire workbook. Some cells may contain formulas that you do not want changed, and certain workbooks may be confidential. You can hide columns and rows within a worksheet, as well as hide worksheets within a workbook. Read more
To protect against incorrect data entry, you can use data validation to restrict the type of data that may be entered into a cell. You can specify a list of the valid entries or limit the number of characters in an entry. To further assist in accurate data entry, you can have a data input message appear that informs you of the type of data to be entered in a particular cell. Read more
By default, all cells in a worksheet are designated as locked. You cannot prohibit changes to locked cells unless you protect the worksheet, after which none of the locked cells can be modified. If you want to be able to modify specific cells in a protected worksheet, you must unlock them before protecting the worksheet. When you modify any unlocked cells, the results in any protected cells that contain formulas dependent upon unlocked cells, will also be modified. Read more
A cell reference refers to a single cell or range of cells on a Excel worksheet. These cells can be referred to by Excel Formulas when calcuations are made. In this tutorial you will learn the differences between relative and absolute cell references. Each behaves differently when copied and filled to other cells. Relative references change when a formula is copied to another cell whereas Absolute references remain constant, no matter where they are copied. Read more
Excel is capable of completing complex calculations relatively quickly. Most of the time, your calculations in Excel will involve using multiple pieces of data for each calculation. In order to do this, you will need to be able to reference multiple cells at the same time. Read more
An array can be defined as any grouping of two or more adjacent cells. These cells form a square or rectangle. When a selection is defined as an array, operations called array formulas can be performed on every cell in the selection rather than on just a single cell. This is a powerful technique that can be used to enhance Excel formulas and functions. Read more
Once you have added graphical objects to a worksheet, it is important to know how to organize their positioning in relation to one another. Using layers, you can choose which object overlaps another, while grouping allows you to group multiple graphical objects together so that you can adjust their properties all at the same time. Over the course of this topic, you will learn all about layering and grouping graphical objects in Microsoft Excel 2016. Read more
Your workbooks can be customized in a number of different ways. Over the course of this topic, we will focus on customization through the addition of comments, hyperlinks, watermarks, and background pictures. Read more
Themes control most of the visual aspects of any workbook and choosing the right theme is important to how your data is displayed. Over the course of this topic you will learn about themes in Excel 2016 and how they can be both changed and customized. Read more
SmartArt combines text-based information with graphics to create a more appearance-driven look. Using Excel’s tools, you will be able to create great SmartArt that can be used for a variety of different purposes. Over the course of this topic, you will learn how to insert SmartArt into your workbooks, as well as customizing it after it has been inserted. Read more
Templates are an excellent resource that you can use to create workbooks much more quickly than having to create one from scratch. Over the course of this topic, you will learn more about templates and how to create them yourself. Read more
Logic operations play a big part in Excel’s functionality, especially the IF function. You can use this function to calculate different values depending on the evaluation of a condition. The structure of an IF function is as follows. Read more
Another commonly used financial function is the FV (future value) function. This function returns the future value of a series of periodic payments to an investment at a fixed interest rate. For example, if you put $5,000 a year into an investment that yields 3.5% annual interest, the FV function can tell you how much your investment will be worth after a given period. Read more
The PMT (payment) function is a financial function that is used to calculate loan payments based upon a constant interest rate. For example, if you take a loan of $10,000 at 6% annual interest over 4 years, you can use the PMT function to calculate what the monthly payment on the loan will be. Read more
Both the VLOOKUP and HLOOKUP functions are used to find specific item(s) in a table of data. The VLOOKUP function contains a number of arguments which are written out like this. Read more
In this article, we will delve a little deeper into how to create a PivotTable with PowerPivot Data in Microsoft Excel. If you see a security warning, click Enable Content. Ensure that the PowerPivot tab is displayed on the ribbon. If you do not see this tab, please complete the steps in the “Enabling PowerPivot” topic first... Read more
To facilitate data analysis and management, you have the ability to turn any range of cells into a table. You can have multiple tables per worksheet, and tables can be as large or small as the amount of data you want to work with. Read more
In this article, we will learn how to insert and use a slicer, rename a slicer and change slicer settings. Slicers allow you to quickly filter any data that is displayed within a PivotTable. Read more
When tracking is enabled, Excel will keep track of the changes that you or others who have access to your workbook have made. This includes data insertions, deletions, and more. While this feature is available in a few Microsoft Office products, Microsoft Excel is somewhat unique in that change tracking is available only in shared workbooks. While typically a shared workbook would be stored in a location where others who need to can access it, you can also track changes using a copy only you have access to. Read more
In this article we will look at the different options for Tracking Changes and how to stop tracking changes. As tracking changes has been integrated with the sharing capabilities of Excel 2013, you can find its customization options by first clicking Review → Share Workbook. Read more
In this article, we will learn how to insert comments, edit comments, navigate through comments Read more
In line with state protocals we have now fully resumed in person or classroom training.
Trainees still have the option of joining any of our classes remotely via Zoom webinar.
To attend any clasess remotely first add any class to your
cart and then click the small check box below the class. See screenshot below.
You will receive webinar login details for your training 2-3 working days before your class is due to commence.