Home Archives for Arrays in Excel Formulas
May 26, 2011 Leave a Comment

Two Ways to Turn Excel Data On Its Side – Transpose the Data!

Transpose Excel Data

Transpose Excel Data

How many times have you spent an hour or more creating an Excel worksheet only to discover – or be told – that you have the wrong structure? The values that you entered run vertically down in Column A. And  now, you need to have your values run horizontally across in Row 1.

Well, don’t despair! And, most important, do NOT retype your entries on another worksheet. Rather,watch this video to see how to “Turn Excel Data On Its Side”:

  1. Select Transpose from the Paste Select dialog box or the right-click shortcut menu
  2. Use the =TRANSPOSE() Function which is an Array Function

Watch as I demonstrate the advantages of each approach – they are different! If you are “curious” about how an Array Function works, the TRANSPOSE Function is a good one to practice with.

Click this link to watch this video in High Definition on YouTube.

Now Using Excel 2010

I filmed this video using Excel 2010. I will use Excel 2010 for all future video lessons unless there is a particular need to use another version to illustrate a point.

Download Workbook Used in This Video

Click on this link to download the Excel Workbook that I used in this video tutorial. It is stored on my “SkyDrive” at www.office.live.com

Get my best-selling DVD-ROM, “The 50 Best Tips for Excel 2007” for only $29.97!

Link to this post!
Filed Under: Download Files from Video Lessons, Excel Functions, Excel Training, Tips and Time Savers, YouTube Videos by Danny Rocks 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: , , , , , , , , ,