Every once in a while, you might find Excel behaving in a bizarre or unexpected way. One example is when you accidentally trigger the scroll lock feature . Another example is when one or more formulas suddenly stops working. Instead of a result, you see only a formula, as in the screen below:
The VLOOKUP formula is correct, why no result?
This can be very confusing, and you might think you’ve somehow broken your spreadsheet. However, it’s likely a simple problem. With a little troubleshooting, you can get things working again. There are two main reasons you might see a formula instead of a result:
- You accidentally enabled Show Formulas
- Excel thinks your formula is text
I’ll walk through each case with some examples.
Show Formulas is enabled
Excel has a feature called Show Formulas that toggles the display of formula results and actual formulas. Show Formulas is meant to give you a quick way to see all formulas in a worksheet. However, if you accidentally trigger this mode, it can be quite disorienting. With Show Formulas enabled, columns are expanded, and every formula in a worksheet is displayed with no results anywhere in sight, as shown below.
Show Formulas disabled (normal mode)
Show Formulas enabled
To check if Show Formulas is turned on, visit the Formula tab in the ribbon and check the Show Formulas button:
Show Formulas enabled - just click to disable
The reason Show Formulas can be accidentally enabled is because it has a keyboard shortcut (Control +) that a user might accidentally type. Try typing Control + in a worksheet to see how it works. You should see formulas toggled on and off each time you use the shortcut.
Watch a video with over 20 formula tips
Show Formulas toggles the display of every formula in a worksheet. If you are having trouble with a single formula, the problem isn’t Show Formulas. Instead, Excel probably thinks the formula is text. Read on for more information.
Excel thinks your formula is text
If Excel thinks a formula is just text and not an actual formula, it will simply display the text without trying to evaluate it as a formula. There are several situations that might cause this behavior.
No equal sign
First, you may have forgotten the equal sign. All formulas in Excel must begin with an equal sign (=). If you leave this out, Excel will simply treat the formula as text:
Broken formula example - no equal sign (=)
Space before the equal sign
A subtle variation of this problem can occur if there is one or more spaces before the equal sign. A single space can be hard to spot, but it breaks the rule that all formulas must start with an equal sign, so it will break the formula as shown below:
Formula wrapped in quotes
Finally, make sure the formula is not wrapped in quotes. Sometimes, when people mention a formula online, they will use quotes, like this:
"=A1"
In Excel, quotes are used to signify a text value, so if you enter a formula like this, the formula will not be evaluated. You can see an example of this in the screen below:
Note: you are free to use quotes inside formulas. In this case, the formula above requires quotes around criteria.
In all of the examples above, just edit the formula so it begins with an equal sign and all should be well:
For reference, here is the working formula:
=SUMIFS(D5:D9,E5:E9,">30")
Cell format set to Text
Finally, every once in a while, you might see a formula that is well-formed in every way but somehow does not display a result. If you run into a formula like this, check to see if the cell format is set to Text.
If so, set the format to General (Control + ~), or another suitable number format. You may need to enter cell edit mode (click into the formula bar, or use F2, then enter) to get Excel to recognize the format change. Excel should then recognize the formula.
After setting the format to General, you may need to enter the formula again to make Excel evaluate it properly.
Tip - Save formula in progress as text
Although a broken formula is never fun, you can sometimes use the “formula as text problem” to your advantage, as a way to save work in progress on a tricky formula. Normally, if you try to enter a formula in an unfinished state, Excel will throw an error, stopping you from entering the formula. However, if you add a single quote before the equal sign Excel will treat the formula as text and let you enter it without complaint. The single quote reminds you that the formula has been intentionally converted to text:
Later, you can resume work on the formula again, starting where you left off. See #17 in this list for more info .
Excel Tables are one of the most interesting and useful features in Excel. If you need a range that expands to include new data, and if you want to refer to data by name instead of by address, Excel Tables are for you. This article provides an introduction and overview.
1. Creating a table is fast
You can create an Excel Table in less than 10 seconds. First, remove blank rows and make sure all columns have a unique name, then put the cursor anywhere in the data and use the keyboard shortcut Control + T. When you click OK, Excel will create the table.
2. Navigate directly to tables
Like named ranges , tables will appear in the namebox dropdown menu. Just click the menu, and select the table. Excel will navigate to the table, even if it’s on a different tab in a workbook.
3. Tables provide special shortcuts
When you convert regular data to an Excel Table, almost every shortcut you know works better. For example, you can select rows with shift + space, and columns with control + space. These shortcuts make selections that run precisely to the edge of the table, even when you can’t see the edge of the table. Watch the video below for a quick rundown.
Video: Shortcuts for Excel tables
4. Painless drag and drop
Tables make it much easier to rearrange data with drag and drop. After you’ve selected a table row or column, simply drag to a new location. Excel will quietly insert the selection at the new location, without complaining about overwriting data.
Note: you must select the entire row or column . For columns, that includes the header.
5. Table headers stay visible
One frustration when working with a large set of data is that table headers disappear as you scroll down the table. Tables solve this problem in a clever way. When column headers scroll off the top of the table, Excel silently replaces worksheet columns with table headers.
6. Tables expand automatically
When new rows or columns are added to an Excel Table, the table expands to enclose them. In a similar way, a table automatically contracts when rows or columns are deleted. When combined with structured references (see below) this gives you a dynamic range to use with formulas.
7. Totals without formulas
All tables can display an optional Total Row. The Total Row can be easily configured to perform operations like SUM and COUNT without entering a formula. When the table is filtered, these totals will automatically calculate on visible rows only. You can toggle the Total Row on and off with the shortcut control + shift + T.
8. Rename a table anytime
All tables are automatically assigned a generic name like Table1, Table2, etc. However, you can rename a table at any time. Select any cell in the table and enter a new name on the Table Tools menu.
9. Fill formulas automatically
Tables have a feature called calculated columns that makes entering and maintaining formulas easier and more accurate. When you enter a standard formula in a column, the formula is automatically copied throughout the column, with no need for copy and paste.
10. Change formulas automatically
The same feature also handles formula changes. If you make a change to the formula anywhere in a calculated column, the formula is updated throughout the entire column. In the screen below, the tax rate has been changed to 7% in one step.
11. Human-readable formulas
Tables use a special formula syntax to refer to parts of a table by name. This feature is called " structured references “. For example, to SUM a column called “Amount” in a table called “Orders”, you can use a formula like this:
=SUM(Orders[Amount])
12. Easy dynamic ranges
The single biggest benefit of tables is that they automatically expand as new data is added, creating a dynamic range . You can easily use this dynamic range in your formulas. For example, the table in the screen below is named “Properties”. The following formulas will always return correct values, even as data is added to the table:
=ROWS(Properties)
=MAX(Properties[Price])
=MIN(Properties[Price])
13. Enter structured references with the mouse
An easy way to enter structured references in formulas is to use the mouse to select part of the table. Excel will automatically enter the structured reference for you. In the screen below, the price column was selected after entering =MAX(
14. Enter structured references by typing
Another way to enter structured references is by typing. When you type the first few letters of a table in a formula, Excel will list matching table names below.
Use the arrow keys to select and the TAB key to confirm. To enter a column name, enter an opening square bracket ([) after the table name follow the same process - type a few letters, select with arrow keys, and use TAB to confirm.
Video: Introduction to Structured References and Tables
15. Check structured references with a formula
You can quickly check a structured reference with the formula bar. For example, the following formula will select data in the “Address” column in the “Properties” table shown above:
=Properties[Address]
And this formula will select the headers of the table:
=Properties[#Headers]
Video: How to query a table with formulas
Video: How to use SUMIFS with a table
16. Change table formatting with one click
All Excel tables have a style applied by default, but you can change this at any time. Select any cell in the table and use the Table Styles menu on the Table Tools tab of the ribbon. With one click, the table will inherit the new style.
17. Remove all formatting
Table formatting is not a requirement of Excel tables. To use a table without formatting, select the first style in the styles menu, which is called “None”.
<img loading=“lazy” src=“https://exceljet.net/sites/default/files/images/articles/inline/table%20styles%20menu%20none.png" onerror=“this.onerror=null;this.src=‘https://blogger.googleusercontent.com/img/a/AVvXsEhe7F7TRXHtjiKvHb5vS7DmnxvpHiDyoYyYvm1nHB3Qp2_w3BnM6A2eq4v7FYxCC9bfZt3a9vIMtAYEKUiaDQbHMg-ViyGmRIj39MLp0bGFfgfYw1Dc9q_H-T0wiTm3l0Uq42dETrN9eC8aGJ9_IORZsxST1AcLR7np1koOfcc7tnHa4S8Mwz_xD9d0=s16000';" alt=“The style called “None” will clear formatting - 28”>
Tip: you can use this style to remove all table formatting before converting a table back to a normal range.
18. Override local formatting
When you apply a table style, local formatting is preserved by default . However, you can optionally override local formatting if you want. Right-click any style and choose “Apply and Clear formatting”:
19. Set a default table style
You can right-click any style and choose “Set as Default”. New tables in the same workbook will now use the default you set.
Note: to set a default table style in new workbooks, create a custom start-up template as described in this article . In the template file, set the default table style of your choice.
20. Use a Table with a pivot table
When you use a table as the source for a pivot table, the pivot table will automatically stay up to date with changes in data. Watch the video below to see how this works.
Video: Use a table for your next pivot table
21. Use a table to create a dynamic chart
Tables are a great way to create dynamic charts. New data in the table will automatically appear in the chart, and charts will exclude filtered rows by default.
Video: How to build a simple dynamic chart
22. Add a slicer to a table
Although all tables get filter controls by default, you can also add a slicer to a table, to make it easy to filter data with large buttons. To add a slicer to a table, click the Insert Slicer button on the Design tab of the Table Tools menu.
The table below has a slicer for Department:
23. Get rid of a table
To get rid of a table, use the Convert to Range command on the Table Tools tab of the ribbon.
You might be surprised to see that converting a table back to a normal range doesn’t remove formatting. To remove table formatting, first apply the “None” table style, then use “Convert to Range”.
