Home Archives for Excel Basics
April 5, 2011 Leave a Comment

Learn How Excel Calculates Formulas

Order of Calculations in Excel

Excel Calculation Order

Have you ever written a Formula in Excel only to receive a result that was different from the one you expected? Most Excel users have shared that experience from time to time! Excel is smart and fast. However, it can not read your mind.

In this lesson, I show you how Excel evaluates and calculates a formula. Once you understand “Excel’s perspective” of the formula, you will know how to correctly write the formula so that Excel will produce the result that you intended. In other words, to see the formula from “your perspective!

Order of Precedence

Excel performs calculations in formulas moving from left to right in this order:

  1. Performs Multiplication (*) and Division (/)
  2. Goes back and performs Addition (+) and Subtraction (-)

This is the “key” to consistently getting Excel to produce the results that you intended. No, you can’t get Excel to make you an “instant millionaire” or do anything illegal. However, understanding how to write formulas correctly – so that you control the order of calculation in Excel – is the “secret sauce!”

Control the Order of Calculation in Excel This formula: =5+15*2 results in 35. If you were expecting the result to be 40, then write the formula as (5+15)*2. In other words, take the “result” of 5+15 or 20 and multiply this by 2 to give me 40.By using parentheses (5+15) you take control over the order of precedence that Excel uses. Help Excel to see the formula from your perspective!

Free Chart of Excel Formula Operators

I have published  a chart – “Using Operators in Excel Formulas” – To get your free copy as a PDF, click on the link below:

CR – Using Operators in Excel Formulas

Explore My Free Excel Video Lessons

Follow this link to my Index of Free Excel Video Tutorials

Watch This Video Tutorial In High Definition

This link will take you to the DannyRocksExcels video on YouTube

Share and Enjoy:
  • Add to favorites
  • Facebook
  • Twitter
  • Technorati
  • Print
  • email
  • Digg
  • StumbleUpon
  • del.icio.us
  • Yahoo! Buzz
  • Google Bookmarks
  • Orkut
  • SphereIt
  • Sphinn
Learn How Excel Calculates Formulas [ 5:18 ] Play Now | Play in Popup | Download (887)
Link to this post!
Filed Under: Excel Basics, Excel Formulas, Excel Training, Podcast Tips & Timesavers Tagged: , , , , ,
January 1, 2011 Leave a Comment

The 10 Most Watched Videos at The Company Rocks During 2010

Reminder Note

Reminder Note

I like to look back to review the results of the past year. Which posts, which videos were the most popular, which videos were downloaded most often? As I look at this list of 10 videos, the clear winner – by category – is merging data from multiple worksheets. Three of the top 10 videos that were watch the most fall into this category!

Here is a listing of the 10 Video Lessons that were watched most frequently on my website – www.thecompanyrocks.com – during 2010:

1- Summarize Multiple Excel Worksheets – Consolidate by Position

2- Build an Accounts Receivable Aging Report

3- Use Pivot Tables to Summarize by Year, Quarter and Month

4- Simplify Data Lookups in Excel

5- Perform Break-Even Analysis with Excel’s Goal Seek Tool

6- How to Display Numbers During a PowerPoint Presentation

7- Make Excel Data Come Alive with Visualization Tools

8-  Excel 2003 Basics – Data Entry

9- How to Merge Multiple Excel Workbooks into a Master Budget

10- Consolidate Data from Multiple Excel Worksheets – Part 2 – by Category

If you enjoy these videos, you will enjoy my DVD-ROMs, “The 50 Best Tips for Excel 2007” and “The 50 Best Tips for PowerPoint 2007.” You can use my secure shopping cart to purchase them now.

I have reduced the purchase price of my Individual DVD-ROMs to $29.97 USD. You do not need a coupon to receive this special price. Simply, go to my online store – http://shop.thecompanyrocks.com

You can also watch my videos on iTunes. Click here to go to my Video Podcast, “Danny Rocks Tips and Timesavers” at the iTunes Store.

