EXCEL-2007-Secrets tips

Do you know these Excel 2007 Productivity Secrets (Hint: Coffee is not one of them)Posted on May 26th, 2009 in Excel Howtos , Learn Excel - 18 comments

Ok, Coffee can be one of them, but may be after the 3rd mug of the day, you will ask the question, “so, what are the productivity secrets of Excel 2007?”.

That, my friend, is the reason why you should put aside the mug and read this post carefully. The post is pure filtered decoction of tips you must sip.

if you like this post (or for that matter if you like coffee) please add it to your delicious bookmarks or stumble it or tweet it.

Thanks, You are beautiful.

Turn on the Clipboard pane, Copy paste like there is no tomorrow

We all swear by ctrl+c, ctrl+v. A large part of my MBA case studies were submitted intime thanks only to the awesome clipboard. But do you know that you can turn on the clipboard feature in Excel 2007 in a full panel view and use it to speed up the copy paste activity.

Hold Down ALT to see what keyboard shortcuts are available, press key and you will see more

All you have to do is hold the ALT key down. And I can bet that you will learn a fun and simple keyboard shortcut for something that you use everyday. What more, in Excel 2007 you can use most of the Excel 2003 (and earlier) keyboard shortcuts.

Lock a feature to Boost up your performance, for eg. lock format painter and paste formats everywhere

When you need to perform certain action several times, like for eg. if you want to paste the same formatting over 40 different cells, you can lock the format painter by double clicking on it. See the screencast to understand how it works.

(When you can inserting shapes etc, you can use “lock drawing mode” option from right click menu)

Make your charts and tables go everywhere, copy them as pictures

When you copy a chart / table of cells as picture, they take less space and look lot better when pasted.

Add Invisible / Hidden features to your Quick Access Toolbar

You can add hidden features of excel 2007 like camera tool etc. to the quick access toolbar (the tools that you usually see next to office button). Just click on the office button > Excel options > Customize and select “commands not in the ribbon”. These are the features that are not usually shown on ribbon (some of them are shown when you lets say select a chart or table or something like that).

Move the Quick Access Toolbar down to save mouse travel time

I think that title says it all. See the image.

Always save the files in Excel 2003-97 compatible mode

Very useful if your colleagues / clients are still using old versions. Just go to Office button > Excel Options > Save and change the file type to Excel 2003-97.

Click on the corner of a group of Ribbon Icons to launch its UI

This is not applicable for all groups. But for some groups of icons you see a little squarish icon on the bottom right corner. Just hit it to launch a friendly office 2003ish looking dialog to make changes to the settings etc.

Add chart related options to Quick Access Toolbar to save time

If you make a lot of charts, then it pays to add the chart related options like “adding error bars”, “adding axis” etc. to the quick access toolbar. Then you can press ALT+number to activate this feature and work with it without even moving your mouse.

Create a named range quickly by typing the name in the corner of formula bar

You can quickly create a named range by selecting a bunch of cells and typing the name in the formula bar’s left hand corner where usually cell address’ is displayed.

See the screencast.

BONUS: Enable Developer Ribbon Toolbar in Excel 2007

If you in to mochas and trying to explore macros, then this one is for you. Enable the Developer toolbar from Excel options > Popular and you can play with all those form controls and macros.

15 MS Excel Tips to Make you a Productivity GuruPosted on October 16th, 2008 in Featured , Learn Excel - 21 comments

We all like to customize things, to personalize them so that we feel better, faster and smarter using them. Microsoft Excel is a perfect example of highly customizable software. It is simple to learn and use, and at the same time it is relatively easy to change the way it works for you.

Here is a list of 15 useful excel tweaks that can make you hyper-productive. Happy Thursday

1. Change the order in which you edit cells

We all know that when you hit enter on a cell excel usually takes you to the next cell in that column for editing. But what if you need to go to next cell in that row? Of course you can use tab. But you can also customize the cell edit order when you are typing out that large list of entries so that you need not change your habits for the software. Just go to Menu > Tools > Options > Edit tab and set the “move selection after enter” to whatever direction you like.

2. Change the number of default sheets on open from three

Whenever you open a new workbook, excel shows 3 spreadsheets by default. Most of the times we need one or two of them. And when we are sharing the project plan or sales report (or whatever else the excel file has) with colleagues, we remove the other 2 sheets. You can save the trouble by telling excel to create only one sheet by default and let you add more if you need it. In Menu > Tools > Options > General Tab change the “sheets in new workbook” from 3 to 1.

3. Customize excel’s standard font to Arial to your favorite

Each one of us have our own favorite fonts. I like Verdana better than Arial. May be you like Georgia compared to Arial. But when you set out to create that gantt chart for your new project you have to manually change the font from Arial to Georgia everytime. No longer. Tell excel to

