Home Archives for MS Excel Training
February 19, 2012 Leave a Comment

How to Use Named Ranges in Existing Excel Formulas

Paste Named Ranges in Formula

Paste Named Ranges in Formula

I am a strong proponent for using Named Cell Ranges when creating Excel Formulas. But what if you have already created formulas – formulas that use cell references. How do you insert or apply a newly created named range into an existing Excel Formula?

Simple answer – watch this short video to see me demonstrate how this is done.

Better answer:

Follow These Steps

  1. Activate in-cell formula editing by either double-clicking the formula cell or using the Keyboard Shortcut F2.
  2. Highlight the cell reference that you wish to replace with a named range.
  3. Choose the Name from the “Use in Formulas” drop-down menu. You can also use the F3 Keyboard Shortcut to open the Paste Names Dialog Box.
  4. Repeat these steps to complete replacing additional cell references with named cell ranges.

Learn to Get the Most from Excel

On my DVD-ROM, “The 50 Best Tips for Excel 2007,” I offer 5 1/2 hours of video instruction. You will be amazed at how much more you can get out of Excel when you invest in this valuable resource. I invite you to visit my secure, online shopping website to learn more about the resources that I offer.

Watch Video in High Definition

You can view this Excel Tutorial in High Definition on my YouTube Channel – DannyRocksExcels

Play Tutorial Now

Link to this post!
Filed Under: Excel Formulas, Excel Training, Names for Cells and Ranges Tagged: , , , , , ,
February 10, 2012 Leave a Comment

3 Reasons to Create and Use Named Ranges in Excel Formulas

Use Named Ranges in Formulas

Use Named Ranges in Formulas

In my opinion, there are three reasons to use Named Range references in Excel Formulas:

  1. They are easier to write. Particularly, if you are referencing cells in another worksheet.
  2. They are easier to remember. Using =Sales – Cost of Goods Sold to determine Gross Profit vs. =A1 – B1
  3. They are easier to explain. Especially, if you are sending an Excel Workbook to a client or a colleague.

Creating Named Ranges in Excel

In this tutorial, I demonstrate two methods for creating a named range:

  1. Select the cells in the range and then type the name in the “Name Box” in the Upper Left Corner of the worksheet.
  2. Select both the cell with the “Name” and the adjacent cells for the range. Then use the Keyboard Shortcut Ctrl + Shit + F3 to open the Create Names from Selection Dialog Box

Remember that all Named Ranges MUST begin with a Letter or an Underscore and they CANNOT contain any Spaces!

Paste Named Ranges into Formulas

If you are using Excel 2007 or Excel 2010, you can take advantage of Formula AutoComplete to quickly and accurately include named ranges in your formulas. In ALL versions of Excel you can use the F3 Keyboard Shortcut to open the Paste Names Dialog Box and select the named range that you wish to paste into your formula.

Additional Resources for Excel

I invite you to visit my secure online shopping website – http://shop.thecompanyrocks.com – where you can preview all of the resources that I offer you.

Watch Tutorial in High Definition

Follow this link to watch my Excel Video in High Definition. My YouTube Channel – DannyRocksExcels – has received over 1 million views!

Watch Video Now

Link to this post!
Filed Under: Excel Formulas, Excel Functions, Excel Training, Names for Cells and Ranges, YouTube Videos by Danny Rocks Tagged: , , , , , , , ,
February 2, 2012 Leave a Comment

The Basics for Creating and Copying Formulas and Functions in Excel

Relative and Absolute Cell References

Relative and Absolute Cell References

Formulas and Functions are the “core elements” of Excel. It is vital that you have a solid grounding in understanding how formulas and functions work; especially when you need to copy and paste them into other cells.

Relative and Absolute Cell References

When you use Relative Cell References – the default setting in Excel – the Row numbers and Column letters adjust automatically when you copy and paste a formula.

There are, however, situations where you need to “freeze in place” part of an Excel Formula. For example, you need to “freeze” or use an Absolute Cell Reference to the cell with “Total Sales,” when creating and copying a formula to determine Product Sales as a Percentage of Total Sales.

