91 Tips for Calculating with Microsoft Excel - Improve Your Excel Calculation Skills in No Time with These Short, Simple Tutorials

Calculating in Excel

Want to find out some quick and easy tips for calculating in Excel? Check out these short, simple tutorials that come complete with screenshots and specific examples to help illustrate the concepts being discussed.

Learn everything from how to copy a formula that keeps certain cell references fixed to how to create a Craps Simulator in Excel and more. Also, discover valuable shortcuts that can improve productivity and literally save hours when preparing a major report, either for business or personal use.

Table of Contents

• Copy a Formula That Contains Relative References
• Copy a Formula While Keeping One Reference Fixed
• Create a Multiplication Table
• Calculate a Sales Commission
• Simplify Entry of Dollar Signs in Formulas
• Learn R1C1 Referencing to Understand Formula Copying
• Create Easier-to-Understand Formulas with Named Ranges
• Use Named Constants to Store Numbers
• Build a Formula Using Labels Instead of Cell Addresses
• Use Natural Language Formulas to Refer to the Current Row
• Assign a Formula to a Name
• Total Without Using a Formula
• Count, Average, etc Without Using a Formula
• Add Two Columns Without Using Formulas
• How to Calculate Sales over Quota
• How to Join Two Text Columns
• How to Sort on One Segment of an Account ID
• How to Isolate the Center Portion of an Account ID
• How to Isolate Everything Before a Dash in a Column by Using Functions
• How to Use Functions to Isolate Everything After a Dash in a Column
• How to Use Functions to Isolate Everything After the Second Dash in a Column
• How to Separate a Part Number into Three Columns
• Avoid #REF! Errors When Deleting Columns
• Create Random Numbers
• Create Random Numbers to Sequence a Class of Students
• Play Dice Games with Excel
• Play Bunco with Excel
• Play Craps with Excel
• Create Random Letters
• Convert Numbers to Text
• Calculate a Loan Payment
• Calculate Many Scenarios for Loan Payments
• Get Help on Any Function While Entering a Formula
• Discover New Functions Using the ƒx Button
• Three Methods of Entering Formulas
• Use AutoSum to Quickly Enter a Total Formula
• AutoSum Doesn’t Always Predict My Data Correctly
• Use AutoSum Button to Enter Averages, Min, Max, and Count
• The Count Option of the AutoSum Doesn’t Appear to Work
• Automatically Number a List of Employees 212 Rank Scores
• Sorting with a Formula
• Rank a List Without Ties
• Add Comments to a Formula
• Calculate a Moving Average
• Calculate a Trendline Forecast
• Build a Model to Predict Sales Based on Multiple Regression
• Use F9 in Formula Bar to Test a Formula
• Quick Calculator
• When Entering a Formula, You Get the Formula Instead of the Result
• Calculate a Percentage of Total
• Calculate a Running Percentage of Total
• Use ^ Sign for Exponent
• Raise a Number to a Fraction to Find the Square or Third Root
• Calculate a Growth Rate
• Find the Area of a Circle
• Figure out Lottery Probability
• Help Your Kids with Their Math
• Measure the Accuracy of a Sales Forecast
• Round Prices to Next Highest $5
• Why Is This Price Showing $2785000001 Cents?
• You Change a Cell in Excel but the Formulas Do Not Calculate
• Use Parentheses to Control Order of Calculations
• Before Deleting a Cell, Find out if Other Cells Rely on It
• Navigate to Each Precedent
• Formula Auditing
• How Is This Cell Calculated?
• Total Minutes That Exceed an Hour
• Convert Text to Minutes and Seconds
• Convert Text to Hours, Minutes, and Seconds
• Convert Times From H:MM to M:SS
• Display Dates as Months
• Group Dates by Month
• Calculate Last Day of Month
• Create a Timesheet That Can Total over 24 Hours
• Find Which Customers Are in an Existing List
• Use VLOOKUP to Find Which Customers Are in an Existing List
• Match Customers Using VLOOKUP
• Watch for Duplicates When Using VLOOKUP
• Count Records That Match a Criteria
• Build a Table That Will Count by Criteria
• Build a Summary Table to Place Employees in Age Bands
• Total Revenue from Rows that Match a Criterion
• Use Conditional Wizard to Help with SUMIF
• Create a CSE Formula to Build a Super Formula
• Learn to Use Boolean Logic Facts to Simplify Logic
• Replace IF Function with Boolean Logic
• Test for Two Conditions in a Sum
• Can the Results of a Formula Be Used in COUNTIF?
• Back into an Answer Using Goal Seek
• Protect Cells with Formulas

More Excel Tips

The Calculating with Excel series is actually Part 2 of a much larger collection of Excel tips written by Bill Jelen, also known as Mr. Excel. You may also be interested in the following groups that make up the other parts of the entire collection.

• Part 1: The Excel Environment – The first part of Mr. Excel’s popular series takes a look at all the different ways you can customize the Excel interface, making your workflow process more productive and ultra efficient. This group of tutorials also includes printing tips and advice on how to use Excel’s automated features.

• Part 2: Calculating with Excel – Want to learn how to take full advantage of Excel’s calculation features? Check out this set of 91 tips that cover everything from calculating multiple loan payment scenarios to playing dice games, like Craps, in Excel.

• Part 3: Wrangling Data – Learn more about the data organization and analysis tools found in Excel, including Pivot Table tricks and What-If Analyses.

• Part 4: Formatting Excel – You can use Excel’s formatting features for much more than just making your final spreadsheet “look good.” You can also utilize these tools to add new data to existing charts, recognize exceptions in large reports, and much more.