Learn how to “Master Excel in Minutes – Not Months!”

My most recent videos can be viewed in High Definition, Full Screen Mode on my YouTube Channel – DannyRocksExcels

Share and Enjoy:
  • Add to favorites
  • Facebook
  • Twitter
  • Technorati
  • Print
  • email
  • Digg
  • StumbleUpon
  • del.icio.us
  • Yahoo! Buzz
  • Google Bookmarks
  • Orkut
  • SphereIt
  • Sphinn
Link to this post!
Filed Under: Consolidate Excel Data, Excel Basics, Excel Financial Tools, Excel Training, Goal Seek in Excel, Lookup Functions in Excel, Most Viewed Excel Videos, Pivot Tables, Powerpoint Techniques Tagged: , , , , , , , ,
December 9, 2010 2 Comments

How to Work with Dates in Excel

The majority of the questions that I receive from viewers center on how to use and format DATES & TIMES in Excel. So, I have decided to create a series of lessons to show you how to work with dates and times in Excel.  This is part one in a continuing series of free Excel Video Training Lessons.

50 Best Tips for Excel 2007

Excel Tips

Learn How to Work with Dates

  • Use a Keyboard Shortcut to enter the current date in a cell – to “time stamp” the date.
  • Use the TODAY() Function to enter a “dynamic” date function in a cell – so that your date always reflects the current date.
  • Distinguish between how Excel “stores” a date – as a serial number – and how Excel formats a date – how you choose to format a date.
  • Tell if a date was entered correctly – as a number – or incorrectly – as “text” by seeing how it is aligned in the cell (right or left).
  • See why a fraction could be recognized as a date – and how to correct this “got’cha” step!
  • How to create a series of dates in your spreadsheet.

In my experience, the majority of “Excel problems” are based around incorrect date and time entries. So, whether you are new to Excel or a veteran who gets frustrated with Time and Date calculations, this lesson is for you.

If nothing else, you will pick up some great Keyboard Shortcuts in this lesson!

Follow this link to watch this video in High Definition, Full Screen Mode on my YouTube Channel – DannyRocksExcels

Learn how to “Master Excel in Minutes – Not Months!”

Share and Enjoy:
  • Add to favorites
  • Facebook
  • Twitter
  • Technorati
  • Print
  • email
  • Digg
  • StumbleUpon
  • del.icio.us
  • Yahoo! Buzz
  • Google Bookmarks
  • Orkut
  • SphereIt
  • Sphinn
How to Work with Dates in Excel [ 8:55 ] Play Now | Play in Popup | Download (796)
Link to this post!
Filed Under: Excel Basics, Excel Date and Time Functions, Excel Training, Podcast Tips & Timesavers Tagged: , , , , , ,
October 21, 2010 Leave a Comment

Learn How to Protect Your Excel Worksheets

Protecting your Excel worksheet is a two-step process. Understand that, by Default, all cells in the worksheet are “locked.”

1) Your first step is to select the input cells that you want your end-user to be able to type in or edit and “unlock” them. That is the “got’cha” step.

2) Next, you go to the Review tab on the Ribbon – in Excel 2007 – and apply the Protection options that you want to be in place for the worksheet.

I like to apply protection to any Excel worksheet that I intend to distribute to end-users. I want them to be able to interact with the data, but I do not want them to be able to change the formulas and formatting that I worked hard to put in place.

Understanding the need to “unlock” input cells while “locking” formula cells is the key to successfully protecting your Excel worksheets. I walk you through two examples in this short video lesson. In addition, I show you how to protect your entire workbook – with or without a password.

This is one of the tips that I include on my DVD, “The 50 Best Tips for Excel 2007.” Click here to purchase it today!

Learn how you can “Master Excel in Minutes – Not Months!”

