Several things about MS Excel 2007 you might want to know

December 19, 2012 @ No comments yet

There are many features on MS Excel 2007 that I usually ignored or just don’t know. Not to mention the common functions, a simple shortcut or an easy mouse click. Even an AutoSum button turned me into a noob on friend eyes. Not because I don’t know the button function but because I did too much “Insert Function” thing.

So whats was special about this AutoSum? it’s because this button did faster calculation compared to writing a “=SUM()” function. You select a cell, click AutoSum, and then thousands of rows above the cell will calculated in just a second. Imagine if you put sum function on the last row. You’re about to put the cell references but you don’t remember which one was the first cell from the 6000+ rows?. Scroll up to see the first cell reference?

Only simple MS Excel 2007 features

Some of the following lists are based on my “previously ignored list”, some based on several sources (Google and friends), and some others simple things but important.

Merge xlsx files

  • You can merge Excel files by move or copy all or selected sheets on a file into another file. On condition both Excel are opened.
  • To type a Numeric as Text on a cell you just need to put a single quote ( ‘ ) before the numeric.
  • Using “=VALUE()” function will turn it back into numeric.
  • You can also convert to number when Error Checking option appear.
  • Be careful changing text to number, it will sometime ruined the real number (i.e. 1,12345E+15)

convert to number

  • If you put percent symbol (%) after an integer number, it will give a decimal value (i.e. =28%,  result = 0.28).
  • If you put percent symbol (%) on a divisor number, it will give percentage value (i.e. =100/1000%, result = 10).
  • There are 3 ways to edit cell; double click cell, using F2 and Formula bar.
  • Duplicating formula can be done in 3 ways; CTRL+D shortcut, dragging cell corner and Double clicking cell corner.

duplicate formula

  • On formula with time function, “[hh]:mm:ss” will give 24 hours format while ”hh:mm:ss AM/PM” give 12 hour format.
  • Put “-” or “+” operator before math operation, it will automatically add “=” before the formula (i.e. -45+3, will be =-45+3).
  • Pressing slash key (“/“) on empty cell will show shortcut hint for toolbar menu and sub menu. You can then press associated key to open menu.

excel shortcut hint

  • To copy cell value and paste as text only, right click destination cell, click Paste Special and select Value.
  • You can also chose to Transpose position from Rows to Column or vice versa on Paste Special menu.
  • Paste formula contain cell reference of source sheet into another sheet will also changed the formula to use cells reference on destination sheet except for absolutes cell reference (F4).
  • Cell reference on another file will show full file path of excel file if not opened.
  • It will changed back to file-name only if the file opened.

full path excel reference

  • You can use partial value of cell as a  lookup value on Vlookup and Hlookup.
  • Multiple Vlookup columns on 6000+ rows will caused Excel not responding for several minutes (more than 5 minutes in my case).
  • To remove duplicate values, select columns/cells, open Data tab menu and click Remove Duplicate.
  • To mark duplicate values, select columns/cells, Open Home tab menu, click Conditional Formatting, Highlight Cells Rules and click Duplicate Values.

Mark duplicate values

  • You can also sort and filter columns by cell colors.
  • Don’t trust Print Preview, sometime the printed document a bit smaller than previewed one. Over flowed text on print preview sometime printed as normal.
  • Be conscientious before printing, a single character on unused column can make you print tons of empty pages until it reach that character.
  • You can create Poster of documents by changing print scale to large percentage on page layout.

There still many things about MS Excel 2007 that I still want to share. Only i can’t remember any of them anymore. I’ve been stuck in-front of my laptop for a while thinking about “what simple things about excel that useful for me”, and can’t remember any. But don’t worry, I’ll update more list when i remember or got new one. Mean while if any of you had things about Excel that you want to share, please write on comment section.

M Riza

Article by M Riza

Is a blogger since 2007, founder and editor of Oa Ultimate. Working as a network administrator, computer technician, database and web maintainer | Twitter | G+

Leave a Reply

*

Loading Google+ Comments ...
Loading Facebook Comments ...