• Skip to main content
  • Skip to secondary menu
  • Skip to primary sidebar
  • Productivity
  • Technology
  • Development
  • Organize
  • Money
  • Time
  • Misc

Productivity501

Pieces of the productivity puzzle.

  • Home
  • Blog
  • About
    • Archives
  • Newsletter
  • Products
    • Courses
  • Contact
You are here: Home / Technology / Three Things You Didn’t Know About Excel

Three Things You Didn’t Know About Excel

By Mark Shead 18 Comments

Excel is really a “swiss army knife” type tool. I have used it for all kinds of odd things including HTML for long lists, creating a checkerboard, analyzing server log files, projecting cell phone usage and analysis whether people preferred live or telecast music at a church overflow room.  Excel has so many hidden little features that it is easy to overlook some of the capabilities that let you do a complicated task quickly.

This video demonstrates three capabilities of Excel that the average user does not know about.


Three Things You Didn’t Know About Excel from Productivity501.

(If you have trouble seeing the video, turn on HD.  You’ll have to go to Vimeo, but you can watch it in high resolution that way.)

The three items covered are:

  1. Transpose copy function
  2. Validate entries to a list
  3. Named regions

None of these things are very earth shattering, but knowing these tools can turn 20 minutes of boring Excel work into a two minute simple job.

Please let me know what you think of these videos and if you’d like to see more of them in the future.

Filed Under: Technology Tagged With: excel, office, software, tips

Reader Interactions

