Home Archives for Transpose Function
November 13, 2011 1 Comment

Tips for Working with Array Formulas and Functions in Excel

Using ARRAY Functions in Excel

ARRAY Formulas in Excel

Array Formulas and Functions are very powerful tools to use in Excel. However, the concept of working with ARRAYs take a little bit of time and practice. In this video tutorial, I demonstrate how to:

  1. Create formulas that use ARRAYS as arguments
  2. Work with ARRAY FUNCTIONS – for example, the TRANSPOSE Function
  3. Key “Got’cha” steps to master – for example, selecting all cells to receive formula results before creating the ARRAY Formula.
  4. The importance of using Ctrl+Shift+Enter to complete the Array formula.

What is an ARRAY?

“An Array is a collection of Cells or Values that Excel treats as a single unit.”

Why Use an ARRAY Formula?

  1. Automatic Level of Protection for Formula Cells – You cannot delete nor edit a single cell in an Array Formula
  2. Eliminate Intermediary Calculations – For example, you can find the Grand Total without having to create a field to calculate “Extended Price.”
  3. Worksheet, usually, calculates faster because you are using fewer formulas.

Visit My NEW Online Shopping Website

http://shop.thecompanyrocks.com is my new, secure online shopping website. I invite you to visit and preview my new products.

Watch Tutorial in High Definition

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

Tips for Working with Array Formulas and Functions in Excel [ 12:54 ] Play Now | Play in Popup | Download (1721)
Link to this post!
Filed Under: Excel Formulas, Excel Functions, Excel Training, Podcast Tips & Timesavers Tagged: , , , , , ,
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: , , , ,