Excel FAQ

There are many annoyances and troubles in Excel. Most of them you can easily fix. For example: You can’t scroll up and down. Or the formula bar is gone. Or you can’t group and ungroup rows and columns. The problem: Often it’s not very intuitive to find the right button or setting. Check our comprehensive FAQ!

Something missing in Excel?

Column and row headings missing in Excel
headings, letters, columns, rows, numbers, excel, header
If the column letters and row numbers are missing, go to View and click on “Headings”.
Sometimes the row and column headings are gone. So there is not written A, B, C and so on above the columns. And 1, 2, 3… is missing to identify the rows.In order to show (or hide) the row and column numbers and letters go to the View ribbon. Set the tick at “Formula Bar”. That’s it!
Formula bar is missing in Excel, how can I get it back?

formula, bar, missing, show, hide, excel
If the formula bar in Excel is missing, go to View and set the tick at “Formula Bar”.

Formula Bar in Excel gone?

If the formula bar in Excel is missing, you can easily show it again. Therefore, go to the View ribbon. Set the tick at “Formula Bar”. That’s it!

Column Headings: Column numbers instead of letters?

numbers, letters, excel, headings, change, options
Switch column headings from numbers to letters within the Excel options.

Excel provides the function to switch from column letters (=default) to numbers. In some cases it makes sense to use numbers instead of letters:

  • Working with VBA macros.
  • If you need to count the number of columns, e.g. when using VLOOKUP.

However, in most cases you want to use column letters. The problem: Formulas look much different if you use column letters. So how to switch from column numbers to letters?

  1. Go To File and click on Options.
  2. Select Formulas on the left hand side.
  3. Set the tick at “R1C1 reference style”.

Switching back from numbers to letters works the same way. Only in the last step, you have to remove the tick from “R1C1 reference style”.

 

Sheet tabs on the bottom of the Excel window missing. How can I get them back?

sheet, tab, excel, missing, gone, worksheet, names
Show the sheet tabs: Go to File, Options and set the tick of “Show sheet tabs” in the advanced tab.

You can’t see the worksheet names on the bottom of the Excel window? All sheet tabs are gone?

You can easily restore them:

  1. Go to File and click on Options.
  2. Navigate to “Advanced” on the left side.
  3. Scroll down until the group “Display options for this workbook:…”. Set the tick for “Show sheet tabs”.

That’s it, the worksheet names should be shown now.

Images, charts or other objects missing in Excel?

excel, options, show, all, drawing, image, missing, chart
Images, charts, drawings etc. missing? Click “For objects, show all” within the Excel options.

You had images, charts or other objects (e.g. drawing) in your Excel workbook, but now they are gone? There are basically two possibilities:

  1. You’ve (accidentally) deleted them?
  2. They are just not being displayed.

There is a hidden setting in Excel with says “For objects, show:”. Here you can select if you want to show all such objects. Objects are in general everything which is not inside cells. So everything from images, drawings, charts, drop-down lists, etc.

You can easily reactivate them. But it’s a little bit hidden:

  1. Go to File and click on Options.
  2. On the left side click on “Advanced”.
  3. Scroll down to the “Display options for this workbook:”. The last bullet point says “For objects, show:”. Set the tick at “All”.

That’s it 🙂


Hold on a second. Was this information helpful so far?
Connect with me on LinkedIn...
Professor Excel at LinkedIn

Boost your Excel skills: Learn the best Excel tricks and tutorials!


Something not working?

Buttons for inserting images or charts are greyed out in Excel?

You want to add a charts, image or drawing to your Excel workbook, but the buttons are greyed out like in the screenshot below?

excel, options, show, all, drawing, image, missing, chart
Images, charts, drawings etc. missing? Click “For objects, show all” within the Excel options.

There are two possible causes:

  1. You are inside a cell, typing something. In that case just press enter or escape on the keyboard to leave the cell.
  2. Objects are hidden within the Excel settings. In that case follow the steps below:

There is a setting in Excel with says “For objects, show:”. Here you can select if you want to show all objects, including images, charts, drop-down lists and so on. Objects are in general everything which is not inside cells.

  1. Go to File and click on Options.
  2. On the left side click on “Advanced”.
  3. Scroll down to the “Display options for this workbook:”. The last bullet point says “For objects, show:”. Set the tick at “All”.

Now the buttons shouldn’t be greyed out any longer.

Why I can’t choose rows and columns to repeat on print outs in Excel?

rows, column, repeat, print, printout, excel
Set the rows and columns to repeat by clicking on “Print Titles” within the “Page Layout” ribbon.

If your worksheet spreads over more than one printed page, you can define rows and columns to repeat. That way, the header row or column will be printed on each page. Large tables are much easier to read.

The problem: Often the fields for defining the rows and columns to repeat are greyed out. This occurs, when you just press Ctrl + p and go through the print preview.

Instead, you have to go through the “Page Layout” ribbon and set the print range there.

  1. Click on “Print Titles” within the “Page Setup” section on the “Page Layout” ribbon.
  2. Select the rows or column you want to be repeated.
  3. Confirm by clicking on “OK”.

