Other Tools available in Microsoft Excel
A number of other useful Excel tools are built into Microsoft Excel and hidden amongst the normal menu items. Some of them are invaluable. Below are some video clips of extremely useful tools built into Microsoft Excel (still in Excel 2003).
Learn how to group and ungroup rows and columns so that you can easily hide or unhide the rows and columns you are interested in.
The Data Autofilter allows you to investigate large quantities of information and filter out unnecessary rows. Extremely useful if you work with large quantities of data.
Workbook-level protection- You can apply protection to workbook elements and you can protect a workbook file from viewing and changes.
Excel is often used to link multiple spreadsheets. However, the risks associated with handling these links is often misunderstood. After reviewing the free material on How Errors Happen with regards External links, this will show you a feature in Excel which allows you to identify your external links, change the source if necessary and break links.
Due to the size of spreadsheets it would sometimes be useful to quickly highlight all cells exhibiting the same criteria. This features allows you to highlight cells that contain comments, constants or formulae, blanks, Objects (pictures, buttons, etc), cells in rows or columns that are different from their neighbors, precedents and dependants, last cell in a sheet, and cells with conditional formatting or data validation.
The find replace tool is more commonly used in MS Word. However, it is extremely useful in Excel and can be used to quickly make changes to a spreadsheet.
Change the format of cells depending on their contents and various other conditions that may be required.
Pivot tables allow you to report on vast sums of information in a quick and efficient manner. Probably Excels most valuable tool.
Excel Help File Description: A PivotTable report is an interactive table that quickly combines and compares large amounts of data. You can rotate its rows and columns to see different summaries of the source data, and you can display the details for areas of interest.
Learn how to sort data in Excel, including how to develop customized sort orders to meet your exact needs.
Learn how to use Excel’s Data Tables feature to automatically run multiple scenarios through a model.
Excel Help File Description: Data tables are part of a suite of commands sometimes called what-if analysis tools. A data table is a range of cells that shows how changing certain values in your formulas affects the results of the formulas. Data tables provide a shortcut for calculating multiple versions in one operation and a way to view and compare the results of all of the different variations together on your worksheet.
Learn how to use hyperlinks to navigate through a spreadsheet or to other electronic documents. Easier (and safer) than using visual basic and macros to set up the navigation.
Excel Help File Description: Creates a shortcut or jump that opens a document stored on a network server, an intranet, or the Internet. When you click the cell that contains the HYPERLINK function, Microsoft Excel opens the file stored at link location.
Learn how to split and freeze your window so that you can view more than one section of a spreadsheet at the same time. Also learn how to arrange the screen so that you can see two different sheets at the same time.
The Paste Special command has a number of useful tools built into it. Besides allowing you to Paste values, it also allows you to transpose lists (columns to rows or rows to columns), multiply, add, skip blanks and paste links.
Adding Protection to a spreadsheet
Learn how to protect cells from accidental or deliberate changes or deletion.
Excel Help File Description: Microsoft Excel provides several layers of protection to control who can access and change your Excel data:
Worksheet protection- You can protect elements on a worksheet — such as cells with formulas — from all user access, or you can grant individual users access to the ranges you specify.
Workbook-level protection- You can apply protection to workbook elements and you can protect a workbook file from viewing and changes.
Want to learn more about Microsoft Excel? If you prefer attending a course and live in South Africa look at the Johannesburg MS Excel 3 Day Advanced Course or the Cape Town MS Excel 3 Day Advanced training course. If you prefer online learning or live outside South Africa, look at our online MS Excel training courses.