change the default font from Arial to your favorite. In Menu > Tools > Options > General Tab set the “Standard font” to what you like. You can set the font size as well.

4. Hack auto-fills using custom lists

Excel has few built-in lists that it uses for auto-filling cells when you drag that little pointer across. For eg. you can write Monday in 1st cell, Tuesday in 2nd cell and select these two and drag that over the next few cells by clicking in the corner and excel would fill rest of the weekdays in that range. This is a very useful feature. But what if you do repetitive typing your company products or annual holidays ? Of course you can tell excel to use your own lists for auto-fills. Just go to Menu > Tools > Options > Custom Lists and add your own lists by typing them or pointing to a location where they are.

5. Change the colors to something bold and better

Excel (2003 and earlier) has a limitation of 56 colors. We all have been living with that for a while now. But what if you need to tweak the colors to suit your company’s color scheme without spending too much time on it. Simple. Just change the colors for the current workbook by going to Menu > Tools > Options > Color and define your own RGB values for each of the colors. Alternative you can try this hack to get more colors in your charts.

6. Configure thousands separator and decimal symbols

Excel’s number formatting is pretty intelligent. It can get your country locale information from the system you are using and thus format the numbers (the thousands separators symbol and decimal point symbol) based on that. This is a very useful feature since you dont have to worry how the numbers are shown. But what if you are in US but your reports needs to show numbers according to some other country’s format? You can change the thousands separator and decimal point symbols to suit your preference. In the Menu > Tools > Options > International Tab, uncheck the “use system separators” and enter your own.

7. Bugged with annoying error checking options? Turn them off forever

Excel’s formula error checking options are both useful and annoying. For eg. Excel would tell me if there is an “inconsistent formula in region”. Very useful feature to spot omissions. But what if you already know what you are doing and you need to omit few cells in that region in that formula? Still excel would bug you to correct that error. It may be better to turn off this error checking option that silence it every time. Go to Menu > Tools > Options > Error Checking Tab and uncheck error checking rules that you don’t want excel to apply.

8. Reduce your typing by using auto correct

We have come to rely on features like spell check, undo, auto correct so much that our productivity would drop 50% if these features were to vanish tomorrow. But do you know that you can use auto-correct feature to be even more productive? You can set your own auto correct rules in Excel (Word, PowerPoint as well) and this little tweak can help you reduce typing. For

eg. while writing blog posts I often write PHD to convey Pointy Haired Dilbert. Instead of actually typing Pointy Haired Dilbert every time, I can define an auto-correct rule that says replace PHD with Pointy Haired Dilbert. Imagine how much of typing you can reduce by defining simple replacements for several day to day words you type. Define your own auto correct rules in Menu > Tools > Auto Correct Options > Auto Correct Tab.

9. Tell excel to show full menus, Always!

One of the smart features of MS Office is that the menus learn what options you use often and show only them when you click on the menu. But this gets in the way of being productive if your work involves using various options all the time. Fortunately you can turn off this feature and tell Excel (and other office apps) to show Full menus always. Just go to Menu > Tools > Customize > Options Tab and check “Always show full menus” option.

10. Reduce the workbook size by compressing pictures

Whenever you are sharing the workbooks with colleagues either through mail or by uploading to a server, it is recommended to keep the size of workbooks low in order to let the receivers quickly get the file. Sometimes excel files can be very huge depending on the number of sheets and formulas you have used. There is one trick to reduce the size of excel files with images. You can tell excel to compress the images for web viewing. This will force saving the images at lower resolution of 96dpi instead of standard 200dpi. In the Menu > File > Save As dialog box, click on “Compress pictures” option and adjust the resolution in the “change resolution” area.

11. Tell excel how to calculate your formulas

Often when you are working on spreadsheets with lots of formulas it can be a bit inconvenient to have excel recalculate every formula at each key stroke. Especially if the formulas involve a large range then excel can take quite a while to perform the calculations hogging system resources. Of course there is a way to force excel to calculate formulas when you choose to (by hitting F9). In Menu > Tools > Options > Calculation tab set the calculation to Manual or Automatic except for tables.

12. Save time by using templates

Every organization / individual has their own tastes on how a status report, project plan, gantt chart or grade table should look like. And often we spend hours touching up that spreadsheet / document to make sure it complies with standard styles / fonts / layouts. There is a simple work around that could reduce the time spent on formatting in Excel. Next time you make a project plan, save it as a template and use it whenever you need a project plan and edit the stuff you need to. Saves time for other nicer things like.. mmm, sipping coffee or reading Pointy Haired Dilbert.