Comments

  1. Steven Klassen says

    July 1, 2008 at 12:28 pm

    I’m on Excel 2007, but it looks like the =SheetName!A1:A10 works to use the values from A1 to A10 on the named sheet as a source.

    Reply
  2. Mark Shead says

    July 1, 2008 at 12:37 pm

    @Steven – Hm. Maybe it is different on the OS X version. I get a message that says: ” You may not use references to other worksheets or workbooks for Data Validation criteria.” When I try to validate using a list in a different sheet. Thanks for pointing that out.

    Reply
  3. Arjun Muralidharan says

    July 1, 2008 at 3:12 pm

    Transpose is great. In fact, it’s a term from maths, if you’ve ever worked with matrices, transposing should be familiar. It basically takes the matrix’s columns and rows and rearranges them just like excel did it here.

    Reply
  4. Nathan says

    July 1, 2008 at 4:16 pm

    When working with the list validation, you can always have it in hidden/protected columns/cells. This is helpful if you don’t need the list actually visible as such.

    Reply
  5. Neil Kelty says

    July 1, 2008 at 8:45 pm

    Mark:

    You’ve done a couple of these video posts in the past with Firefox and another with Excel – they are absolutely WONDERFUL. And I’d really like to see you do many more of these – I know it probably takes more time, but it is well worth it – you’ve got an excellent narration voice.

    Great job!

    Hopefully we’ll see some more video posts.

    Reply
  6. Mandar Vaze says

    July 2, 2008 at 12:00 am

    Mark,
    Good to see you in person :)

    I’ve seen someone use the “Validate to list” earlier, but didn’t know myself how to do it.

    Transpose will also be helpful, I had to do exact same thing few days ago, and would have saved me some time, had I know this earlier.

    Thanks
    -Mandar

    Reply
  7. juanac says

    July 2, 2008 at 12:28 am

    Great ! I liked the video.
    Anyway, I’d prefer shorter ones, i.e. 2-3 minutes with short tips insted of long ones.

    Reply
  8. Jay says

    July 2, 2008 at 10:32 am

    That’s great, but is there a way to take the named rows concept and get all the sales for a particular person?

    SUM(SALES[x] IF PERSON[x] == ‘BOB’)

    I’ve been trying to do this for a while now

    Reply
  9. Mark Shead says

    July 2, 2008 at 11:01 am

    @Jay – You can get that information (summing sales for each individual) by using a Pivot Table.

    Reply
  10. Parish says

    July 3, 2008 at 6:35 pm

    Jay, have you tried using a SUMIF formula?

    When I started using ‘advanced’ excel features, I found ‘Excel Hacks’ by o’reilly press to be extremely useful (conditional formatting, nested subtotals, text-to-columns, etc. will save power-users TONS of time.)

    Reply
  11. Kevin H says

    July 4, 2008 at 11:45 am

    Great post! I thought I was pretty good with excel, but even I learned something about the transpose feature. Great!

    Reply
  12. Carolyn Bahm says

    July 5, 2008 at 1:25 pm

    I really appreciate finding this video — very useful! I also passed along to my teammates at work. I look forward to more.

    Reply
  13. Mert ALEMDAR says

    July 6, 2008 at 4:17 pm

    Hi, Great tip thank you.
    I want to ask something different.
    How are you recording your videos. I mean in the same time I’m watching you in thumpnail format at the right and in the same time I’m watching your screen. How are you doing this multiple recording? Is it in same time recording? Or are you recording two seperate video and merging? ? realy wonder how this two in same time could be happened? Thank you.

    Reply
  14. Mark Shead says

    July 6, 2008 at 4:39 pm

    @Mert – In the past I used a video camera recording to Quicktime and SnapZPro recording my desktop and them put them together in iMovie or FinalCut. It was a very painful process. I’m using ScreenFlow now and it records both sources simultaneously and then I can edit back and forth between them. It significantly reduces the overhead required to make a tutorial like this.

    Reply
  15. AdamV says

    July 7, 2008 at 4:07 am

    @Jay If you data is arranged in a tabular style then you can use the intersection of row and columns for which you have defined names, so if column C is called “Sales” (as a defined named range $C:$C) and row 6 is called Bob, then you can simply use =Sales Bob
    or =Bob Sales
    to get the intersection (the space is the delimiter here, which is why you can’t have spaces in defined names)
    Data arranged like this would probably be better off setup as a List (what is now called a Table in Excel 2007, a slightly improved version of the same thing)
    However, if you don’t have this tabular format with a single cell for the results (ie multiple rows for Bob), then SUMIF is probably the function you need, also note that in Excel 2007 there is an extended version of that function – SUMIFS (note the plural) which takes multiple criteria, producing (in your pseudo-syntax):
    SUMIFS(Sales(X) IF Person = Bob AND Region=North AND Quarter=1).
    I agree that if you do want a similar calculation for lots of people, then a Pivot Table is almost certainly a better idea in the long run.

    For Data Validation to short lists, simply type the list in instead of a cell range, so you don’t need any cells used for this.
    You can copy a cell with validation on and then use Paste > Special to copy just the validation settings to another cell which saves some effort.
    Also note if you have several cells with the same validation criteria and you change one, you can tick the box to “apply these changes to all other cells with the same settings” to make sure you don’t forget any.

    @Steven / Mark – Using a named range as the source for data validation is a way to use lists on other sheets. So, first you create a list on sheet1, say, then define a name which points to that range, then in your data validation on sheet2 just use =MyNamedRange. An added benefit of this method is that you can easily extend the range just by redefining the name, rather than going back to the validation formula, and the validation makes sense if you choose a good name such as “ProductCodes” or “CurrentEmployees”

    The O’Reilly Excel Hacks book is great. I think there is a second edition out now, but this was my review of the first edition:
    http://veroblog.wordpress.com/2007/12/11/excel-hacks-david-and-raina-hawley/

    Reply
  16. Deepa says

    July 9, 2008 at 4:40 am

    Hi,
    Nice video, short and sweet.. to the point
    I vouch for the fact that using these surely saves lot of time..
    I have been doing this for quite some time.. and boy surely saves time.

    However, I wish to add one tweeny little things
    It is possible to call the list from a diffrent sheet ( of the same workbook — where you can have all the lists)

    This is how i do it

    In different sheet
    Type some city name
    select the list (range of cells)

    Click the name box
    Typein a meaningful name ( I have given “City”)

    Come to main sheet
    Select colum / range of cells
    Data – validation – List ”
    Type =City

    Here “City” is the name of the list.
    and, “=” sign must be there

    Hope this helps
    Loved this space
    Regards !

    Reply
  17. John Wyman says

    July 18, 2008 at 1:10 pm

    Very nice video on Excel. Can use the Validation approach
    now.

    Well done.

    John

    Reply

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Primary Sidebar

Featured Posts

The $500,000 Solution to a $12 Problem

College Degree And Income Potential

Photo Sharing Christmas Gift Ideas for Family

Minimal Minimalism

See Your Mail Before It Arrives

Finishing vs. Starting

Best Bluetooth Headset I’ve Owned

What Is Your Current Work Zone?

Social Glass Ceilings

How Much Time Do You Actually Have

Return to top of page

Copyright 2018 Xeric Corporation