Copying Excel Formulas

In this tutorial, I demonstrate two methods for copying and pasting formulas and functions:

  • Standard Practice is to select the cell with the formula and use the Ctrl + C Keyboard Shortcut to place the formula cell on the Excel clipboard. Then, after selecting the destination cell(s), use the Ctrl + V Keyboard Shortcut to paste the formula in the new location(s)
  • AutoFill Tool.If you are copying the formula cell into adjacent cells, use the AutoFill tool to do this quickly and accurately!

Tips that You May Not Know

In my experience, many Excel veterans are not familiar with these tips and tricks which I demonstrate in this tutorial:

  • The Ctrl + ~ (tilde) Keyboard Shortcut to “toggle” the Show Formulas view for the active Excel Worksheet.
  • The Alt + Enter Keyboard Shortcut to automatically use the =SUM() Function – for adjacent cells.
  • The F4 Key to automatically add Absolute Cell Reference when creating or editing a formula. For example, converts A1 to $A$1.

Learn More Excel Tips and Tricks

50 Best Tips for Excel 2007

50 Best Tips DVD-ROM

If you enjoy the tips and techniques that I demonstrate in this lesson, then you will really benefit from purchasing my best-selling DVD-ROM, “The 50 Best Tips for Excel 2007.” You can learn more about the resources that I offer by visiting my secure online shopping website – http://shop.thecompanyrocks.com

Watch Tutorial in High Definition

Follow this link to view this Excel tutorial in High Definition on my YouTube Channel – DannyRocksExcels

Play Video Now

Link to this post!
Filed Under: Excel Basics, Excel Formulas, Excel Functions, Excel Training, YouTube Videos by Danny Rocks Tagged: , , , , , , , , , , , , , , , ,
January 30, 2012 Leave a Comment

How to use Wrap Text and Merge Cells Formatting to Improve Excel Worksheet Appearance

Wrap Text for Labels

Wrap Text for Labels

In this tutorial – the second in my series explaining how a worksheet, that my accountant friend Alan Friedman created, works – I focus on improving the formatting for the column labels and other descriptive text.

Wrap Text for Labels

In my experience, I find that many accountants and other financial managers have not mastered the basics of formatting an Excel Worksheet. It seems that they are still using “old fashioned IBM typewriter tricks” when it comes time to create the labels for the columns of data in their spreadsheet.

Case in Point: Using two or more vertical cells to type “Cost of” (Return key on Computer) “Goods Sold.” This “old fashioned” process will lead to multiple problems. For example, improper sorting and filtering of data.

In this tutorial, I demonstrate how to use the Wrap Text command to format a single-cell text label. You will also learn how to use the Keyboard Shortcut Alt + Enter to control where your text “breaks” when wrapping text.

Merge Cells Across Columns

When you have a long, descriptive text – e.g. Your Company Name – it is best to type this into a single cell and then use the Merge Cells Command. Now, when you double-click between Column Letters to automatically expand the width of a column, only the “um-merged” labels in the column are extended in width.

Additional Resources for Tutorial

You can learn “The 50 Best Tips for Excel 2007” on my DVD-ROM. Visit – http://shop.thecompanyrocks.com/50-best-tips-for-excel-2007/ – to learn more about this.

Click on this link to download the Excel Worksheet – created by Alan Friedman – that I use in this lesson.

Watch Tutorial in High Definition

Follow this link to watch this tutorial in High Definition on my YouTube Channel – DannyRocksExcels

View Tutorial Now

Link to this post!
Filed Under: Excel Financial Tools, Excel Training, Format Cells in Excel, YouTube Videos by Danny Rocks Tagged: , , , , , , ,
January 29, 2012 Leave a Comment

How to Apply Alternate Row Shading Using Conditional Formatting in Excel

Alternate Row Shading

Alternate Row Shading w/ Conditional Formatting