13. Use paste special when copying charts to PowerPoint, it saves space as well

Ok, this is pretty common, we design charts, tables in Excel and then paste them in to PowerPoint. But somehow the formatting is not preserved or the file is too huge. Here is a simple hack that you may already know. Use paste special whenever you are pasting the charts / tables to PowerPoint / word or outlook and select “enhanced meta file” option. This makes sure you have a good quality chart that looks slick when projected (or printed) while taking up less space.

14. Remove any personal information from the spreadsheet with one click

Often when you make a spreadsheet about that vacation plan or to share party expenses and send it to others you may want to remove your personal information from the excel file, just to make sure the file is harmless even if it is posted on the cloud. In Menu > Tools > Options > Security tab, check the “Remove personal information from file properties on save” to make sure your company name, last name etc. are removed from the excel file properties.

15. Bored with Excel menus? Create your own

Finally, how would you like your own menu with your own shortcuts ? This can be super-productive if you spreadsheet a lot and need to access all those nifty features with one click. Here is a clue on how to get your own menus. Go to Menu > Tools > Customize > Commands Tab and add your menu to the list. How? That is your home work. After all you are a spreadsheet customization guru now.

That is all. So go ahead and tweak that spreadsheet software and have fun in all the time you have just saved.

25 Very Useful Keyboard Shortcuts

