Home > Formulas > Sum > FAQs Excel Functions and Formatting FAQ Get answers to Frequently Asked Questions about Excel worksheet functions and Excel formatting

1. Functions

1. Round a value to the nearest increment of 5
2. Find value in another sheet and return data from lookup table
3. Add the largest/smallest 5 entries
4. Pick 20 random items from a list
5. Sum visible cells only
6. Sum numbers between 5 and 10
7. Formula result is #NAME
8. Formula to get sheet name

Is there a formula that will round a value to the nearest increment of 5, or to the nearest quarter?

=ROUND(A1/5,0)*5

=ROUND(A1/0.25,0)*0.25

I am looking for a formula that will look at an adjacent column then find that value in another sheet and return the data from an adjacent column.

=VLOOKUP(A2,Sheet2!A2:B100,2,FALSE)

For more information and examples, see Excel -- Worksheet Functions -- VLookup

I want to add the largest/smallest 5 entries in A column.

=SUM(LARGE(A:A,{1,2,3,4,5}))

How do I pick 20 random items from a list of 100?

1. Enter the items down A1:A100.
2. In B1:B100 enter formula =RAND().
3. Sort the list by B column; top 20 rows is your selection.
4. Press F9 for new B numbers
5. Repeat for a new selection.

Is it possible to write a SUMIF worksheet formula to sum visible cells only?

If the cells are hidden as a result of AutoFiltering, you can use the SUBTOTAL function, with 9 as the first argument

I can sum all numbers >=10 with =SUMIF(A1:A20,">=10") . But how do I enter two criteria so I can sum numbers between 5 and 10?

That equals sum of all >=5 minus sum of all > 10:
=SUMIF(A1:A20,">=5")-SUMIF(A1:A20,">10")

Or you can use this method:
=SUMPRODUCT((A1:A20>=5)*(A1:A20<=10)*A1:A20)

Why does my function display #NAME?

The function may point to an add-in function that is not available to this Excel. Most frequently it's an Analysis ToolPak function; choose Tools > Add-Ins and check that there are checks against Analysis ToolPak.

Unlike Excel's built-in functions, Add-in functions do not translate themselves to regional language, so American add-in functions are by default unavailable on a Norwegian computer and vice versa.

Is there a way of returning the name of a sheet in a cell without using code?

=CELL("Filename",A1) returns the complete file path and sheet name

=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,255) will strip away everything but the sheet name.

Note: The file must be saved or the formula will not work.

2. Calculation

1. Formula does not calculate correctly!
2. Formula does not calculate at all!
3. Formulas show, instead of results

My formula does not calculate correctly!

There are three common causes for messages like this.

1. What is displayed in the cells is not what's really there. A cell can show 1 (no decimals format) but it may contain real or calculated values like 0.6 or 1.4. Add or multiply a bunch of those and you're surprised; Excel will calculate with real cell contents, NOT displayed contents. You may choose "precision as displayed" in the Tools > Options menu for a workaround, but make sure you know what you're doing.
2. A computer use binary numbers, and this has its limitations. It canot represent numbers like 1/10 exactly. Numbers like that are rounded to nearest 15 significant decimal digits, and Excel will be "wrong" around 15th-16th digit. Some operations suffer from this, and some Boolean tests (tests that may appear as 0.1=0.1) can return False because of this. "Normal work" like sensible-number budgeting and day-to-day math is usually not affected, but this may not be the tool for advanced science.
3. You are using Excel's statistical functions. Some of those are not good enough.
   LINEST() (also SLOPE(), INTERCEPT(), VAR(), STDEV(), LOGEST(),
   TREND(), FORECAST(), etc.) uses a numerically unstable algorithm.

You're right, Excel's wrong. Links to backgrounders and tools at https://www.mathtools.net/Excel/Statistics/index.html

My spreadsheet does not calculate at all!

