• Skip to main content
  • Skip to header right navigation
  • Skip to site footer

My Online Training Hub

Learn Dashboards, Excel, Power BI, Power Query, Power Pivot

  • Courses
  • Pricing
    • Free Courses
    • Power BI Course
    • Excel Power Query Course
    • Power Pivot and DAX Course
    • Excel Dashboard Course
    • Excel PivotTable Course โ€“ Quick Start
    • Advanced Excel Formulas Course
    • Excel Expert Advanced Excel Training
    • Excel Tables Course
    • Excel, Word, Outlook
    • Financial Modelling Course
    • Excel PivotTable Course
    • Excel for Customer Service Professionals
    • Excel for Operations Management Course
    • Excel for Decision Making Under Uncertainty Course
    • Excel for Finance Course
    • Excel Analysis ToolPak Course
    • Multi-User Pricing
  • Resources
    • Free Downloads
    • Excel Functions Explained
    • Excel Formulas
    • Excel Add-ins
    • IF Function
      • Excel IF Statement Explained
      • Excel IF AND OR Functions
      • IF Formula Builder
    • Time & Dates in Excel
      • Excel Date & Time
      • Calculating Time in Excel
      • Excel Time Calculation Tricks
      • Excel Date and Time Formatting
    • Excel Keyboard Shortcuts
    • Excel Custom Number Format Guide
    • Pivot Tables Guide
    • VLOOKUP Guide
    • ALT Codes
    • Excel VBA & Macros
    • Excel User Forms
    • VBA String Functions
  • Members
    • Login
  • Blog
  • Excel Webinars
  • Excel Forum
    • Register as Forum Member
  • Login

Excel Factor 13 Handy Tips & Tricks

You are here: Home / Excel / Excel Factor 13 Handy Tips & Tricks
Excel Factor 13 Handy Tips & Tricks
September 5, 2012 by Mynda Treacy
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.

Excel Format Selected Words or Values

  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:

    Excel Format Selected Words or Values
  4. Click the Options button and select Format for โ€˜Replace withโ€™:

    Excel Format Selected Words or Values
  5. This will open the Format dialog box where you can set your formatting:

    Excel Format Selected Words or Values
  6. Click OK once youโ€™re done and youโ€™ll be presented with a Preview. If youโ€™re happy press โ€˜Replace Allโ€™.

    Excel Format Selected Words or Values
  7. Voila. Note: this will format the entire cell containing your chosen word or character, as you can see in cell A1 below :

    Excel Format Selected Words or Values

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.

    Excel Format Selected Words or Values
  5. And, Bobโ€™s your Uncle! Now all of your values are 10% higher and by selecting โ€˜Paste Valuesโ€™ I have kept my formatting.

    Excel Format Selected Words or Values
  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.

    Excel Format Selected Words or Values
  3.  

  4. Select โ€˜Columnsโ€™ and enter 100 in the โ€˜Stop valueโ€™ field. Click OK.

    Excel Format Selected Words or Values
  5.  

  6. 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 GotliebDr. 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.

Excel Factor 13 Handy Tips & Tricks
Mynda Treacy

Microsoft MVP logo

AUTHOR Mynda Treacy Co-Founder / Owner at My Online Training Hub

CIMA qualified Accountant with over 25 years experience in roles such as Global IT Financial Controller for investment banking firms Barclays Capital and NatWest Markets.

Mynda has been awarded Microsoft MVP status every year since 2014 for her expertise and contributions to educating people about Microsoft Excel.

Mynda teaches several courses here at MOTH including Excel Expert, Excel Dashboards, Power BI, Power Query and Power Pivot.

More Excel_Factor Posts

Excel Factor Voting Roundup

Excel Factor Voting Roundup

Excel Factor 21 Hyperlink Triptych

Excel Factor 21 Hyperlink Triptych

Tips and tricks with hyperlinks to make them dynamically update as the selection in the sheet changes. Sample workbook available.
Excel Factor 20 Custom Number Format Disguise

Excel Factor 20 Custom Number Format Disguise

Excel Factor 19 Dynamic Dependent Data Validation

Excel Factor 19 Dynamic Dependent Data Validation

Excel Factor 18 Dynamic Hyperlinks and the Magic Hash

Excel Factor 18 Dynamic Hyperlinks and the Magic Hash

The Excel HYPERLINK function creates links to places inside or outside of your Excel file. I'll show you a shortcut for creating them that few people know.
Excel Factor 17 Lookup and Return Multiple Matches

Excel Factor 17 Lookup and Return Multiple Matches

Excel Factor 16 Dynamic Lookup

Excel Factor 16 Dynamic Lookup

Excel Factor 15 The Lazy Lookup

Excel Factor 15 The Lazy Lookup

Excel Factor 14 Interleave Data from Two Columns into One

Excel Factor 14 Interleave Data from Two Columns into One

Excel Factor 12 Secret EVALUATE Function

Excel Factor 12 Secret EVALUATE Function

More Excel Posts

8 Excel in-built AI Tools

Built-in Excel AI Tools

Clean, analyze and visualize data with these Official Excel AI tools by Microsoft. Including formula writing, PivotTables, Charts and more.
excel date and time

Excel Date and Time

Everything you need to know about Excel date and time. Includes comprehensive workbook with every date and time function, plus PDF version.
10 mistakes to avoid when using excel formatting

10 Mistakes to Avoid With Excel Formatting

These are the Excel formatting habits that drive me crazy and what you should do instead to ensure you use formatting effectively.
linear regression

Excel Linear Regression

Excel linear regression is easy with the built-in tools. Use charts to plot linear regression or use the Data Analysis Toolpak.
speed up slow excel files

