RSS

Category Archives: MS Excel

Excel Cell Displays the Function Instead of its Calculated Value

Problem:  I inserted a column and entered some formula into it.  But after pressing enter, it was displaying the formula itself instead of its calculated values. Solution:

  • Highlight the column (in my case), row or cells; wherever the problem is.
  • Right click on selection.
  • Select ‘Format Cells…‘ option.
  • Click on ‘Number‘ tab, if not already on it.
  • Click on ‘General‘ option under ‘Category‘.
  • Click OK to close the dialog box.
  • Recalculate the sheet/cells.
 

Tags: , ,

MS Excel files as MS Outlook attachments are Not Opening

One of my friends reported that he is not able to open MS Excel 2013 files from MS Outlook 2013 that has been sent as attachment.  He was not able to even preview it in Outlook.

The error message was:

Microsoft Excel cannot open or save any more documents because there is not enough available memory or disk space.

  • To make more memory available, close workbooks or programs you no longer need.
  • To free disk space, delete files you no longer need from the disk you are saving to.

But when saved on disk the file was successfully opening without any error.

err

The solution to this problem is simple.  Follow the below steps to solve:

  • Open Excel.
  • Go to File –> Options.
  • Click on Trust Center option on left navigation pane.
  • Click on Trust Center Settings button on the right.
  • Click on Protected View option on left navigation pane.
  • Untick all the options under Protected View on the right.
  • Close all the open dialog boxes by click on OK buttons.
 
Leave a comment

Posted by on June 6, 2015 in MS Excel, MS Outlook

 

Tags: , , , , ,

Transpose in Excel

Sometimes it happens that we enter tabular data in some arrangement and then realize that it should not be in this arrangement.  We may want to switch the columns to row or row to columns.  Now there is no need to retype or manually cutting and pasting each and every cell.  Excel has a built in option, Transpose, for that.  Switching data from ‘Rows to Columns’ or ‘Columns to Rows’ is called Transposing.

Transpose1

In the above figure Zones are in column and Months are in Rows.  Now you want to arrange Months in Columns and Zones in Rows.

Follow the below steps to accomplish the same:

Select the source data and copy.  You may use Ctrl-C for copying or click on Home tab –> Clipboard group –> Copy button.

Transpose2

Select target cell by clicking on it.  Click on down arrow below Home tab –> Clipboard group –> Paste button and select Transpose from available Paste options.

Transpose3

Transpose4

Points to note:

  1. Do not use Cut command.
  2. Copy and Paste areas cannot overlap.  Make sure that you select a cell in a paste area that falls outside of the area from which you copied the data.
  3. If the cells that you transpose contain formulas, the formulas are transposed and cell references to data in transposed cells are automatically adjusted. To make sure that formulas continue to refer correctly to data in nontransposed cells, use absolute references in the formulas before you transpose them.
 
Leave a comment

Posted by on June 30, 2014 in MS Excel

 

Tags: , , , , , , ,

Macro Code for Extracting URL from Hyperlink

Public Function GetURL(aa As Range) As String
On Error Resume Next
GetURL = aa.Hyperlinks(1).Address
End Function

 
Leave a comment

Posted by on February 10, 2014 in MS Excel

 

Cut and Copy Options are Greyed Out – Excel 2013

One of my friends reported me that he is able to ‘Cut’ and ‘Copy’ using Ribbon buttons and Ctrl-X and Ctrl-C as usual but when he Right clicks on any cell ‘Cut’ and ‘Copy’ options are disabled in shortcut menu.  But if used Ctrl-C, Paste option is available on shortcut menu.

Whereas these options are working well with other MS-Office files.

Following is the solution that I worked out and provided to the user and it solved the problem:

  • Close Excel
  • Open My Computer
  • Go to folder ‘C:\Users\username\AppData\Roaming\Microsoft\Excel’.  (AppData folder is hidden by default)
  • Rename Excel14.xlb and Excel15.xlb
  • Open Excel and Check
 
30 Comments

Posted by on December 21, 2013 in MS Excel

 

MS Excel Shortcut Keys

F2 Edit the selected cell.

F3 After a name has been created F3 will paste names.

F4 Repeat last action. For example, if you changed the color of text in another cell pressing F4 will change the text in cell to the same color.

F5 Go to a specific cell. For example, C6.

F7 Spell check selected text or document.

F11 Create chart from selected data.

Ctrl + Shift + ; Enter the current time.

Ctrl + ; Enter the current date.

Alt + Shift + F1 Insert New Worksheet.

Alt + Enter While typing text in a cell pressing Alt + Enter will move
to the next line allowing for multiple lines of text in one cell.

Shift + F3 Open the Excel formula window.

Shift + F5 Bring up search box.

Ctrl + A Select all contents of the worksheet.

Ctrl + B Bold highlighted selection.

Ctrl + I Italic highlighted selection.

Ctrl + K Insert link.

Ctrl + U Underline highlighted selection.

Ctrl + 1 Change the format of selected cells.

Ctrl + 5 Strike through highlighted selection.

Ctrl + P Bring up the print dialog box to begin printing.

Ctrl + Z Undo last action.

Ctrl + F3 Open Excel Name Manager.

Ctrl + F9 Minimize current window.

Ctrl + F10 Maximize currently selected window.

Ctrl + F6 Switch between open workbooks or windows.

Ctrl + Page up Move between Excel work sheets in the same Excel document.

Ctrl + Page down Move between Excel work sheets in the same Excel document.

Ctrl + Tab Move between Two or more open Excel files.

Alt + = Create a formula to sum all of the above cells

Ctrl + ‘ Insert the value of the above cell into cell currently selected.

Ctrl + Shift + ! Format number in comma format.

Ctrl + Shift + $ Format number in currency format.

Ctrl + Shift + # Format number in date format.

Ctrl + Shift + % Format number in percentage format.

Ctrl + Shift + ^ Format number in scientific format.

Ctrl + Shift + @ Format number in time format.

Ctrl + Arrow key Move to next section of text.

Ctrl + Space Select entire column.

Shift + Space Select entire row.

Ctrl + – Delete the slected column or row.

Ctrl + Shift + = Insert a new column or row.

Ctrl + Home Move to cell A1.

Ctrl + ~ Switch between showing Excel formulas or their values in cells.

 
4 Comments

Posted by on September 10, 2013 in MS Excel, MS Office

 

Tags: , , , , , , , , , , ,

If Function in Excel

The if() function is used to analyze data, test whether or not it meets certain conditions and then act upon its decision.   The if() function has three parameters 1. Logical_test, 2. Value_if_true and 3. Value_if_false.   The syntax of if() function is as under:

= IF(logical_test, value_if_true, value_if_false)

For comparing two conditions (logical_test) Excel provides following LOGICAL OPERATORS:

  • =             Equal to
  • <>           Not Equal to
  • >             Greater Than
  • <             Less Than
  • >=           Greater Than or Equal To
  • <=           Less than or Equal To

Value_if_true is the value to be returned if the result of logical test is true.

Value_if_false is the value to be returned if the result of logical test is false.

 

Example:

If cell A1 contains 65 and we use function =if(A1>60, “Yes”, “No”) then it will return “Yes” and if we use function =if(A1<60, “Yes”, “No”) then it will return “No”.

 
Leave a comment

Posted by on September 7, 2013 in MS Excel