Get answers to Frequently Asked Questions about Excel worksheet functions and Excel formatting

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

=ROUND(A1/5,0)*5

=ROUND(A1/0.25,0)*0.25

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

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

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

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

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

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) **

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.

=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.

- Formula does not calculate correctly!
- Formula does not calculate at all!
- Formulas show, instead of results

There are three common causes for messages like this.

- 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.
- 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.
- 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

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

- 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.
- Perhaps the View Formulas option is turned on. To turn it off
in Excel 2010:
- On the Excel Ribbon, click the Formulas tab. In the Formula Auditing group, click Show Formulas
- 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)

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

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

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

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

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

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.

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

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.

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

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

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

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

- Before you type the character for which you want the overbar, change the font to Symbol.
- To create the bar, type the
**`**character (accent grave, may be above the Tab key) - 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.

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.

Last updated: April 21, 2021 3:25 PM

Contextures RSS Feed