Microsoft_Excel_2013_logo.svg

Obtain an Excel Sum Automatically!

Obtaining an Excel Sum (or Average, Count, Min, etc.) automatically is pretty easy. You no longer have to worry about entering in strings of cell references or clicking and dragging over the numbers you want to add. Using AutoSum is quick and easy and, it’s a great way to speed up simple Excel calculations. All you need is a column (or row) of numbers and you can get an Excel sum automatically.

Calculate an Excel Sum with AutoSum!

To start, you’ll need a column of numbers (a row of numbers works just as well), as in the picture below. Note that your column/row can be anywhere on your spreadsheet.

Excel Sum Column

Then simply click on the cell at the bottom of your column (or at the end of your row). This will be the cell where your Excel Sum will be calculated. Once you’ve selected the proper cell, simply navigate up to the Home Tab. The AutoSum function is located on the far right-hand side of the Ribbon.

Excel Sum AutoSum

Simply click the AutoSum button, and your screen will look like this.

Excel Sum Final

Excel automatically determines the cell range for you by including all of the adjacent cells directly above the cell you clicked on. It also gives you a visual representation of what the formula will include by highlighting the included cells with a dotted line. Simply press the Enter key to accept the formula and calculate the sum.

Excel Sum Calculation

And you’re done! Not that if you want something other than an Excel Sum, simply click the drop down arrow next to the AutoSum button. Some of the more common choices include Average, Count, Min, Max, but you can use this technique with many more formulas.

Get even more free tips and tricks when you signup for our free email newsletter. You’ll receive free Excel tips and tricks directly in your inbox and also receive exclusive discounts to our upcoming training classes.

Microsoft_Excel_2013_logo.svg

Excel TRIM, SUBSTITUTE, and Removing Spaces

The Excel TRIM function makes removing spaces from the ends of words very easy. In fact, it is frequently necessary to add or remove spaces from strings of text in Excel. Using the Excel TRIM function or the SUBSTITUTE function makes adding (and especially removing) these spaces very easy to do. If you know how to use both of these functions, you will never have to go through a spreadsheet and manually remove the extra spaces in cells ever again!

The Excel TRIM Function

Excel TrimThe Excel TRIM function is very simple to use. The syntax is simply =TRIM(Cell). When you use TRIM, it will remove all of the extra spaces on either side of a string of text in a cell. So, if you have some data in a cell that is preceded by a space or followed by a space, you can use TRIM to remove those spaces quickly. However, note that TRIM is somewhat limited, in that it will not remove spaces that occur between words. So, if your string contains a series of words separated by spaces, TRIM will only remove the spaces that occur before the first word and after the last word.

The Excel SUBSTITUTE Function

The SUBSTITUTE function is similar to the TRIM function, but it is much more flexible. With SUBSTITUTE, you can remove spaces from anywhere within a string of text (including between different words, unlike TRIM). You can also use SUBSTITUTE to add/remove more than just spaces. You can use it to swap out letters, numbers, or even whole words! The syntax is also very simple. It is =SUBSTITUTE(Cell,Old-Text,New-Text). So, if you wanted to remove all of the spaces from cell A1, you would use =SUBSTITUTE(A1,” “,””). If you wanted to remove all of the “A”s from A1 and replace them with “B”s, you would change it to =SUBSTITUTE(A1,”A”,”B”). Note that capitalization counts here.

Using the Excel TRIM and SUBSTITUTE functions makes adding and removing spaces, letters, numbers, and words quick and easy. Used properly, these two functions can make cleaning up your data a breeze.

Learn even more free tips and tricks when you signup for our free email newsletter. You’ll receive free Excel tips and tricks directly in your inbox and also receive exclusive discounts to our upcoming training classes.

Microsoft_Excel_2013_logo.svg

Excel INDIRECT Formula – How and Why to Use It

The Excel INDIRECT formula is a very flexible and powerful formula that allows you to make your formula references dynamic. In its simplest form, the Excel INDIRECT formula allows you to put the reference for one cell inside another, and then obtain the data from the first cell by referencing the formula. So, if your formula reads =INDIRECT(A1) and the value of A1 is “B1”, then the formula will return the value of B1. In effect, the INDIRECT formula converts any string of text into a cell reference. Creating dynamic cell references is where this formula is most useful.

