100 Excel Tips & Resources

These 100 tips & resources are organized in to the areas,

• Keyboard Shortcuts
• Formulas
• Using Excel to do more
• Charting
• Excel Books for everyone
• Excel Blogs & Resources

All these tips are bite sized and easy to read, digest and implement. The focus is on improving your productivity and making your day better. Wherever possible, I have included links to relevant articles on this site so that you can read and learn more.

25 Very Useful Keyboard Shortcuts

1. To format any selected object, press ctrl+1
2. To insert current date, press ctrl+;
3. To insert current time, press ctrl+shift+;
4. To repeat last action, press F4
5. To edit a cell comment, press shift + F2
6. To autosum selected cells, press alt + =
7. To see the suggest drop-down in a cell, press alt + down arrow
8. To enter multiple lines in a cell, press alt+enter
9. To insert a new sheet, press shift + F11
10. To edit active cell, press F2 (places cursor in the end)
11. To hide current row, press ctrl+9
12. To hide current column, press ctrl+0
13. To unhide rows in selected range, press ctrl+shift+9
14. To unhide columns in selected range, press ctrl+shift+0
15. To recalculate formulas, press F9
16. To select data in current region, press ctrl+shift+8
17. 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 F4
19. To format a number as currency, press ctrl+shift+4 (ctrl+$)
20. To apply outline border around selected cells, press ctrl+shift+7
21. To open the macros dialog box, press alt+F8
22. To copy value from above cell, press ctrl+’
23. To format current cell with comma formats, press ctrl+shift+1
24. To go to the next worksheet, press ctrl+shift+pg down
25. 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)…
29. To get nth smallest number in a range, use = small(range,n)…
30. To generate a random phone number, use =randbetween(1000000000,9999999999), needs analysis toolpak if you are using excel 2003 or earlier…
31. To count number of words in a cell, use =len(trim(text))-len(SUBSTITUTE(trim(text),” “,”"))…
32. To count positive values in a range, use =countif(range,”>0″)…
33. To calculate weighted average, use SUMPRODUCT() function
34. To remove unnecessary spaces, use =trim(text)
35. To format a number as SSN using formulas, use =text(ssn-text,”000-00-0000″)…
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 days
37. To get name from initials from a name, use IF(), FIND(), LEN() and SUBSTITUTE() formulas…
38. 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…
42. 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…
44. 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″…
47. To format a phone number, use the custom format code “000-000-0000″…
48. To show values after decimal point only when number is less than one, use [<1]_($#,##0.00_);_($#,##0_) as formatting code…
49. 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)…
50. To hide a worksheet, go to menu > format > sheet > hide…
51. To align multiple objects, like charts, drawings, pictures use drawing toolbar > align and select alignment option…
52. To freeze rows on top, select the a row and use menu > window > freeze panes…
53. To disable annoying formula errors, go to menu > tools > options > error checking tab and disable errors you don’t want to see…
54. 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…
55. To transpose a range of cells, copy the cells, go to empty area, and press alt+e+s+e…
56. To save data filter settings so that you can reuse them again, use custom views…
57. 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 chart

9 Charting Tips for Everyone

61. To create an instant micro-chart from your normal chart, use camera tool…
62. Understand  to reduce chart junk, using even a pixel more of ink than what is needed can reduce your chart’s effectiveness
63. Combine two different types of charts when one is not enough, to use, add another series of data to your sheet and then right click on it and change the chart type…
64. To reverse the order of items in a bar / column chart, just click on y-axis, press ctrl+1, and check “categories in reverse order” and “x-axis crosses at maximum category” options
65. To change the marker symbol or bubble in a chart to your own favorite shape, just draw any shape in worksheet using drawing toolbar, then copy it by pressing ctrl+c, now go to the chart and select markers (or bubbles) and press ctrl+v
66. To create partially overlapped column / bar charts, just use overlap and gap settings in the format data series area. A overlap of 100 will completely overlap one series on another, while 0 separates them completely.…
67. To increase the contrast of your chart, just remove grayish background color that excel adds to the chart (in versions excel 2003 and prior)
68. To save yourself some trouble, always try to avoid charts like – 3D area charts (unstacked), radar charts, 3D Lines, 3D Columns with multiple series of data, Donut charts with more than 2 series of data…
69. To improve comparison, replace your radar charts with tables…

6 simple steps for better chart formats

70. Remove any vertical grid-lines
71. Change horizontal grid-line color from black to a very light shade of gray
72. Adjust chart series colors to get better contrast
73. Adjust font scaling (for versions excel 2003 and prior)
74. Add data labels and remove any axis (axis labels) if needed
75. Remove chart background colors

5 Excel books for everyone

76. Excel 2007 Bible by John Walkenbach
77. Excel 2007 Formulas by John Walkenbach
78. Excel 2007 Charts by John Walkenbach
79. Excel 2007 Power Programming with VBA
80. Teach Yourself VISUALLY Excel 2007
PS: Links to Amazon, affiliate code used

20 Excellent Resources and Blogs for getting latest Excel Tips & Charting Ideas

81. PTS Blog
82. Andrew’s Excel Tips
83. Microsoft Excel 2007 Team Blog
84. Contextures
85. Junk Charts
86. Daily Dose of Excel
87. Digital Inspiration
88. Life Hacker
89. Jorge’s Charts Blog
90. DSA Inisights
91. Excel Blog @ TVM Calcs
92. Juice Analytics
93. More information per pixel
94. Newton Excel Bach
95. Presentation Zen
96. Visual Business Intelligence by Stephen Few
97. Cell Matrix
98. Allen Wyatt’s Excel Tips
99. Code for Excel and Outlook
100. All the Popular Excel Bookmarks in Delicious

Conditional Formatting in Excel – 5 Tips to make you a Rockstar

Excel conditional formatting is a hidden and powerful gem that when used well, can change the outlook of your project report / sales budget / project plan or analytical outputs from bunch of raw data in default fonts to something truly professional and good looking. Better still, you dont even need to be a guru or excel pro to achieve dramatic results. All you need is some coffee and this post to learn some cool conditional formatting tricks.

So you got your coffee mug? well, lets start!

The 5 tricks we are going to learn are,

1. Highlighting alternative rows / columns in tables

2. No-nonsense project plans / gantt charts

3. Extreme Incell graphs

4. Highlight mistakes, errors, omissions, repetitions

5. Create intuitive dashboards

If you are new to Excel Conditional Formatting, please read the Conditional Formatting Basics article before proceeding.

I have created an excel sheet containing all these examples. Feel free to download the excel and be a conditional formatting rock star

 

1. Highlighting alternative rows / columns in tables:

Often when you present data in a large table it looks monotonous and is difficult to read. This is because your eyes start interpreting the data as grid instead of some important numbers. To break this you try highlighting or changing the background color of alternative rows / columns. But how would you do this if you have rather large table and it keeps changing. The trick lies in Conditional Formatting. (Of course you can use the built-in auto format feature, but we all know how the default settings of various Microsoft products are like).

• First select data part of the table you want to format.
• Go to Conditional formatting dialog (Menu > Format > Conditional Formatting)
• Change the “cell value is” to “formula is” (YES, you can base your formatting outcome on formulas instead of cell values)
• Now, if you want to highlight alternative rows, the formula can go something like this,
  =MOD(ROW(),2)=0
which means, whenever row() of the current cell is even, to change the coloring to odd rows, you just need to put =MOD(ROW(),2)=1 as formula
Also, if you want to highlight alternative columns instead of rows you can use the column() formula.
What if you want to change background color of every 3rd row instead, just use =MOD(ROW(),3)=0 instead. Just use your imagination.

• Set the format as you like, in my case I have used yellow color. When you are done, the dialog should look something like this:
  
• Click OK.
• Congratulations, you have mastered a conditional formatting trick now

 

2. Creating a quick project plan / gantt chart using conditional formatting:

 

Project plans / gantt charts are everyday activity in most of our lives. Creating a simple and snazzy project plan template in excel is not a difficult job, using conditional formatting a bit of formulas you can do it no time.

• First create a table structure like shown above, with columns like Activity, start and end day, day 1, 2,3, etc…
• Now, whenever a day falls between start and end day for a corresponding activity, we need to highlight that row. For that we need to identify whether a day falls between start and end. We can do that with the below formulas,
  =IF(AND(F$8>=$D9, F$8<=$E9),"1","")
Which means, whenever, the day number represented on the top row is between start and end we will in 1 in the corresponding cell.

• Next, whenever the cell value is 1, we will just fill the cell with a favorite color and change the font to same color, so that we dont see anything but a highlighted cell, better still, whenever you change the start or end dates, the color will change automatically. This will be done by conditional formatting like below:
  
• Congratulations, you have mastered the art of creating excel gantt charts now

 

3. Extreme In-cell Graphs:

Incell graphing is a nifty trick that basically uses REPT() function (used to repeat a string, character given number of times) to generate bar-charts with in a cell. You can apply conditional formatting on top of them to give the charts a good effect. Here is a sample:



The above is a table of visits to Pointy Haried Dilbert in the month of January 2008. As you can see I have highlighted (by changing the font color to red and making it bold) for the cells that have more than average number of visits in the month. I am not going to tell you how to do it, it is your home work

4. Highlight mistakes / errors / omissions / repetitions using conditional formatting:

Often we will do highly monotonous job like typing data in a sheet. Since the work is monotonous you tend to make mistakes, omit a few or repeat something etc. This can be avoided by conditional formatting. I use this trick whenever I am typing something or pasting a formula over a rather large range of cells (for eg. vlookup on annual revenue data of all your accounts, could run in to thousands of rows across multiple states /regions etc.).

Lets see how you can highlight a cell when it has an error:

• First select the cells that you want to search for errors
• Next go to menu > format > conditional formatting and mention the formula as: =iserror() (see below)
  
• In the same way you track repetitions, a simple countif() would do the magic for you, or Omissions (again a countif())
• Thats it, you have learned how to save tons of time by letting excel do the job for you. Sit back and sip that coffee before it gets cold.

5. Creating dash boards using excel conditional formatting:

As I said before you can use conditional formatting to create intuitive sales reports or analytics outputs. Like the one shown here,



Here is how you can do it:

• Copy your data table to a new table.
• Empty the data part and replace it with formula that can go like this (I am using the above table format to write these formulas, may change for your data)
  =ROUND(C10,0) & " " & IF(C9 Essentially, what we are doing is, whenever the cell value is more than its predecessor in the data table we are appending the symbol â–² (go to menu > insert > symbols and look for the above one) etc.

• Next, conditionally change the color of cell to red / green / blue or pink (if you want ) and you are done
• Show it to your boss, bask in the glory

I have created an excel sheet containing all these examples. Feel free to download the excel and be a conditional formatting rock star.

Excel Conditional Formatting Basics

What is excel conditional formatting ?

Conditional formatting is your way of telling excel to format all the cells that meet a criteria in a certain way. For eg. you can use conditional formatting to change the font color of all cells with negative values or change background color of cells with duplicate values.

Why use conditional formatting?

Of course, you can manually change the formats of cells that meet a criteria. But this a cumbersome and repetitive process. Especially if you have large set of values or your values change often. That is why we use conditional formatting. To automatically change formatting when a cell meets certain criteria.

Few Examples of Conditional Formatting

 Here are 3 examples of conditional formatting.

So How do I Apply Conditional Formatting?

This is very simple. First select the cells you want to format conditionally. Click on menu >
format > conditional formatting or the big conditional formatting button in Excel 2007.

(we have used excel 2003 in this tutorial, but conditional formatting is similar in excel 2007 with lots of additional features)

You will see a dialog like this:

There are 2 types of conditions:

• Cell value based conditions: These conditions are tested on the cell value itself. So if you select a bunch of cells, and mention the condition as between 15 and 25, all the cells with values between 15 and 25 are formatted as you specify.
• Formula based conditions: Sometimes you need more flexibility than a few simple conditions. That is when formulas come handy. Conditional Formatting Formulas are slightly complicated and can be difficult to learn or use if you are new to excel. But they are very useful and intuitive and if you use them once you get a hang of it.

What are the limitations of Conditional Formatting?

In earlier versions of Excel you can only define max. of 3 conditions. This is no longer true if you are using Excel 2007.
However, you can overcome the conditional formatting limitation using VBA macros (again, if you are new to excel, you may want to wait few weeks before plunging in to VBA)
Also, you can only use conditional formatting with cells and not with other objects like charts.

Ok, Enough Theory, Time for your First Conditional Formatting

Go ahead, open a new workbook and try few conditional formats yourself. See how easy and intuitive it is. Use it in your day to day work and impress your colleagues. Learn 5 impressive tricks about conditional formatting.
If you have trouble getting started, download the conditional formatting examples workbook.

Tell us how YOU use Conditional Formatting

Share with us how you use CF in your work. I am sucker for conditional formatting and use it wherever I can. What about you?

Create in-cell pie charts in Excel

Here is a ridiculously easy trick to do nice incell pie charts in excel, what more, they will make you look like a charting wizard.

1. Download free pie chart font from here (direct download link). The font has 21 pie chart codes starting with empty circle (character ‘a’) and ending with full circle (character ‘u’)
2. Install the font (just extract the zip file, copy the font file to your fonts folder, click here to learn how to install a font in windows)
3. Open excel, go to your data table and insert a column where you want incell pie charts
4. Enter a formula like =CHAR(CODE("a") + ROUND(data cell*21,1)), just replace the data cell with actual cell code.
5. Finally change the font to “pie charts for maps” from font dialog.
6. There is no step 6, so go ahead and show off this cool table to your colleagues, make their jaws drop in awe.

You can add some conditional formatting to the pie charts like I did to make it nice.

Creating in-cell bar charts / histograms in excel

Ever since writing the create in-cell pie charts in excel, I have been itching to find a simple enough method to do incell bar graphs. An in-cell bar would probably be more useful and cuter than an in-cell pie as it can instantly provide trending details. What more, these would probably look gorgeous when printed out.

My first challenge was that there was no font readily available for bar graphs. Thankfully this is when I noticed Font Struct, an online font creator / editor. I quickly created a bar graph font using their wonderful editor. When you use bargraph font, 0 through 9 will show bars of increasing heights, as shown below.

You should download bargraph font if you want to use incell bar graphing technique mentioned here.
Once you have downloaded the font, the rest is simple process. For this example, lets use fictitious sales data of various beverages over the last 12 months. The initial data table is something like this:

1.  First we will insert a column next to the total sales column and call it “last 12 months”. We will use this column to fill up the in-cell bar graph for the last 12 month sales. 
2.  Next we need to normalize the sales values for each month to a value between 0 to 9. I have used linear normalization, ie the maximum value across the 12 months would be 9 and everything else will be normalized according this. The formula for one month looked something like this: =ROUND(jan_sales/MAX(all_sales)*9,0). Remember, we need to round the data make it one digit instead of a decimal.
3. Once we have normalized values for each of 12 sales figures for each row, we just need to concatenate them to create a 12 character long string of numbers and place this values in our newly inserted column
4. Finally, we will change the font of this column to “bargraph” (just select all the cells under the “last 12 months” and change the font) When we are finally done, our table should look something like this:
   
   
   
   
   Thats all, we now have a charming data table with cute little incell bar graphs to insert your project report / sales memo or news letter.

 Feel free to download example excel sheet I have created and learn how to do in-cell bar graphs

Remember, you need to have the bargraph font installed first. 

Do you like this? share your feedback.