Tracking Changes in MS Excel

Microsoft Excel

Training Resources

Below you will find useful information, including tips and tricks, how-to articles etc. Content is updated on a weekly basis. We also offer training on Microsoft Excel.

Understanding the Excel Interface

Understanding the Excel Interface

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

Starting Microsoft Excel

Starting Microsoft Excel

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

Opening Workbooks in Excel

Opening Workbooks in Excel

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

Entering Data into Excel

Entering Data into Excel

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

Adjusting Columns and Saving Files  in Excel

Adjusting Columns and Saving Files in Excel

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

Printing and Exiting from Excel

Printing and Exiting from Excel

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

Building Formulas in Excel

Building Formulas in Excel

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

Using Basic Functions in Microsoft Excel

Using Basic Functions in Microsoft Excel

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

Working with Selections in Excel

Working with Selections in Excel

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

Creating multiple views in Excel

Creating multiple views in Excel

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

Creating Workspaces in Excel

Creating Workspaces in Excel

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

Working with Multiple Worksheets and Workbooks

Working with Multiple Worksheets and Workbooks

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

Working with Multiple Workbooks cont.

Working with Multiple Workbooks cont.

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

Sharing Data across Worksheets and Workbooks

Sharing Data across Worksheets and Workbooks

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

Creating a  List in MS Excel

Creating a List in MS Excel

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

Maintaining a  List in MS Excel

Maintaining a List in MS Excel

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 Lists in Excel

Filtering Lists in Excel

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

Using Subtotals in an Excel List

Using Subtotals in an Excel List

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

Working with Permissions in Excel

Working with Permissions in Excel

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

Sharing Excel Workbooks

Sharing Excel Workbooks

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

Adding Digital Signatures in Excel

Adding Digital Signatures in Excel

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

Recording Macros in Excel

Recording Macros in Excel

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

Running a Macro in Excel

Running a Macro in Excel

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

Editing and Deleting Macros

Editing and Deleting Macros

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

Hiding and Unhiding Data

Hiding and Unhiding Data

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

Data Validation

Data Validation

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

Protecting Excel Worksheets

Protecting Excel Worksheets

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

Relative and Absolute Cell Reference

Relative and Absolute Cell Reference

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

Multiple Cell References

Multiple Cell References

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

Array Formulas in Excel

Array Formulas in Excel

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

Layer Group Graphic Objects

Layer Group Graphic Objects

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

Customize Workbooks in Excel

Customize Workbooks in Excel

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

Managing Excel Themes

Managing Excel Themes

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

Incoporating SmartArt into MS Excel

Incoporating SmartArt into MS Excel

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

Using Templates in Excel

Using Templates in Excel

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

Using IF, AND, OR Functions

Using IF, AND, OR Functions

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

Using the FV Function in Excel

Using the FV Function in Excel

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 Function

The PMT Function

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

VLOOKUPS and HLOOKUPS

VLOOKUPS and HLOOKUPS

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

Creating a PivotTable with PowerPivot Data

Creating a PivotTable with PowerPivot Data

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

Working with Tables in Excel

Working with Tables in Excel

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

Working with Slicers

Working with Slicers

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

Tracking Changes in MS Excel

Tracking Changes in MS Excel

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

Tracking Change Options

Tracking Change Options

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

More Resources