How to Use the Excel INDIRECT Formula

To see how the Excel INDIRECT formula works with cell references in practice, consider the example of a workbook with multiple tabs, with different (class) names, where each tab looks like this (with scores filled in for each question):

excel indirect example

If you wanted to create a master summary table (as in the example below), where each class is listed out on the left-hand side of the table and a summary of their scores is presented on the right, you could use the INDIRECT formula to accomplish this fairly easily.

excel indirect example 2

You can use the Excel INDIRECT formula to turn each of the class names in the master summary table into a tab and cell reference. This will allow you to pull in each of the values from the separate worksheets without a lot of cutting and pasting. To pull in the data for the first question, simply insert the formula, =INDIRECT(A2&”! B2″). The INDIRECT formula will then return the value in A2 (which is “Class1”) and merge that with the concatenation “! B2” (we’re using cell B2 because that is the cell that contains the score value for Question1). This results in the cell reference “Class1! B2”. So, Excel will pull the value from cell B2 on the Class1 sheet into the master summary table. Simply copy the formula down to the remaining rows to pull in the values for the remaining sheets. As you can see, using the Excel INDIRECT formula is a quick and easy way to create dynamic cell references that allow you to pull in data from multiple sheets without having to directly specify the sheet names in the formula.

Learn even more free tips and tricks when you signup for our free email newsletter. You’ll receive free Excel tips and tricks directly in your inbox and also receive exclusive discounts to our upcoming training classes.

Microsoft_Excel_2013_logo.svg

Excel Formulas Not Working? Decode the Code!

Excel Formulas Not Working? Not sure what that error message means? In the course of compiling an Excel spreadsheet, you are going to run into formula errors. In order to fix the errors with your formulas, you have to know what that error message is telling you. What follows is a list of the most commonly encountered error messages you will encounter in Excel. Simply bookmark this page and refer back to it the next time you run into an unfamiliar error message or you aren’t sure how to fix your formula.

Excel Formulas Not Working? 8 Most Common Formula Errors

#DIV/0!

This error occurs when you attempt to divide a number by 0. Since this is mathematically impossible, Excel returns this error.

#N/A

This error occurs when your formula references a value that does not exist. For example, if you attempt to use a VLOOKUP formula that looks like =VLOOKUP(“USA”,A:B,1,False), but “USA” does not appear anywhere in Column A, then your formula will return the #N/A error.

#REF!

This error occurs when your formula contains an invalid cell reference. This could occur because you either mistyped the cell reference (i.e., you reference cell 1A instead of A1), or because you referenced a Named Range that does not exist.

#VALUE!

This error occurs when one (or more) of the values referenced in your formula is of the wrong type. For example, if you attempt to add a cell with text in it to another cell (e.g. =A1+B1, where the value of A1=”USA”), you will receive this error.Excel formulas not working

#NAME?

This error occurs when Excel is unable to recognize a text reference within a formula. This can be either a formula name that Excel doesn’t recognize or a misspelled Named Range reference. The most common cause of this error is a misspelled formula name (as in “VLOKUP” instead of “VLOOKUP”).

#NUM!

This error occurs when your formula contains an invalid number. This could either be a number that does not exist (as in the square root of a negative number) or a number that exceeds Excel’s processing ability.

#NULL!

This error is fairly rare, but most often occurs when you reference improper range references (as in where you’re trying to refer to two non-intersecting ranges).

#####

This error occurs when a number is so long it cannot fit in the cell. Simply re-size the column width of the cell to resolve the error. You will also see this error if you make a date negative.

Once you know why your Excel formulas are not working, you can start figuring out how to fix them. Many times this involves a lot of trial and error (especially if you are working with a long, complicated formula). But once you know why your Excel formulas are not working, you will be well on your way to figuring out how to fix them.

Excel Formulas Not Working? Need Help With Excel?

To learn even more free tips and tricks, sign up for our free email newsletter! You’ll learn all kinds of new and useful Excel tips and also receive exclusive discounts on our upcoming training classes.