Share and Enjoy:
  • Add to favorites
  • Facebook
  • Twitter
  • Technorati
  • Print
  • email
  • Digg
  • StumbleUpon
  • del.icio.us
  • Yahoo! Buzz
  • Google Bookmarks
  • Orkut
  • SphereIt
  • Sphinn
Learn How to Protect Your Excel Worksheets [ 6:31 ] Play Now | Play in Popup | Download (561)
Link to this post!
Filed Under: Excel Basics, Excel Training, Podcast Tips & Timesavers, Workbooks and Worksheets in Excel Tagged: , ,
September 13, 2010 Leave a Comment

When to Use Absolute, Relative & Mixed Cell References in Formulas

In my experience, I find that many Excel users get easily confused when it comes time to use an Absolute or a Mixed Cell Reference in a formula. Let me demonstrate how and when to use the proper cell references, so that you always get the correct result.

Here is what each type of cell reference looks like in a formula:

  • Relative Cell Reference – =A2*B2 when copied down one row becomes = A3*B3
  • Absolute Cell Reference – =A2*$B$2 when copied down one row becomes =A3*$B$2 (Notice the $B$2 Absolute cell reference in the formula)
  • Mixed Cell Reference – =$A2*B$3 when copied down one row becomes = $A3*B$3 and when copied one cell to the right becomes =$A*C$3 (Notice how one part of the cell reference is relative – it moves; and the other part of the reference is Absolute – it remains fixed in place)

Learn how to “Master Excel in Minutes – Not Months!”

Share and Enjoy:
  • Add to favorites
  • Facebook
  • Twitter
  • Technorati
  • Print
  • email
  • Digg
  • StumbleUpon
  • del.icio.us
  • Yahoo! Buzz
  • Google Bookmarks
  • Orkut
  • SphereIt
  • Sphinn
How to Use Absolute, Relative & Mixed Cell References in Formulas [ 5:09 ] Play Now | Play in Popup | Download (788)
Link to this post!
Filed Under: Excel Basics, Excel Formulas, Excel Training, Podcast Tips & Timesavers Tagged: , , , , , ,
May 15, 2010 Leave a Comment

I Have Updated My Index of Excel Video Lessons

As part of the process of reorganizing my website, I have updated my “Index of Excel Video Lessons” page. The content of this page will be updated as I add each new Excel Video Training Lesson. So, I suggest that you click here to go to the Video Index and then save the page as one of your “Favorites” if you want to learn how to really get the most out of Excel or to find a quick answer when you are using Excel.

On the Index Page you can see an organized listing of my Excel Videos by Category – e.g. Formulas, Formatting, Pivot Tables “What-if” Analysis, etc. Each lesson is “hyperlinked” so that you can just click on the topic to go directly to the lesson that you are interested in.

I indicate in the title if the lesson is specific to Excel 2007.  All my new Excel video lessons are created using Excel 2007.

If there is a topic that you would like me to cover, send me an e-mail message – danny@thecompanyrocks.com

If you enjoy my video instruction, I encourage you to purchase my DVD, “The 50 Best Tips for Excel 2007.” The DVD contains over 5 1/2 hours of Tips, Tricks & Techniques to help you to master the program – and to save a considerable amount of time while working in Excel! Click here to enter my secure shopping site.

Thank you for your support and encouragement!

Danny Rocks

Share and Enjoy:
  • Add to favorites
  • Facebook
  • Twitter
  • Technorati
  • Print
  • email
  • Digg
  • StumbleUpon
  • del.icio.us
  • Yahoo! Buzz
  • Google Bookmarks
  • Orkut
  • SphereIt
  • Sphinn
Link to this post!
Filed Under: COUNTIF and SUMIF Functions, Everyday Calculations in Excel, Excel Basics, Excel Data Tables and Lists, Excel Date and Time Functions, Excel Financial Tools, Excel Formulas, Excel Functions, Excel Training, Import Data into Excel, Lookup Functions in Excel, MS Excel 2007 DVD, MS Excel Tips, MS Excel Training, Online Store for The Company Rocks, Pivot Tables, Text Functions, Tips and Time Savers, Training, Video Training Courses, What-if Analysis Tagged: , , ,
April 9, 2009 Leave a Comment

