Microsoft Excel Tips

Since 1998

Microsoft Excel Tips

Business, Science and Engineering

Contact Us Newsletter

Interesting Microsoft Excel tips and problem solving.

Presented below is our Microsoft Excel tips that will save you time and effort in your everyday Excel activities.

How to Clean Excel Text Data with Formulas

Many times when text data is downloaded, it comes in corrupted. Bank and financial downloads are notorious for this, engineering and science data sometimes. The corruption generally comes in the form of extra spaces before, in between and after the text; non-printable ascii characters in the text; and unwanted characters in the text. If your text data includes these imperfections, your lookup and logic formulas; Excel filter tools; PivotTables and so forth will not interpret the data correctly. To clean your data, the new buzzword is "shape", insert a column next to your text data and use the worksheet functions CLEAN, TRIM and SUBSTITUTE to remove and/or replace unwanted characters from the text. The CLEAN function removes non-printable ASCII characters, the scourge of data processing. Non-printables are not generally seen and  the ones most encountered occupy the number set 0-31 on the ASCII table (lookup on internet). Just when you assume you do not have any of them, you do so be alert. The CLEAN function removes them. The TRIM function removes leading and trailing edge spaces from the text data and makes the spacing between words in the text data 1 space. You use that function if you have multi-word text and you want to keep the spacing between the words. The SUBSTITUTE function can be used to remove and replace unwanted characters in text data with new characters (if replacing alphabetical characters, case sensitive). The author likes to use SUBSTITUTE to remove all the blank spaces in text when required. You search for a space and replace it with an empty string which is two "" quotes put together. The formulas that use these functions are presented below in the two data sets below.

Cleaning Your Data with TRIM, CLEAN and SUBSTITUTE

Use the CLEAN, TRIM and SUBSTITUTE functions to clean your data. Insert new column next to text data to put formulas in.

When done cleaning the data, copy/paste it back over the original data using Copy/PasteSpecial/Values. Remember to delete the extra column that was made for the formulas. Also be sure to check out all the TEXT worksheet functions like LEFT, RIGHT, MID, SEARCH, and the overlooked TEXT function.

Copy/Paste/Special Data

Copy/paste the clean text column to the original text column and use PasteSpecial/Values.

How to Turn the Gridlines and Headers Off on a Worksheet

When designing dashboards and reports on an Excel worksheet, you want a nice, clean, paper like appearance to make your product look professional. The gridlines and headers on an Excel worksheet can be turned off to produce this appearance. To accomplish this task, select the worksheet whose headers and gridlines you want to remove, proceed to the Ribbon and select Page Layout. On the Page Layout tab, uncheck the Gridlines and Headings check boxes.

Gridlines and Headers Check Boxes

Uncheck the Gridlines and Headings Check Boxes Under Page Layout

The worksheet will now look like a sheet of paper. Be sure before you do this, to color the borders and backgrounds of the cells that you want to enhance on the worksheet. This can be done using the Format Cells dialog box (Ctrl+1) and the Home tab.

Freeze Top Row of Worksheet

Report Uses a Chart, ActiveX Control and Conditional Formatting

How to Debug and Error Check an Excel Nested Formula One Step at a Time

Microsoft Excel formulas involving nested calculations are generally very difficult to debug due to their confusing structure. All those ()'s. The rule of thumb is the inner calculations of a nested formula calculate first then the outer ones, but this calculation sequence is not always apparent especially when dealing with nested function structures that reach 3 or more levels. Microsoft Excel has a tool, however, that lets you evaluate a nested formula one part at a time. It is called the Evaluate Function tool. Using this tool, you can see the different parts of a nested formula calculate, in the order it calculates, which greatly aides in the debug process.

To use this tool, select the cell with the nested formula, proceed to the Ribbon and select Formula/Evaluate Formula (in the Formula Auditing group on the right-hand side). When the tool appears, the underlined calculation in the Evaluation box is the next calculation to be evaluated. When the Evaluate button is clicked, that part will calculate and display a result. Keep clicking Evaluate and watch the box and the underlined sections. Note that clicking the Step Into button just gives a more detailed account of how the underlined section calculates (when it is enabled). If clicking this button, be sure click Step Out to get back to the main screen. Overall a very useful tool when calculating nested formula structures.

Nested Formula

Simple nested formula to be evaluated, switches between two tables using IF, VLOOKUP and TRIM.

Evaluate Formula

Click on a cell containing a nested formula and select Formulas/Evaluate Formula to watch pieces calculate.

Evaluate Formula Dialog Box

Click Evaluate or Step Into (when enabled) and watch the underlined calculation in the Evaluation box calculate.

If you click on the Error Checking button in the Ribbon, it is the same basic tool as just discussed but will lock onto all cells containing errors in their formulas and will show you the part that errors out. Just click the Show Calculation Steps button to see the formula calculate. You will click the Next button to move to the next formula with an error. Very cool for large worksheets with thousands of rows and errors you might not know about. Good quick way to see the worksheet is calculating correctly. No cell selection necessary before using the tool. Note that if you click the Ignore Error button and wish to error check the formula again later, proceed to the Excel Options screen, generally File/Options, and on the Formula tab click Reset Ignored Errors to debug.

