Excel Factor 13 Handy Tips & Tricks

These Excel Factor tips are from Dr Isaac Gottlieb, professor at Temple University in Philadelphia.

Every month Isaac publishes a series of Excel tips at his University website. He offered to share his tips with us….the problem I had was choosing just a few for this post, so feel free to visit his site and glean more of his wisdom 🙂

Tip # 1 Change Format of a Specific Word or Value

Let’s say we want to format each instance of ‘Apples’ in the list below in bold green font.

1. Highlight the range of cells you want formatted.
2. CTRL+H to open the Find and Replace dialog box.
3. Type ‘Apples’ in the Find and Replace fields:
   
   
4. Click the Options button and select Format for ‘Replace with’:
   
   
5. This will open the Format dialog box where you can set your formatting:
   
   
6. Click OK once you’re done and you’ll be presented with a Preview. If you’re happy press ‘Replace All’.
   
   
7. Voila. Note: this will format the entire cell containing your chosen word or character, as you can see in cell A1 below :
   
   

Tip # 2 Multiply the Entire Range by One Value

Back in the days when I used to prepare budgets we would start with the prior year’s actual figures and increase them by a set amount….ah, if only it was that easy, budget done. Oh no, we’d then spend months doing a bottom up budget as well, but I won’t bore you with the details.

So, let’s say you’ve got a range of values that you want to increase by 10%, well it’s easy with Paste Special.

1. In an empty cell type 1.1
2. Copy the cell containing 1.1
3. Select the range of data you want to increase by 10%
4. Paste Special: (CTRL+ALT+V) to open the Paste Special dialog box and choose ‘Values’ and ‘Multiply’ and click OK.
   
   
5. And, Bob’s your Uncle! Now all of your values are 10% higher and by selecting ‘Paste Values’ I have kept my formatting.
   
   
6. Bonus tip – you can reduce the values by multiplying by less than 1 or change the sign by multiplying by -1.

Tip # 3 Fill Series

Let’s say we wanted to quickly insert the numbers 1 to 100 down a column. This is easy with Fill Series.

1. Enter a 1 in the cell where you want your numbers to start.
2. With the cell selected click on the ‘Fill’ icon in the Home tab of the Ribbon.
   
   

 

3. Select ‘Columns’ and enter 100 in the ‘Stop value’ field. Click OK.
   
   

 

4. Now you have the numbers 1 to 100 in a column. Piece of cake!

If you haven’t heard the expressions ‘Bob’s your Uncle’, or ‘Piece of cake’ click the links for an explanation 🙂

Thanks to Isaac for sharing his tips.

Dr. Isaac Gottlieb is a professor at Temple University in Philadelphia. Over 25,000 students and professionals have taken their Excel workshop with Dr. Gottlieb over the last 15 years. He taught this class at Columbia, NYU and other universities as well as in many corporations. He has written a book “Next Generation Excel: Modeling in Excel for Analysts and MBAs” - Wiley Finance. Dr. Gottlieb has 20 years industrial experience in addition to his academic background.

Vote for Isaac

If you’d like to vote for Isaac's tips (in X-factor voting style) use the buttons below to Like this on Facebook, Tweet about it on Twitter, +1 it on Google, Share it on LinkedIn, or leave a comment….or all of the above 🙂

Share Your Knowledge

Do you have a tip or trick you'd like to share? I'd love you to email it to me with an example and explanation and share your knowledge.