This is the first tutorial in a new series. In collaboration with my good friend, Alan Friedman – CPA and partner with the firm Friedman, Kannenberg & Co., PC – I am using Alan’s Financial worksheets to demonstrate how to get the most out of Excel. In this lesson, I show you how to apply Alternate Row Shading – aka “Green Bar Shading” – to a Year-end Inventory Analysis Excel worksheet.

Conditional Formatting in Excel

In this lesson, I create a new rule that uses a formula to determine the “condition” – or criteria – that must be met in order to apply the “formatting” to my selection of cells. The “key” to understanding this rule: The formula that you use for this “rule” must return either TRUE or FALSE. The cells – in the selection – that evaluate to TRUE will receive the special formatting – a background shading.

Extending Range of Cells to Receive Conditional Formatting

In general, you can use Excel’s AutoFill Handle to extend Conditional Formatting to adjacent cells. If, however, you want to apply the “Rule Using a Formula” to non-adjacent cells, first copy a cell that contains the Conditional Formatting. Then, use Paste Special – Formatting to apply this rule to your new selection of cells.

Resources for This Tutorial

I invite you to visit my secure online shopping website – http://shop.thecompanyrocks.com – to preview all of the resources that I offer to you.

Please visit the Friedman, Kanneberg & Co. website to download the worksheet used in this series of lessons – and other seminar handouts.

 

Watch Tutorial in High Definition

Follow this link to watch this Excel Tutorial in High Definition on my YouTube Channel – DannyRocksExcels

Play Video Now

Link to this post!
Filed Under: Conditional Formatting, Excel Financial Tools, Excel Training, Format Cells in Excel, YouTube Videos by Danny Rocks Tagged: , , , , , , , , , ,
January 24, 2012 3 Comments

How to Create Custom Views of a Worksheet in Excel

Custom Views in Excel

Custom Excel Views

This is Part 2 in my series of video tutorials demonstrating the Commands found on the View Tab of the Excel Ribbon. Building on the concepts that I demonstrated in Part 1 (“How to Freeze Row and Column Labels While Scrolling in Excel”), I now show you how to save these settings as a Custom View.

Create a Custom View in Excel

  1. Display the settings that you wish to save as a Custom View – e.g. Changing the ZOOM Level of Magnification, Freezing Rows or Columns, etc.
  2. From the View Tab on the Ribbon, choose the Custom Views Command.
  3. In Custom Views Dialog Box, click Add; Give your View a Name and Click OK.
  4. Remember to Save your Excel Workbook. To test your custom view, I recommend that you revert to your normal or default view and save that version. Then close the workbook and reopen it. Now, it will display the last view displayed when you saved the workbook. Click on the Custom Views Command and select the Custom View that you recently added; the Custom Settings will now display.
  5. Add – and Save – additional Custom Views.

Custom Views are Worksheet Level Views

When you create a Custom View, it only applies to the Excel Worksheet where you created it. In fact, while you “Show” a Custom View, all other Worksheets in the Active Workbook are NOT available.

Excel Tables and Custom Views

 There is one “gotcha” with Custom Views. If you have formatted a data set as an Excel Table on ANY worksheet in the workbook, ALL Custom Views are blocked out. Watch  this video to see how to “work around” this roadblock.

Online Shopping at The Company Rocks

I invite you to visit my secure online shopping website – http://shop.thecompanyrocks.com – to preview all of the resources that I offer you.

Watch Tutorial in High Definition

Follow this link to view this Excel Tutorial in High Definition on my YouTube Channel – DannyRocksExcels

Play Video Now


 

 

Link to this post!
Filed Under: Excel Basics, Excel Training, MS Excel Training, Workbooks and Worksheets in Excel, YouTube Videos by Danny Rocks Tagged: , , , , ,
October 26, 2011 Leave a Comment

Master Excel Pivot Tables in 90 Minutes: Announcing My New Extended Length Video Tutorials!

Master Pivot Tables in Minutes

Master Pivot Tables in Minutes

I am proud to announce the publication of my new series of “Extended Length” video tutorials: “Master Excel Pivot Tables in 90 Minutes!”

