Sunday, November 29, 2015

Know More about AutoSum option in MS Excel

Today, when I was working with MS Excel then I used the AutoSum option many times to make sum of the numbers. I have noticed one thing about this option which was very interesting. This option is available in two places, first in Home tab and second in Formulas tab. Check the images below:

   AutoSum in Home Tab                                           AutoSum in Formulas Tab

Shortcut for AutoSum:

In MS Excel, every formula and function have its own shortcut. You can use ALT+ as the shortcut of AutoSum option. To test this option, write down the numbers in the excel sheet and press ALT+ to make its AutoSum. Now I am describing my experience with this function.

First I have created three columns named: Physics, Chemistry and English. Now I entered some numbers along with the columns.

Test of AutoSum Option

Vertically Up: Excel user may use‘=sum(number1,number2)’ formula or AutoSum option to sum-up the data. When I tried the AutoSum option in vertically up then it gives me the following result:

After this option, I pressed the Enter key to get my answer. The answer was correct.

Vertically Left: In this situation, I have selected the value of a single row and apply the AutoSum on it.I found the result like this:

I pressed Enter key and got my answer.

Vertically Down: Now, I was trying to test the AutoSum vertically down. In this case, the AutoSum option was not proving the range of the data cells. Check the image below:

So this is the one limitation with AutoSum option.

Vertically Right: The AutoSum option was not working in this situation. When I tried, then I got only the formula, not the range of numbers. Take a look:

So, Thing option doesn’t work in this situation.

Blank Cell in Numbers: If you have the blank cell in the numbers then, the AutoSum option will not take the values before it. This is also a limitation of this function.

So, we have seen the AutoSum function in the very different levels and the limitations of this function. I hope you like it. If you have more information about it then please share with me.
READ MORE - Know More about AutoSum option in MS Excel

Tuesday, November 24, 2015

Prevent Your Excel File by Automatic Creation of Hyperlinks

A hyperlink is important but in few cases it becomes tedious. Automatic creation of hyperlink is like a speed breaker in the document creation process. It creates with the following prefixes:

  • http:// 
  • ftp://
  • file:
  • www.
  • mailto:
  • news:
  • \\

Suppose, you are working with large the number of excel files and each file consists of thousands of hyperlinks then what will do? I think you will try to remove manually but here, we are talking about thousands of hyperlinks. In this case, try these options:

Apostrophe (‘) at the starting of the cell entry: To understand this option. Let’s take an example. Suppose you are trying to write in the cell. When you write it then, it becomes hyperlink automatically. In this case, write an apostrophe (‘) at the beginning of the cell entry.
It won’t become hyperlink. This is the first way to prevention of automatic hyperlink. The second option is more interesting which is given below:

Using autocorrect options: Simply follow these simple steps:

  • Go to the File-> Options -> Proofing -> Auto-Correct Options.
  • Click on the AutoFormat As You Type.
  • Uncheck the option “Internet and network paths with hyperlinks”

Using Macro: If you have the coding skills then you can easily try this method and if you are doing this first time then follow these steps:
  • Open the excel file and press Alt+F11 to start the visual basic editor.
  • In the left hand pane, you can see the worksheets of the excel files.

  • If you want to remove the hyperlinks of Sheet1 then double click on it.
  • Now, write the following code using project explorer:
  • Sub RemoveHyperlinks()
    End Sub 

  • Save the file.
  • Go to the excel sheet, Press Alt+F8 to run the macro. Click on the run button.

  • All the hyperlinks will be deleted easily.

Using paste special option: Paste special option is also used to remove the hyperlinks. Follow these steps:
  • Write 1 in a blank cell.
  • Copy the cell.
  • Select the cells with hyperlinks.
  • Now select the Paste Special.
  • Choose the multiply option.

  • Click OK.

Now the hyperlinks will be deleted off the selected range.
Note: This option will not work MS Excel 2007 and the previous versions. 

Final words: MS Excel does not provide any built-in method to fix this problem. In this article we have seen four methods to remove the automatic hyperlinks.   

READ MORE - Prevent Your Excel File by Automatic Creation of Hyperlinks
Related Posts Plugin for WordPress, Blogger...