Error Checking

Click the Show Calculation Steps to watch the formula error in the Evaluation Formula tool. Click the Next button to move on to the next error.

How to Close All Excel Workbooks at Once

Short and simple tip. If you have 20 workbooks open and you want to close all of them at once, hold down the Shift Key and then click the close button in the upper right-hand corner of any Excel window. It will prompt to Save All if required. Do not let up on the Shift key until everything is closed.

Close all workbooks

Close an Excel Workbook.

How to Drag and Fill Using the Right Mouse Button

When dragging and filling down a worksheet, everybody knows to left mouse click hold on the bottom right-hand side dot (i.e. fill handle) of a range selection and drag. Depending upon the contents of the selection, Excel will fill a series of the values when possible or copy / paste down the drag selection. However next time, after selecting the range to drag, right mouse click hold over the fill handle and drag the selection. When you release the mouse, a short cut menu will appear prompting you with options. This will depend upon the contents of the cell. Pictured below, a single date in a cell, a serial date number, was dragged down the worksheet using the right mouse button. At the end of the drag and releasing the mouse button, options appear to ask you how you want to sequence the date. Very handy.

Group Worksheets

Right mouse click hold over fill handle and drag. Select from shortcut menu.

How to Turn Text into a Number in a Cell Formula Calculation

When performing formula calculations in Excel, many times the output of a formula part is a piece of text. For example when using the RIGHT worksheet function to extract text from the right-hand side of a string like =RIGHT("CX100",3), it will return "100" as an answer. Numbers as text can wreak havoc on things like PivotTables and other functions that might use the value like VLOOKUP, MATCH and so forth. The idea here is to develop a simple automated method to modify the values without having to manually do it as an extra step or use an extra function. These concepts are illustrated below in a PivotTable trying to read numbers output as text that are not modified.

Text as numbers in pivot table.

The answer to the problem is actually very simple. Excel formulas, not function calls, just basic formulas like =A1+A2 have the ability to coerce a number entered as text into a number if the text number is used in a math operation. For example, If cell A1 has "100" in it and cell A2 has 200 in it, then developing a formula in A3 like =A1+A2 will automatically flip the value in cell A1 to a number and add it. They call this data type coercion in computer programming and Excel has it built in, who knew. This concept is illustrated below in the simple formula that flips the value in cell A1 before using it.

Create your formula with an equal sign.

So to flip a text output in a formula without changing its value, simple multiply the output by 1 and you have your number back. It works on date text as well number text. You can test out different things. Using this method allows you to automatically rotate text to numbers without doing any other steps in Excel or using extra functions. This concept is illustrated below in a formula that takes a bad text date, removes its periods and flips its value to a serial number and also in a formula that returns the right 3 characters from a piece of text and flips it to a number.

Multiply text output by 1.

How to Create a Formula in Microsoft Excel

Microsoft Excel has the ability to perform calculations on a worksheet in addition to just storing data in cells. To perform a calculation on a worksheet, you enter a formula in a cell. A cell is an intersection between a worksheet row and column and are referred to as A1, B2,...etc.

To enter a formula, first open a workbook and select a worksheet. Next type the values in the cells the formula will use on the same worksheet you will type the formula (multi-worksheet formulas later tip). Just click on a cell, type, and press Enter. When entering the values, think like using a calculator, the numbers you punch into a calculator are the numbers you put on the worksheet. Also group the numbers together on the worksheet so users can easily find and type in them. Format the area if you can which is next week's tip. Type text to the left of the numbers so users know what they are typing. When typing in the numbers, dollar signs do not matter to Excel. They are called a format and can be changed at will, also next week's tip. These concepts are illustrated below.

Type your values for a formula.

After entering the values, select a cell to place the formula in. To enter a formula in the cell, press the = sign. A = sign always starts a formula in a cell. In this example, we are going to add the expenses so click on the Hotel expense with the mouse, type a +, click on the Car expense with the mouse, type a + and last click on the Flight expense and press Enter. Always press Enter to enter a formula, do not click off the cell with the mouse. These concepts are illustrated below.

Create your formula with an equal sign.

As you just noticed, you use cell references in a formula to read the cell values used in the formula. It is just simple substitution, where you see B2, visualize 520.75 and you have the concept. A mathematical operator (i.e. +, -,...) is used to separate the cell references in the formula and perform the math. You never end a formula with a math operator like + or -. Also, where the formula is placed on the worksheet is not important although you should place it where users can easily see it.

The real power behind using formulas in Excel is if you change a cell value, the formula recalculates. Formulas can be used to calculate everything from basic tasks to Mach 2 fighter jets. No matter how high tech the formula is, they all use the same basic principles shown here.

To edit a formula, double click on the cell, click in the cell, edit the cell references and operators and press Enter. You will notice the cells used in the formula will be color coded so you can easily figure them out.

Contact Us Newsletter
Need Help? Please call us at 1.805.498.7162

Microsoft Excel Products

- Excel Self-Study Manuals

Copyright © 2002-2020

EMAGENIT All Rights Reserved