How to Improve Excel Performance

How to improve Excel performance and the various causes of slow Excel files so you can speed up Excel and avoid problems in future.
Securely Share Excel Files

Securely Share Excel Files

Securely share Excel files stored locally, on OneDrive or SharePoint. Prevent editing or downloading, specify who can open and edit the file.
excel check boxes

Interactive Excel Check Boxes

Excel check boxes are interactive elements you can link to formulas, charts, conditional formatting and more.
tips for working in multiple excel files

Hacks for Working in Multiple Excel Files

Awesome tips for navigating, arranging and working in multiple Excel files. Guaranteed to streamline your workflow and increase productivity.
chatgpt for excel

ChatGPT for Excel

Using ChatGPT for Excel can be hit and miss. Learn the best uses for ChatGPT to make your Excel life easier and what to avoid using it for.
excel templates

Where to Find Free Excel Templates

Where to find free Excel templates and how to create your own Excel templates. Using templates saves time and effort.


Category: ExcelTag: Excel_Factor
Previous Post:Excel Factor 12 Secret EVALUATE FunctionExcel Factor 12 Secret EVALUATE Function
Next Post:Excel Factor 14 Interleave Data from Two Columns into OneExcel Factor 14 Interleave Data from Two Columns into One

Reader Interactions

Comments

  1. Pearl

    May 20, 2016 at 12:52 am

    I love Excel tips but the title says “13” and there are three. Gave me a chuckle. Might want to change the title of this page.

    Reply
    • Mynda Treacy

      May 20, 2016 at 8:53 am

      ๐Ÿ™‚ I see your point. The title should read ‘Excel Factor 13 – Handy Tips & Tricks’. There are 12 other entries in the Excel Factor series before that one.

      Reply
  2. Juan

    November 7, 2013 at 9:00 pm

    Thank you very much, Dr. Gottlieb and Mynda for these wonderful tips. I love them all, specially the second one, it’s very practical and helpful.
    Warm regards, Juan

    Reply
    • Mynda Treacy

      November 7, 2013 at 9:01 pm

      You’re welcome, Juan ๐Ÿ™‚

      Reply
  3. CSP

    September 17, 2012 at 9:41 pm

    Thanks sooo much Mynda! Excel can be a pain at times! x

    Reply
    • Mynda Treacy

      September 18, 2012 at 6:41 pm

      You’re welcome ๐Ÿ™‚

      Reply
  4. Ravi Xavier

    September 12, 2012 at 1:41 pm

    The second one is very useful to me thank you for that.

    Reply
    • Mynda Treacy

      September 12, 2012 at 1:49 pm

      Great! Thanks Ravi.

      Reply
  5. David Kramer

    September 5, 2012 at 9:55 pm

    Hi Dr. Gottlieb,
    Thanks for the great, practical tips!
    David Kramer

    Reply
    • Mynda Treacy

      September 5, 2012 at 10:17 pm

      Cheers, David.

      Reply
  6. saran

    September 5, 2012 at 1:01 pm

    Nice tips Isaac.

    Normal excel users may not know the first tip.

    However, all the tips are useful.

    Regards,
    Saran

    Reply
    • Mynda Treacy

      September 5, 2012 at 1:44 pm

      Cheers, Saran ๐Ÿ™‚

      Reply

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Current ye@r *

Leave this field empty

Sidebar

More results...

office scripts course

Popular Content

  • 10 Common Excel Mistakes to Avoid
  • Top Excel Functions for Data Analysts
  • Secrets to Building Excel Dashboards in Less Than 15 Minutes
  • Pro Excel Formula Writing Tips
  • Hidden Excel Double-Click Shortcuts
  • Top 10 Intermediate Excel Functions
  • 5 Pro Excel Dashboard Design Tips
  • 5 Excel SUM Function Tricks
  • 239 Excel Keyboard Shortcuts

100 Excel Tips and Tricks eBook

Download Free Tips & Tricks

239 Excel Keyboard Shortcuts

Download Free PDF

mynda treacy microsoft mvpHi, I'm Mynda Treacy and I run MOTH with my husband, Phil. Through our blog, webinars, YouTube channel and courses we hope we can help you learn Excel, Power Pivot and DAX, Power Query, Power BI, and Excel Dashboards.

Blog Categories

  • Excel
  • Excel Charts
  • Excel Dashboard
  • Excel Formulas
  • Excel Office Scripts
  • Excel PivotTables
  • Excel Shortcuts
  • Excel VBA
  • General Tips
  • Online Training
  • Outlook
  • Power Apps
  • Power Automate
  • Power BI
  • Power Pivot
  • Power Query
microsoft mvp logo
trustpilot excellent rating
Secured by Sucuri Badge
MyOnlineTrainingHub on YouTube Mynda Treacy on Linked In Mynda Treacy on Instagram Mynda Treacy on Twitter Mynda Treacy on Pinterest MyOnlineTrainingHub on Facebook

Sign up to our newsletter and join over 400,000
others who learn Excel and Power BI with us.

 

Company

  • About My Online Training Hub
  • Disclosure Statement
  • Frequently Asked Questions
  • Guarantee
  • Privacy Policy
  • Terms & Conditions
  • Testimonials
  • Become an Affiliate
  • Sponsor Our Newsletter

Support

  • Contact
  • Forum
  • Helpdesk – For Technical Issues

Copyright © 2023 ยท My Online Training Hub ยท All Rights Reserved. Microsoft and the Microsoft Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries. Product names, logos, brands, and other trademarks featured or referred to within this website are the property of their respective trademark holders.