Save Time and Maintain Consistency When Entering Data

This is every company’s nightmare: You have a list of Customer Names that are entered in multiple worksheets by multiple employees. The (nightmare) result – you have multiple misspellings of the Customer Names! This is GIGO (“Garbage In, Garbage Out”). This is a mess!

This is preventable!

In this Excel Training Video I demonstrate three tips to save you time and help you to maintain consistency when entering long text labels into your worksheets. The Tips:

  1. Use Data Validation – Choose from a List
  2. Use a Named Constant  – you type =”Shortcut” and get the Customer Name spelled out correctly
  3. Use MS Office’s AutoCorrect Option – Create your “Replace and Replace With” in Excel and you can use it in Word and PowerPoint

Here are the steps to follow in this lesson:

  1. Enter the list of Customer Names in a worksheet. Highlight the list and type a NAME for this range into the Name Box. Remember to begin your Name with a letter and you can not use a Space in the Name.
  2. Tip # 1 – Use Data Validation – Allow – List. You can watch my lesson on Data Validation.
  3. Tip # 2 – Create a Named Constant. You can watch my lesson on Named Cells, Ranges & Constants.
  4. Tip # 3 – Use MS Office’s AutoCorrect Option – Go to Tools – AutoCorrect Options in any MS Office Program. For the “Replace” entry, type in your “Shortcut.” I like to make my 1st character an”_” so that it is easy to find, edit or replace. For the “Replace with” type in the full Customer Name. Remember to ht the SPACEBAR when you use this in Word & PowerPoint!

It is easy to find the Excel Training Video you want to view – Search my Index of Excel Topics

New! Danny’s DVD Training Series, “The 50 Best Tips for …” is now available at the online store for The Company Rocks

Share and Enjoy:
  • Add to favorites
  • Facebook
  • Twitter
  • Technorati
  • Print
  • email
  • Digg
  • StumbleUpon
  • del.icio.us
  • Yahoo! Buzz
  • Google Bookmarks
  • Orkut
  • SphereIt
  • Sphinn
Link to this post!
Filed Under: Excel Basics, Excel Training Tagged: , , , , , ,
September 5, 2008 2 Comments

Excel 2003 Basics – Data Entry

A viewer wrote in to request a few videos that cover “The Basics” in Excel 2003. In this video lesson, I cover the basics of data entry.

Even if you have been using Excel for a number of years, I guarantee that you will pick up one or two tips in this video that will save you time and make you more productive.

Here are the steps to follow in this video lesson:

  1. Excel aligns “text” entries to the left-side of the cell. “Numeric” entries (including Dates & Times) are aligned to the right-side of the cell.
  2. All calculations in Excel MUST start with the =sign. This includes Formulas and Functions. Write formulas that refer to other cells. (Avoid writing formulas that only use constant values.)
  3. When we input data in a cell and press the ENTER key, Excel accepts the entry and makes the cell that is down one row in the column the “Active Cell.”
  4. To input data in the cells of the same row – and continue to use the ENTER key – first select the range of cells in the row.
  5. You can also select a range of cells in adjacent rows and columns. Excel goes down the cells in the column first and then moves automatically to the top row in the next column in your selection.

News! My DVD, “The 50 Best Tips for Excel 2007” is now availabe to purchase. I invite you to visit my online bookstore for more details.

Find the Excel Video Lesson that you want – Index to all Excel Topics

Share and Enjoy:
  • Add to favorites
  • Facebook
  • Twitter
  • Technorati
  • Print
  • email
  • Digg
  • StumbleUpon
  • del.icio.us
  • Yahoo! Buzz
  • Google Bookmarks
  • Orkut
  • SphereIt
  • Sphinn
Link to this post!
Filed Under: Excel Basics, Excel Training Tagged: , , , ,
« Previous Page