1. To format any selected object, press ctrl+12. To insert current date, press ctrl+;3. To insert current time, press ctrl+shift+;4. To repeat last action, press F45. To edit a cell comment, press shift + F26. To autosum selected cells, press alt + =7. To see the suggest drop-down in a cell, press alt + down arrow8. To enter multiple lines in a cell, press alt+enter9. To insert a new sheet, press shift + F1110. To edit active cell, press F2 (places cursor in the end)11. To hide current row, press ctrl+912. To hide current column, press ctrl+013. To unhide rows in selected range, press ctrl+shift+914. To unhide columns in selected range, press ctrl+shift+015. To recalculate formulas, press F916. To select data in current region, press ctrl+shift+817. To see formulas in the worksheet, press ctrl+shift+` (ctrl+~)18. While editing formulas to change the reference type from absolute to relative vice versa, press F419. To format a number as currency, press ctrl+shift+4 (ctrl+$)20. To apply outline border around selected cells, press ctrl+shift+721. To open the macros dialog box, press alt+F822. To copy value from above cell, press ctrl+’23. To format current cell with comma formats, press ctrl+shift+124. To go to the next worksheet, press ctrl+shift+pg down25. To go to the previous worksheet, press ctrl+shift+pg up

20 Situations and How to Solve them using Excel Formulas

26. To get the first name of a person, use =left(name,find(” “,name)-1)27. To calculate mortgage payments, use =PMT(interest-rate,number-of-payments,how-much-loan)28. To get nth largest number in a range, use =large(range,n)… Get Full Tip29. To get nth smallest number in a range, use = small(range,n)… Get Full Tip30. To generate a random phone number, use =randbetween(1000000000,9999999999), needs analysis toolpak if you are using excel 2003 or earlier… Get Full Tip31. To count number of words in a cell, use =len(trim(text))-len(SUBSTITUTE(trim(text),” “,”"))… Get Full Tip32. To count positive values in a range, use =countif(range,”>0″)… Get Full Tip33. To calculate weighted average, use SUMPRODUCT() function34. To remove unnecessary spaces, use =trim(text)35. To format a number as SSN using formulas, use =text(ssn-text,”000-00-0000″)… Get Full Tip

36. To find age of a person based on DOB, use =TEXT((NOW()-birth_date)&”",”yy “”years”" m “”months”" dd “”days”"”), output will be like 27 years 7 months 29 days37. To get name from initials from a name, use IF(), FIND(), LEN() and SUBSTITUTE() formulas… Get Full Tip38. To get proper fraction from a number (for eg 1/3 from 6/18), use =text(fraction, “?/?”)39. To get partial matches in vlookup, use * operator like this: =vlookup(”abc*”,lookup_range,return_column)40. To simulate averageif() in earlier versions of excel, use =sumif(range, criteria)/countif(range, criteria)41. To debug your formulas, select the portions of formula and press F9 to see the result of that portion… Get Full Tip42. To get the file extension from a file name, use =right(filename,3) (doesn’t work for files that have weird extensions like .docx, .htaccess etc.)43. To quickly insert an in cell micro-chart, use REPT() function… Get Full Tip44. COUNT() only counts number of cells with numbers in them, if you want to count number of cells with anything in them, use COUNTA()45. Using named ranges in formulas saves you a lot of time. To define one, just select some cells, and go to menu > insert > named ranges > define

15 Excel Tips on Improving Productivity Using Excel

46. To format a number as SSN, use the custom format code “000-00-0000″… Get Full Tip47. To format a phone number, use the custom format code “000-000-0000″… Get Full Tip48. To show values after decimal point only when number is less than one, use [<1]_($#,##0.00_);_($#,##0_) as formatting code… Get Full Tip49. To remove grid lines from excel worksheet, go to menu > tools > options > and un-check grid lines option. (Excel 2007: office button > excel option > advanced)… Get Full Tip50. To hide a worksheet, go to menu > format > sheet > hide… Get Full Tip51. To align multiple objects, like charts, drawings, pictures use drawing toolbar > align and select alignment option… Get Full Tip52. To freeze rows on top, select the a row and use menu > window > freeze panes… Get Full Tip53. To disable annoying formula errors, go to menu > tools > options > error checking tab and disable errors you don’t want to see… Get Full Tip54. To change the shape of cell comments from rectangle to some other symbol, select the comment, go to drawing tool bar and change the shape from there… Get Full Tip55. To transpose a range of cells, copy the cells, go to empty area, and press alt+e+s+e… Get Full Tip56. To save data filter settings so that you can reuse them again, use custom views… Get Full Tip57. To select all formulas, press CTRL+G, select “special” and check “formulas”58. To select all constants, press CTRL+G, select “special” and check “constants”59. To clear formats from a range, select menu > edit > clear > “formats”60. To move a chart and align it with cells, hold down ALT key while moving the

Excel can be Exciting – 15 fun things you can do with your spreadsheet in less than 5 secondsPosted on August 1st, 2008 in All Time Hits , Featured , Learn Excel , hacks , ideas - 60 comments

Who said Excel takes lot of time / steps do something? Here is a list of 15 incredibly fun things you can do to your spreadsheets and each takes no more than 5 seconds to do.

Happy Friday

1. Change the shape / color of cell comments

Just select the cell comment, go to draw menu in bottom left corner of the screen, and choose change auto shape option, select a 32 pointed star or heart symbol or a smiley face, just wow everyone

2. Filter unique items from a list

Select the data, go to data > filter > advanced filter and check the “unique items” option.

3. Sort from Left to Right

What if your data flows from left to right instead of top to bottom. Just change the sort orientation from “sort options” in the data > sort menu.

4. Hide the grid lines from your sheets

Go to Options dialog in tools menu, uncheck the “grid lines” option to remove gridlines from your worksheets. You can also change the color of grid line from here (not recommended)

5. Add rounded border to your charts, make them look smooth

Just right click on the chart, select format chart option, in the dialog, check the “rounded borders”. You can even add a shadow effect from here.

6. Fetch live stock quotes / company research with one click

Just enter the stock symbol (MSFT, GOOG, AAPL etc.) in a cell, alt+click on the cell to launch “research pane”, select stock quotes to see MSN Money quotes for the selected symbol. You can fetch company profiles in the same way. Learn more.

7. Repeat rows on top when printing, show table headers on every page

When you are on the sheet view, just hit menu > file > page setup, go to the last tab, specify “rows to repeat”. You can “repeat columns while printing” as well from the same menu.

8. Remove conditional formatting / all formatting with one click

Just go to Menu > Edit > Clear > All to remove all the formatting from selected cell / range.

9. Auto sum cells with one click

Select a bunch of cells and click on the Sigma symbol on the standard tool bar. Alternatively you can use Alt+= keyboard shortcut.

10. Find width of a column with formula, really!

Just use =cell("width") to find the width of the column to which that formula cell belongs. Width is returned as the nearest integer.

11. Find total working days between any two dates, including holidays

If you work on project plans, gantt charts alot, this can be totally handy. Just type =networkdays(start date, end date, list of holidays) to fetch the number of working days. In the above sample you can see the number of working days between New years day and September first of this year (labor day).

12. Freeze Rows / Columns in your sheet, Show important info even when scrolling

Select the cell diagonally beneath the row / columns you want to freeze (for eg. if you wan to freeze row 1&2 and columns A&B, click in C3), go to menu > window and click on freeze panes.

13. Split sheets in to two, compare side by side to be more productive

Just click on this little vertical bar on the bottom right corner of the sheet (see below) and drag it to create a vertical split. You can do the same way for a horizontal split as well

14. Change the color of various sheet name tabs

Right click on sheet and select “Tab color” option to change the worksheet tab colors. Group them with similar colors if you have lot of sheets, it looks nice.

15. Insert a quick organization chart

Click on menu > insert > diagram to open the above dialog, just select the organization chart option, enter node values and you have a pretty organization chart. Alternatively learn how to create org charts in excel.

So what do you say now? Isn’t Excel Exciting?