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:
- Transpose copy function
- Validate entries to a list
- 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.
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.
@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.
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.
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.
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.
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
Great ! I liked the video.
Anyway, I’d prefer shorter ones, i.e. 2-3 minutes with short tips insted of long ones.
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
@Jay – You can get that information (summing sales for each individual) by using a Pivot Table.
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.)
Great post! I thought I was pretty good with excel, but even I learned something about the transpose feature. Great!
I really appreciate finding this video — very useful! I also passed along to my teammates at work. I look forward to more.
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.
@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.
@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/
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 !
Very nice video on Excel. Can use the Validation approach
now.
Well done.
John