voluntaryXchange

Excel Tip # 22: Clicking a List, and Highlighting the Results

This is kind of a neat trick. Everyone likes “tiger striping” their spreadsheets to make data more visible. What if you could shade your data based on a choice you made from a list? Even better, what if you had multiple, separate rows that you could stripe? Like so:

See how the choice of “Jams,Preserves” at the top right got the two rows corresponding to that choice to highlight!

These directions from Susan Harkins at TechRepublic are a bit involved, but not hard. First, from your list of non-unique entries, extract a list of unique ones. Second, turn that into a drop-down menu. Third, set up conditional validation for a broader range based on that menu choice.

Technorati Tags: excel,conditional,formating,from,list

What Is a Surface Good For? An Interesting Perspective

Matt Baxter-Reynolds argues that Microsoft’s new Surface tablet isn’t really about competing with the iPad. It’s about being able to use Microsoft Office on a tablet.

With the kickstand out, Type Cover keyboard deployed, and Word fired up, the Surface is an absolute joy to use. It's a truly, almost unfathomably good computing experience. I instantly went from "meh, what's the point of this thing?" to "OMG - this is the best device ever!"

That’s interesting … there’s a lot of people who’s biggest problem with tablets is the difficulty of getting their new ideas into it … since everyone agrees that the iPad is a great way to get older ideas out.

Technorati Tags: microsoft,surface,ipad,office

Excel Tip # 21: A Dynamic Chart

Here’s a great tutorial from TechRepublic on how to combine a =vlookup() and a drop-down menu to create a chart that can show what the user wants to see.

Technorati Tags: excel,tip,21,dynamic,chart

Domino’s Transshipment Visualized

Two thoughts about this visualization of Domino’s delivery routes:

1) The bicycle deliverer is a piker. I delivered (by car) in the Buffalo suburbs for over 4 years. I covered about 100 square miles of territory, did over 100 miles of stop-and-go-driving on a weekend night, and the suburbanized swampy terrain had very few neat, homogenous rectangles.

2) What a great video for the transshipment portion of a linear programming or Excel class.

P.S. We have a distribution center (one of the red nodes towards the video’s end) for Wal-Mart located about 35 miles south of my home. I recently found out that it’s actually a distribution center for distribution centers: it handles mostly clothing, and ships nothing directly to stores.

Technorati Tags: domino's,pizza,transshipment,delivery,route

A Joke for Coders

Test your code before it matters. This goes for your spreadsheets too … if you didn’t’ think they count as “code”.

Technorati Tags: excel,test,code,production

Excel Tip #20: Thermometer Graphs

I think this sort of graph begs for downstream user misinterpretation, but I have had students ask me how to do this:

So, here’s a thermometer graph tutorial from Susan Harkins at TechRepublic.

Technorati Tags: thermometer,graph

Excel Tip # 19: Protecting a Style

This is kind of neat trick. Obvious in retrospect, but I’d never thought of it.

Any sort of cell formatting you can do can be saved as a style and applied elsewhere.

But … protection is a form of formatting … so that can be part of your style as well.

Why is this useful? Because often you want to protect most of your sheet from downstream users, while leaving open a few select spots for them to edit. If such editable cells share a style, you can turn them on and off as a group, or add new cells to that list by simply assigning a style to them.

Technorati Tags: excel,tip,29,protect,style

Beware of Spreadsheets’ Modelers

An oldie but a goodie:

Technorati Tags: dilbert,spreadsheet,financial,modeling,strategy

Confidence Interval

Technorati Tags: confidence,interval

Excel Tip #18: Custom Lists

Remember the first time you typed a 1, and then a 2, and then dragged those cells to create a sequence of numbered cells?

You can do the same with other lists: Excel includes obvious ones like days of the week and months. What I really like about these is that you can start in the middle and they still work.

Access Analytics points out another good one: a list of letters you can drag.

In Excel 2010

• Type the list you want to be able to reuse. Sorry, no formulas.
• Click “File”
• Click “Options”
• Click “Advanced”
• Scroll down and click “Edit Custom Lists”
• Click the button to select a range, and select the list you typed.
• Click “Import”. Your list should appear in the box.
• Click “Add”

What sort of lists might you want to create this way?

• Upper case letters
• Lower case letters
• Roman numerals
• Ordinals (First, Second, Third, etc.)
• People’s last names
• People’s first names

Those last two are pretty useful for a teacher: I can quickly generate lists of students to mix and match for group projects, and such.

Here’s an odd one I’ve done. I give multiple choice exams on scantrons. The rows of “bubbles” for questions are in groups of 10. Sometimes, if I have more than one thing on a test, I have to give students a score out of questions 1-10, and say, 11-20. So I have a custom list that starts with those and goes up to 91-100.

Technorati Tags: excel,tip,18,custom,list