For more information about printing Excel sheets please refer to our large print guide.

Why are values not updating or calculating in Excel?

wallpaper, desktop, excel, free, keep, calm, spreadsheet, f9
Keep calm and press F9 – green

Your Excel results are obviously wrong? Maybe you’ve just changed something but the results are still the same, Excel is not updating the results?

In such case it’s probable, that your workbook is set to manual calculation. That way, Excel recalculates your workbook only when you ask it to. You got the following options:

  • Press F9 on the keyboard. That way, all changed formulas and their depending cells will be recalculated.
  • In some rare cases, pressing F9 doesn’t solve the problem. Try pressing Ctrl + Alt + F9 on the keyboard. That way, all formulas – no matter if they changed or not – will be recalculated.
  • If the problem persists, please check your formulas… 🙂

For more information about the calculation modes in Excel, please refer to our detailed article.

Why I can’t add groupings to rows or columns in Excel?

You try to add groupings to selected rows or columns in Excel but it’s not working? Maybe even the corresponding buttons are grayed out on the data ribbon?

There are several possibilities:

  • Have you selected several worksheets at the same time? In such case, you can’t add groupings. You can only do it sheet by sheet.
  • Are you inside an Excel cell? If yes, you have to leave the cell first in order to add groupings.
  • The worksheet is protected? In that case you’d usually receive an error message.

 

 

check, outline, box, groups, grouping, groupings, not shown, not displayed
Go to the options and make sure that “Outline symbols are displayed”

There also might be another reason. Within the Excel options, you can choose if you want to display groups. So maybe, the grouping works, but is just not shown?

  1. Click on “File” and the “Options”.
  2. Navigate to “Advanced” on the right-hand side and scroll down.
  3. Make sure the box “Show outline symbols if an outline is applied”.
  4. Confirm by clicking on “OK”.

 

This article is part of our Excel FAQ.

I can’t scroll in Excel. Why?

You can’t scroll up and down or side wards?

Supposing you’ve checked your hardware (and confirmed that your mouse works correctly…), please check the following things:

  • Are there frozen panes? E.g. the first row or column? If yes, try to unfreeze them. Therefore, go to View and click on Freeze Panes and again on Freeze Panes. Can you scroll now?
    The reason: Maybe the frozen section is larger than the screen. Please refer to this article for more information about freezing panes.
  • Are there any (hidden) other windows or dialogue boxes open? For example a sort window, but shown somewhere else (e.g. on another screen)? In such case, the scrolling might be disabled as well.
  • Are you pressing the Shift key? In such case, scrolling might not work.
  • If your workbook zooms in or out, you are probably pressing the Ctrl key at the same time.

 

This article is part of our Excel FAQ.

 


Other questions

Grouping direction above or left of data in Excel?

grouping, group, outline, direction, left, right, above, below
You can change the direction of grouping in Excel.

Usually when you use groupings on rows, the small + or – sign is below the grouped data. The same for columns: The little + and – sign for showing or hiding grouped columns is located on the right side of the data. But sometimes it’s the other way around, how to change this?

Admittedly, it’s quite hidden:

  1. Go to the Data ribbon.
  2. Click on the tiny arrow in the bottom corner of the outline section.
  3. Set the ticks according to your desired direction, e.g. “Summary rows below detail”.

Do you want to do this for several worksheets at the same time? Take a look at our Excel add-in “Professor Excel Tools“. The layout manager provides a function to apply such layout to the complete workbook or just some worksheets.

Buttons and ribbons are very big and spacious in Excel. How can I make them smaller?

Your ribbon looks like this:

The buttons are large and very spacious. The problem when working on a normal computer: Less buttons can be shown. Don’t worry, that’s not a bug. It’s called “Touch mode”. It’s made for working on tablets when you use your fingers on the touch screen.

Unfortunately, the option for disabling this feature is not found within the normal menu structure. If you can find this  symbol in your Quick Access Toolbar you are lucky. Just click on it and switch to mouse mode (see no. 1 on the image on the right hand side).

If you don’t have this symbol in your Quick Access Toolbar, you got two options:

  1. Add it to the Quick Access Toolbar. Therefore, click on the small arrows like shown at number 2 on the image on the right side. Next click on “Touch/Mouse Mode”.
  2. If you got Excel 2016, you can also use the text input field saying “Tell me what you want to do”. Just type “Touch Mode” and you’ll see the button as in number 3 of the picture.
What version of Excel am I using?

excel, version, number, look, up
Look up the version number of your current Excel version.

You want to know which version of Excel you are running? E.g. Excel 2016 or 32/64-bit?

You can look it up with just a few clicks.

  1. Within Excel, click on “File”.
  2. In the lower part on the left side click on “Account”.
  3. There is a large button with a question mark on the right side. It says “About Excel”. When you click on it, a new window opens.
  4. The top of the new window shows the version number.

Your question not listed above? Ask it here!

Published
Categorized as General Tagged

Henrik Schiffner is a freelance business consultant and software developer. He lives and works in Hamburg, Germany. Besides being an Excel enthusiast he loves photography and sports.

Leave a comment

Your email address will not be published. Required fields are marked *