Now, you can can get an in-depth video tutorial, so that you can Master Excel Pivot Tables with 90 minutes of video instruction. You also receive the actual Excel Workbook file that I used while filming the video lessons as well as a printed step-by-step instructional manual.

What Makes this Product Unique?

  •  Excel Versions for the Video Tutorials

  • Available for Excel 2010, Excel 2007 or Excel 2003

Multiple Options for Delivery

Resources to Assist Your Learning

  • The Excel Workbook file that I used while filming each video lesson – Included with purchase!
  • PDF Manual of Step-by-Step Instructions – That you can print out – Included with purchase!

Take a Short Video Tour

Follow this link to watch a short – 5 minutes – video tour of my new products on my YouTube Channel – DannyRocksExcels

Visit My New Secure Online Shopping Website

I invite you to visit http://shop.thecompanyrocks.com This is my new online shopping website. I have created secure shopping carts to ensure that your privacy is protected. I also offer a 100% Customer Satisfaction Policy. If, for any reason, you are not 100% satisfied with any of my products, I will refund your purchase with no questions asked!

Danny Rocks

The Company Rocks

New Extended Length Video Tutorials: Master Excel Pivot Tables in 90 Minutes [ 5:16 ] Play Now | Play in Popup | Download (1989)
Link to this post!
Filed Under: Excel Training, Online Store for The Company Rocks, Pivot Tables, Podcast Tips & Timesavers Tagged: , , , , , , , , , , ,
August 6, 2011 Leave a Comment

Tips to Help You Remain Productive When Upgrading to Excel 2007

Beginning Level Videos for Excel

Videos at the Beginning Level - 50 Best Tips for Excel

I enjoy participating in several discussion and sharing groups on LinkedIn. In one group, the question was raised, “The Ribbon – Love it or Leave it?” Many group members wrote to say that they just cannot figure out the new user interface – The Ribbon. Others expressed frustration that they were much less productive in their work as they tried to make the transition to either Excel 2007 or Excel 2010. Some said that they simply gave up and returned to using Excel 2003.

Certainly, I felt frustrated and lost when I first began to use Excel 2007. So, let me share my best tips for quickly adjusting to and mastering the commands and tools in Excel 2007 – and Excel 2010.

Topics Covered in this Video

  • The Office Button in Excel 2007 to open, save and print Excel workbooks and to change Excel Options
  • 7 Standard Tabs on the Ribbon
  • Groups of related Command Buttons
  • Launchers to open up Dialog Boxes
  • Command Buttons with Menus
  • Showing the Quick Access Toolbar below the Ribbon
  • Customizing the Quick Access Toolbar
  • Working with the Mini Toolbar

Watch Video in High Definition on YouTube

Follow this link to watch this Excel Video Tutorial in High Definition on my YouTube Channel – DannyRocksExcels

Purchase My DVD-ROM, “The 50 Best Tips for Excel 2007”

Here is the link to learn more about my best-selling DVD-ROM. You can purchase this from my online store using a secure shopping cart. On sale now for only $29.97 USD.

 

Link to this post!
Filed Under: Excel Training, MS Excel 2007 DVD, New in Excel 2007, Tips and Time Savers, YouTube Videos by Danny Rocks Tagged: , , , , , , , ,
January 3, 2011 1 Comment

Get My New Popular Keyboard Shortcuts for Excel Chart – It’s Free When You Register

Click the link below to download my chart of Excel Keyboard Shortcuts as a PDF:
CR – Updated Chart of Popular Excel Keyboard Shortcuts
Excel Keyboard Shortcuts

Preview Excel Keyboard Shortcuts

 Hello and Welcome – to my new “Members Only” section of The Company Rocks website! 

I just published a new five-page Chart – “Popular Keyboard Shortcuts for Excel.” It is free for you to download as a PDF. 

This, along with additional content, will soon be inlcuded in my new “Exclusive Membership Content” area on my website. 

