Professor Excel

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!

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.

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

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.

 

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.

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.

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 🙂

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.

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