Perhaps Calculation is set to Manual, alter this in Tools > Options menu.

The formulas are showing, instead of the results!

1. The cell may be formatted as Text. To change it, choose Format>Cells, and on the Number tab, choose a category other than Text, e.g. General or Number.
2. Perhaps the View Formulas option is turned on. To turn it off in Excel 2010:
   1. On the Excel Ribbon, click the Formulas tab. In the Formula Auditing group, click Show Formulas

      

   2. OR, in Excel 2003, choose Tools>Options. On the View tab, remove the check mark from Formulas.

      

Tip: The keyboard shortcut to show or hide the formulas is Ctrl + `  (accent grave, may be above the Tab key on the keyboard)

3. Working with Tables and Lists

1. Change data from rows to columns
2. In Data>Subtotals copy the totals only.
3. AutoFilter drop down does not show all items
4. Split first and last names.
5. Make a drop down list in a cell

Data stored in rows, change to columns

In Excel 2007, and later versions:

1. Select the data, and copy it.
2. Select the cell where you want to paste the data
3. On the Ribbon's Home tab, click Paste, and click Transpose.

Watch this short video to see how to Transpose data in Excel 2007.

In Excel 2003, and earlier versions:

1. Select the data, and copy it.
2. Select the cell where you want to paste the data
3. Choose Edit>Paste Special
4. Check the Transpose option, click OK

Using Data>Subtotals, I would like to create a table that has just these subtotals, not the hidden detail rows.

1. Once you've applied the Subtotal, collapse the outline so that only the rows you want to copy are visible.
2. Select the cells.
3. Choose Edit>Go To, click the Special button
4. Select 'Visible Cells Only', click OK
5. Click the Copy button
6. Go to another sheet, and paste

When I use AutoFilter I don't see all the items in the drop down list. Why not?

In older versions of Excel, an AutoFilter dropdown list will only show 1000 entries. You could add a new column, and use a formula to split the list into 2 or three groups, e.g.:
     =IF(LEFT(C2,1)<"N","A-M","N-Z")
Filter on this column first, then by the intended criteria.
Another option is to choose Custom from the drop-down list, and type the criteria.

How to split full name into separate cells.

Use Data>Text to Columns and specify the comma as a delimiter.

I want to fix a cell so the user can only choose from a list, for example DHL, FEDEX or UPS.

You can use Data Validation to create a drop down list in the cell. For a short list, you can type the options in the Source box, as shown below.

1. Select the cell(s) where you want the list to appear, then choose Data>Validation.
2. In the Allow dropdown, select List.
3. In the Source textbox, enter "DHL,FEDEX,UPS" (without the quotes)

   

4. Formatting

1. Make cell change colour, based on value in another cell
2. Format a cell to blink or flash? 
3. Add a bar above a character, e.g.

Can a formula that do this -- =IF(D25 does not equal E25 then D25 font will turn red)?

Try Conditional Formatting:

1. Select cell D25.
2. Choose Format>Conditional Formatting.
3. Set Condition 1 - Cell Value Is, not equal to, click on E25 on the worksheet.
4. Click on Format, select the Font tab. Under Colour, choose red.
5. Click OK, OK.

Can I format a cell to blink or flash when a condition is met?

No. Use conditional formatting to change the cell colour instead.

How can I add a bar above a character?

1. Before you type the character for which you want the overbar, change the font to Symbol.
2. To create the bar, type the ` character (accent grave, may be above the Tab key)
3. Then, stay in Symbol font, or switch to a different font, and type the character that has the overbar.

Note: This technique looks better on-screen in some font sizes than in others, but all should look okay when printed.

5. International Issues

1. Translate Functions

Translate Functions

These functions are in English, and you can not enter them in a Swedish Excel as is. Run this macro:

Sub EnterEnglishFunction()
  ActiveCell.Formula = InputBox("English function:") 
End Sub

paste the function in and OK, and in most cases it translates.