Excel Frequently Asked Questions: Knowledge Repository

Missing rows in Excel? There are a few possibilities:

1. 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. 1. Go to C:Documents and SettingsusernameApplication DataMicrosoftExcel (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 HKCUSoftwareMicrosoft12.0Excel 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. 1. The sheet may be protected. Go to Ribbon /Tools / Protection / Unprotect sheet.

1. 1. The workbook may be shared. Go to Ribbon / 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 > Transpose > 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. 1. Use keyboard shortcut CTRL + ENTER + ; – you will insert current date without hour

1. 1. 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 (how to insert a new line)?

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.

How to add particular sign at the beginning of text within a cell?

You may need to add some text to the beginning or the end of cells in some range.

Just use this formula: =”Some text: “&$A1 or =$A1&” some text”

The other way is to use CONCATENATE function: =CONCATENATE(“Some text: “,$A1)

My Excel won’t open

There are a few possibilities to solve the problem:

• • When you can’t open Excel simply by double clicking the icon open Excel from Windows Start menu instead. Click File – Options – Advanced – General. Uncheck Ignore other applications that use Dynamic Data Exchange (DDE)

• • Repair your Excel installation by re-installing Office

• • Reset Excel files associations in Control Panel – Programs – Default Programs – Set your default programs

• • Try turning off add-ins you installed in File – Options – Add-Ins

• • File – Options – Advanced – uncheck Disable hardware graphics acceleration

• • Try to install Office in another directory. There might be a problem with to long directory path on your hardware.

Copy takes long time

Try to clear conditional formatting rules:

• • Conditional Formatting – Clear Rules – Clear Rules form entire sheet

• • Conditional Formatting – Clear Rules – Clear Rules from selected cells

Formula is not calculating

Go to ribbon Formulas -> Calculation Options and check Automatic option.

How to insert multi line break?

Wanna more space in the cell? It’s possible in Excel.

1. 1. Double click the cell to get into it.

1. 1. Place your cursor in the proper place.

1. 1. Use ALT + Enter as many times as you need to insert multi line break.

How to wrap text in sheet?

Click the cell.

Click Wrap Text button.

Adjust the size of your cell. You many need to make it wider, higher or both. It’s up to you.