My Exclusive Membership Site is not quite ready to launch. So, I am offering this chart for you to download as a PDF from this post. 

To get this chart, click on this link –  

CR – Updated Chart of Popular Excel Keyboard Shortcuts 

New! Download My Free 50 Minute Video Recording Demonstrating Excel Keyboard Shortcuts

Click this link to learn how you can download my new “extended length” video recording. It is “free of charge!”

I am proud of the way my Excel Keyboard Shortcuts Chart turned out – and I think that you will get a lot of value out of this free resource. I organized the Keyboard Shortcuts by the following Tasks:

Excel Keyboard Shortcuts

  • Basic File Operations
  • Undo, Restore or Repeat Actions
  • Insert, Delete, and Copy Cells
  • Edit Data
  • Show / Hide Columns and Rows
  • Select Contiguous Cells
  • Navigate Within a Worksheet
  • Navigate Between Workbooks
  • Find, Replace, and Go To
  • Enter Data
  • Work with Cell Comments
  • Work with Names for Cells and Ranges
  • Work with Formulas and Functions
  • Work with Formula Precedent and Dependent Cells
  • Print Worksheet
  • Formatting Cells and Text
  • Work with Subtotals and Outlines
  • Work with Tables, Lists, and Filters
  • Work with Charts
  • Work with Excel Windows and Open Applications

So, as you can see, this is a comprehensive – but not exhaustive – list of the best Keyboard Shortcuts for Excel. I took care to ensure that these Keyboard Shortcuts will work for all versions of Excel from 2003 through version 2010. 

The 50 Best Tips

The 50 Best Tips Series

I have several video lessons that cover keyboard shortcuts on my DVD-ROM, “The 50 Best Tips for Excel 2007.” It is a best-seller and it is now on sale! Use Coupon SAVE-20-PCT at checkout and save 20% on any puchase at my online store. Click here to open a secure shopping cart. 

And let me know your thoughts on my Excel Keyboard Shortcut Chart. Do you have a favorite Keyboard Shortcut that you want to share with our readers? Add your comments below.

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

Link to this post!
Filed Under: Excel Training, Keyboard Shortcuts, Membership Content, Tips and Time Savers Tagged: , , , ,
December 7, 2010 2 Comments

Use the SUMPRODUCT Function to Total Lists Using Multiple Criteria

In Excel 2007 and Excel 2010 you can use the new SUMIFS Function to total a list using multiple criteria. This is a great new feature!

But … what if you are using Excel 2003 or earlier? Or, if you collaborate with colleagues and clients who use older versions of Excel – how can you use multiple criteria to select the records that you want to Sum? You can use the SUMPRODUCT Function as I demonstrate in this video lesson.

The 50 Best Tips

The 50 Best Tips Series

Key Concepts

  • Create – and use – Named Cell References for Ranges / Arrays in your formulas
  • Use the Function Arguments Dialog Box – Use the keyboard shortcut Ctrl + A to bring this up
  • Use the F3 Keyboard Shortcut to bring up the Named Ranges List
  • In this lesson, use () to enclose each Array Argument
  • In this lesson, use * to substitute for the , to separate each Array in the formula (Edit this in the Formula Bar)

There are usually several ways to solve any problem in Excel. This is a good procedure to learn – regardless of the version of Excel that you use.

Click here to view the 1st part of this series on how to use the SUMPRODUCT Function in Excel.

You can view this Excel Video in Hig Definition, Full Screen Mode on my YouTube Channel – DannyRocksExcels by clicking here.

I cover the SUMIF and SUMIFS Functions in greater detail on my DVD, “The 50 Best Tips for Excel 2007.” Click here to start shopping using my secure shopping cart.

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

Using the SUMPRODUCT Function to Total a List Using Multiple Criteria [ 7:47 ] Play Now | Play in Popup | Download (1121)
Link to this post!
Filed Under: Excel Functions, Excel Training, Podcast Tips & Timesavers, SUMIF and SUMIFS Functions Tagged: , , , , , , , , ,
« Previous Page
Next Page »