Excel FAQ

There are many common issues in Excel. Most people have the same difficulties.

In this post I answer frequently asked questions, which you ask me. This post will develope.

I hope it will help.

How to change text in cells to only capital letters?

Capitalizing large amounts of text is quite easy in Excel. You need o use formula: =UPPER(A1) if your text is in A1 cell. It will turn your letters to capital.

 

I can't see every rows in spreadsheet.

Missing rows in Excel? There are a few possibilities:

  1. Try to turn off autofilter - Data / Filter / Autofilter
  2. Rows could be hidden. Go to Data / Filter / Show All

 

I can't paste nothing to Excel

Cannot paste to Excel? You must have few instances of Excel openned. You must work with only one instance of Excel.

 

Why right mouse click doesn't work in Excel?

There are a few ways to solve this problem:

  1. Go to C:\Documents and Settings\username\Application Data\Microsoft\Excel (username is name of your account) and erase Excel11.xlb file.
  2. Unregister and register Excel. [Unregister - Start - Run - Excel /unregsvr, Register - Excel /regsvr]
  3. Go to Task Manager, search for Verclsid and end task. Then go to C:\Windows and search for the same file and rename it ( for example Verclsidold). Then reopen Excel.
  4. This happens because the Excel Options key becomes corrupted. To fix, close Excel and open regedit. Go to HKCU\Software\Microsoft\12.0\Excel and rename the Options key to Options.old. Open Excel and a new Options key will be created with default settings, with right-click functionality restored. (12.0 is for Office 2007; if using 2010, go to 14.0 instead)
  5. Use For Each Next VBA formula. Open VBA editor (Alt + F11 keyboard shortcut), select Insert / Module and paste this code:
Sub Enable_Right_Click() Dim Cbar As CommandBar For Each Cbar In Application.CommandBars Cbar.Enabled = True Next End Sub

 

I can't edit my spreadsheet

Go to the file and right mouse click it. Then select Properties. Go down to attributes and if the box that says Read Only is selected.  Unclick it.

 

I see formula instead of result

Go to Ribbon / Formulas and in Formulas Auditing unclick Show Formulas button.

 

Why text in my cell is like ########?

The cell seems to be not wide enough to display the whole text. Grab the right border of cell and move it rightward to make the column wider.

 

Why I can't scroll down?

Maybe your panes are frozen? Check Freeze Panes post out. I hope this post helped to solve your problem. If not, post a commentary.

 

Why I can't merge cells? (merge and center is greyed out)

If the worksheet is protected then merge and center will be greyed out. Check if Unprotect Worksheet or Workbook buttons are available to select. The second reason is that you have come hidden cells in your spreadsheet.

 

Why I can't filter and sort?

You have 2 (or more) sheets grouped together (or multiple sheets are selected). You should right click on the tab name of the visible sheet and select Ungroup, at which point "Group" should disappear from the file name.

 

Why is freeze panes greyed out?

Switch Excel to Normal View. Go to Ribbon / View / Workbook Views and click Normal View.

 

How to sort horizontally?

When you go to Ribbon / Data / Sort there is an Options button where you can set the sort to be Left to Right.

 

I have The PivotTable field is not valid error

Check if you have any blanks in your range. Remove blanks and it will work fine.

 

Why is conditional formatting disabled?

It could be two possibilities:

  1. The sheet may be protected. Go to Ribbon /Tools / Protection / Unprotect sheet.
  2. The workbook may be shared. Go toRibbon / Tools / Share workbook and uncheck "Allow users..."

 

Why Insert and Developer button in the Ribbon are greyed out?

Go to: File / Options / Advanced / Display options for this workbook / For objects, show: All

 

Error message "Cannot Empty Clipboard" when dragging and dropping cell data

Close rdpclip.exe process. You can find it under the Process tab of the Task Manager in Windows.

 

What is the symbol of "Not equal to"?

Does not equal to is <> symbol

For example =A2<>3 is the formula where you check if A3 is not equal to 3.

=A1<>A2 is a logical function where you check if A1 cell is not equal to A2 cell. A1 and A2 can be even empty to check it. If they are both empty, Excel thinks that they are equal.

 

How to rotate table clockwise?

Select whole table and copy it (use keyboard shortcut CTRL + C)

Select the cell where you want to paste the table. Click right > Paste Special > Trancpose > OK.

 

How to display formulas in cells?

Use keyboard shortcut CTRL + ~

 

How to enter Euler's number into Excel?

Just use EXP function. Enter:

=exp(1) for e

=exp(2) for e^2

= exp(x) for e^x where x is a number of power you want to use

 

How to insert current date to your workbook?

There are two simple ways to insert current date in Excel:

  1. Use keyboard shortcut CTRL + ENTER + ; - you will insert current date without hour
  2. Use NOW function and type =NOW() formula - you will insert current date with hour, minute and seconds

 

How to insert degree (Fahrenheit) sign into worksheet?

There is a keyboard shortcut you can use: ALT + 0176. You can also copy and paste it from here: °

 

How to write two lines in one cell?

After typing the first line of text press ALT + ENTER keyboard combination.

How to delete hard spaces?

Use CTRL + F keyboard shortcut.

Go to Replace tab.

Put your cursor in Find What field.

Hold ALT and type 0160. You will see that a space was written (cursor moved).

Leave Replace With field blank.

Click Replace All button.

How to put a hard space into a cell?

Hold the Alt key and type 0160 from the Num keypad.

 

How to change a negative number to zero?

Use this formula: =IF(A1<0,0,A1)

 

How to add leading zeros to a cell value?

Thanks to this formula: =TEXT(A1,"0000000000") your number with change into leading zeros + your number. For example "355" will become "0000000355" (seven zeros and 3 digits what gives 10 digits).

 

How to Freeze the Top Row?

Go to The ribbon to the View tab. Click Freeze Panes and select the Freeze Top Row button.

 

How to get the highest number in a range?

Use this function: =MAX(range) eg. =MAX(A1:A10).

How to get the second highest number in a range?

Use this function: =LARGE(A1:A10,2). For third the highest use =LARGE(A1:A10,3).

How to get the highest number in a range?

Use this function: =MAX(range) eg. =MAX(A1:A10).

Why left mouse click works like hold?

When left-clicking it highlights cells and drags. It's because you are in Extended Selection mode. Just press F8 keyboard key to fix it.