track...o...tron..? whatever, free template!

Our practicum teams are starting to get moving, and my team has decided to use a spreadsheet to help keep track of our tasks as they start building up. As one of the project management software evangelists at my last job, I couldn’t help rubbing my hands together with glee as I created a Google Sheet, tossing in some functionality to make it user-friendly and easily filtered. Here’s a screenshot!

image

Features:

  • Data validation creates drop-down menus for fields like Status, Priority, Deliverable, Area Lead, Assigned Yes/No
  • Conditional formatting calls attention to High priority tasks and missed deadlines
  • Due date field is set up to have a time due as well as a date, eliminating confusion
  • Filtering allows users to ignore completed tasks, and allows collaborators to see only what is assigned to them (columns H-M)
  • Deliverable and Area Lead drop-down menus populate from other sheets in the workbook, allowing for simple customization and updating

Another nifty feature is that I’m sharing this absolutely free.

If the idea of project management software makes you want to run screaming to your hills of post-it notes, be not afraid! This is essentially a to-do list on steroids. No Gantt charts, no dependencies, no attachments. Just what needs to get done, when, and by whom, with a few teensy extras to make it slap you in the face when you’re late.

Maybe you’re planning a vacation with your extended family. Maybe you’re leading a volunteer committee. Maybe you just want to plan your homework. I don’t know what it is that you do, just get yourselves organized! And when you do, let me know how it goes.

the Outfit-o-Tron 5000, or, what an analytics grad student does on break

I have a confession to make.

When I have enough time on my hands, I tend to organize certain things to within an inch of their lives, typically using spreadsheets. For example, when my husband and I hosted Thanksgiving last year at our apartment, I created a color-coded spreadsheet tracking what foods we would serve, how much we spent on ingredients, oven timing, etc.:

image

           the color coding was more for fun than any actual purpose.

Perhaps, then, it’s not too surprising that after a few days off from class I’ve created a wardrobe inventory/outfit planning spreadsheet. I call it the Outfit-o-Tron 5000.

When the fall term begins later this month, my classmates and I will be expected to wear “business casual” to “business” wear five days a week. This got me wondering how deep my wardrobe was, and reminded me of a general desire I’ve had to catalog my wardrobe (I know, anal retentive much?). When I go clothes shopping I completely forget what I already own and what I need, so an inventory would help me identify holes.

But I wanted to go beyond a list. A snazzy enough spreadsheet would also help me plan future outfits and help me stretch my current wardrobe that much farther.

Warning: The end result is completely over the top and almost frighteningly obsessive-compulsive, so please believe me when I say I mostly did this as a learning and database-building exercise and not because I really need that much help with my wardrobe.

I began by listing out all of my “business”-worthy clothing on different worksheets in the same Excel file: tops, layering tops (such as cardigans and blazers to pair with blouses), bottoms, layering bottoms (such as tights to pair with skirts), and dresses. I left shoes and accessories alone for now. 

Next I applied Ashish Mathur’s very easy-to-follow instructions for using Microsoft Query within Excel to create all possible combinations of multiple lists. (Prior to this project, I didn’t even know you could query in Excel!)

I made intermediate worksheets to create all the possible combinations of layered tops, layered bottoms, and dresses with cardigans/blazers (as well as unlayered tops/bottoms/dresses), and then created a final worksheet of all the possible top and bottom combinations.

I present to you… the Outfit-o-Tron 5000 (alpha)!

One of my favorite features of the OoT5K is that, because it’s formatted as a table, I can start building an outfit using the drop-down menus rather than scanning through the whole list. So if I wake up and feel like wearing a particular top but don’t know what to wear it with, I can select that top and browse through the combinations. Despite a relatively thin wardrobe, I apparently have nearly 8,000 possible combinations of outfits!

My second favorite feature is that I can add or remove an item to/from one of the building block worksheets, refresh the query-built spreadsheets with the click of a button, and voila! Up-to-date combinations.

This is what I could put together quickly, but I’m going to experiment with a few improvements:

  • Make the query more sophisticated.The spreadsheet creates some combinations that I would never put together, such as a polka-dot top with a pinstripe skirt. I could manually delete bad combinations, but ideally I’d like to fix the query to only match patterns-solids or solids-solids, and not to match warm colors with cool colors. That way I could add or remove an item from the wardrobe and refresh the outfit combinations without adding back the outfits I don’t like.
  • Delete the “bottom combos”. In this case it doesn’t feel worth it to see every possible pair of tights I could put with a skirt, top and second top layer - it makes for too much to sift through and I don’t need to be prompted to match up a skirt with tights.

Like I said, completely over the top - but a fun and quick way to practice a simple project from start to finish and pick up some new skills. 

Could you use a tool like this in your life? Maybe for meal planning or building workout routines?