Tutorials

  • How to Create Pivot Tables

    Let’s talk pivot tables.

    Pivot tables make the process of summarizing data super simple: select your data, drag and drop a few variables, and BOOM! Seriously, they are really that straight forward. What used to take time and thought to create now takes mere seconds!

    My friends, pivot tables are a game-changer. If you aren’t using them, you are missing out.

    Why some people consider them to be an “advanced” Excel skill is beyond me.

    In my opinion, pivot tables only require a few things:

    1. Well-structured data (for more about this, click here)
    2. Knowledge of what you want your data to tell you
    3. Basic pivot table mechanics

    Let’s dive into each.

    Well-structured data

    First, your data MUST be structured so that observations are in rows, and variables are in columns.

    For example, if you want to track your income and expenses, you should have columns for:

    • Transaction date
    • Transaction amount
    • Transaction description
    • Transaction category
    • Etc.

    Each row beneath your column headers will contain the above information for a single transaction.

    Knowledge of what you want your data to tell you

    Do you want to know how much you spent on each category?

    Do you want to know how much you spent in each month?

    Do you want to know how much you spent on each category each month?

    Do you want to know how much you earned?

    Understanding what it is you want your data to tell you is essential when it comes to actually creating your pivot table. But, once you understand this, creating a pivot table is easy!

    Basic pivot table mechanics

    Both Excel and Google Sheets make building pivot tables simple, although they differ slightly in their approach.

    We’re going to walk through the mechanics (and tie the first to points together) using an example:

    Suppose you sell spreadsheets through your website (sorry, couldn’t help it!). Of course, you track your expenses and income using the Profit & Loss Tracker, which already comes with a few nifty pivot tables. Score! 

    You love seeing how much money your website brings in each month, which the pivot tables make easy to do.

    And then, your arch nemesis hacks into your computer and – just to mess with you – deletes all of your pivot tables. 

    NO!

    How on earth will you rebuild them?

    Exel

    1. Select your data => Insert => Pivot Table => OK
    1. Then this happens…
    1. Now, go to the box on the right called “Field Names” and drag items from it into one of the four boxes below using the following overly general guidelines:
      • Dates often work best as columns. If you just want to see a simple list of items, e.g., expense categories and their totals, leave the columns box blank.
      • For basic data analysis, categories or other variables of interest typically go in the rows box.
      • Variables for which you want to exclude certain values can go in the filter box.
      • Variables that represent amounts often go in the values box.

    I can already conceptualize a bazillion exceptions to these generalizations. So, the best way to determine what goes where is to just start dragging and dropping things – keeping in mind the question(s) you are asking of your data.

    In this case, I’m going to drag the:

    • Month variable to the column box
    • Expense Category variable to the row box
    • Amount variable to the values box
    1. This ^ pivot table gives looks a little rough, but it contains everything it needs. You can:
      • Change its formatting in the Design tab on the ribbon. 
      • Change how the variables are displayed by clicking inside the boxes in the Pivot Table Fields section.
      • Change the look of other elements (check out this post for some ideas).

    Here’s what my pivot table looks like after a little formatting:

    Google Sheets

    1. Select your data => Data => Pivot Table => New Sheet => Create
    1. Then this happens…
    1. [This commentary was copied from the Excel section, since you probably didn’t read it if you’re reading the Google Sheets tutorial] Now, go to the Pivot Table Editor box on the right => click the “Add” button next to Rows, Columns, Values, and Filters options using the following overly general guidelines:
      1. Dates often work best as columns. If you just want to see a simple list of items, e.g., expense categories and their totals, leave the columns box blank.
      2. For basic data analysis, categories or other variables of interest typically go in the rows box.
      3. Variables for which you want to exclude certain values can go in the filter box.
      4. Variables that represent amounts often go in the values box.

    I can already conceptualize a bazillion exceptions to these generalizations. So, the best way to determine what goes where is to just start adding things and then drag and drop them as desired – keeping in mind the question(s) you are asking of your data.

    1. This ^ pivot table gives looks a little rough, but it contains everything it needs. You can:
    1. Change its formatting in the Design tab on the ribbon. 
    2. Change how the variables are displayed by clicking inside the boxes in the Pivot Table Fields section.
    3. Change the look of other elements (check out this post for some ideas).

    Here’s what my pivot table looks like after a little formatting:

    Want to learn more about pivot tables (and pivot charts)?

    Subscribe to my low-volume newsletter!

    Continue reading

  • Save Loads of Time with Well-Structured Spreadsheets

    How I structure my spreadsheet tables to save time (& grow with my biz)A former employer hired me to set energy prices and create stupidly wicked compensation metrics for its sales team.

    The job paid well, but guess what 75% of my days involved?

    Being the office’s spreadsheet alchemist.

    My employer could have hired on someone at half my salary to handle the tasking that accompanied said status, but it didn’t.

    A typical day would begin with the Operations Manager asking me to dynamically link up a slew of spreadsheets that tracked his team’s “saves” (customers who called to cancel service only to be convinced otherwise). There was no template – it was a very manual process.

    Later, the Sales Manager would come over to my cube in a frenzy needing to know RIGHT NOW whether I could make sense of some random calculations that were provided to him in a spreadsheet that contained no documentation on how to use it.

    Then, as I’m refilling my coffee, a sales team supervisor would corner me and ask if I could automate her spreadsheet that she shares with the spreadsheet-illiterate VP every morning – a spredsheet that required two-hour manual updates. EVERY. FREAKIN. DAY.

    What does all this have to do with you, dear reader?

    That job taught me the importance of structuring spreadsheets well.

    It taught me how to structure my spreadsheets so that they would effectively and efficiently accomplish their intended tasks.

    It also taught me how to create spreadsheets with enough foresight to keep them relevant over time.

    My friends, when you are working with spreadsheets, everything boils down to how your tables are structured. And I mean everything.

    Well-structured tables will save you boatloads of time.

    Well-structured spreadsheet tables will save you boatloads of time.Click To Tweet

    Well-structured tables don’t require advanced spreadsheet proficiency to create but will elevate you into goddess territory if you create them because so many people don’t.

    Quick note: If you haven’t already downloaded From Spreadsheet Virgin to Goddess in 30 Minutes, it would behoove you to do so because I cover this and related topics there.

    Here’s how to structure your spreadsheets so that they don’t drain your most valuable asset – your time:

    Step 1: Put a bit of thought into the how and what and do you questions:

    1. What is your objective?
    2. What do you need the data to tell you?
    3. How should you organize your data to manipulate them effectively?
    4. Do you have the expertise to accomplish your goals efficiently?

    Seriously, I can’t overstate how critical these questions are. I ask myself them every single time I design a spreadsheet.

    Step 2: Spend a few minutes creating a mockup.

    I don’t care if you sketch it out on a napkin while the Starbucks barista pulls your espresso, or if spend a bazillion hours laying it out dashboard-style in Excel or even Photoshop (just don’t waste your time at this stage picking out colors, border styles, etc.).

    The point is to have a rough idea of what you want the finished product to look like.

    Step 3: Create your basic tables – without formatting.

    Nothing sucks more than having to constantly undo and recreate borders during the creation phase. Just leave them for the end.

    Note: if you use Excel’s “insert table” option, you will be presented with a preformatted table. There are advantages and disadvantages to using them and must be evaluated on a case-by-case basis.

    Anyway, here are the types of tables you will most likely create:

    1. Tables that are intended for raw data entry
    2. Tables that display information for the end user to digest
    3. Tables that function as points of reference for other tables
    4. Tables that function as calculators

    Often, your table may contain elements of multiple table types. Some of mine contain all four!

    Now, here’s where people screw up.

    They create tables that are intended to not only display information, but also to make calculations and serve as the raw data entry point. Because of the former, they make them pretty.

    They add spacer columns and rows.

    They may even merge header rows with similar groupings.

    And, they just multiplied their workload by a thousand.

    That former colleague I told you about did just that. She had a pretty spreadsheet that was supposed to be easy on the spreadsheet-illiterate VP’s eyes. So, she added a bazillion spacers and organized the output by group (in her case, it was by sales territory). Not that either of these things are terrible things to do – I do them sometimes – but by doing so, she had to spend two freakin’ hours every freakin’ morning adding new spacers, adjusting existing formulas to account for the new blank spacer rows, and linking to other reference tables. Because she was manually adjusting everything and because the VP was her end user, she had to cross-check everything to make sure that she didn’t screw things up.

    Instead, she could have pulled the spacers, created new variables for each of the header groupings, and added a pivot table. If the VP didn’t like the look of the pivot table output, no worries. She could update the same original pretty table with the information that the pivot table spit out. So, I spent a few hours overhauling her spreadsheets.

    Guess what?

    Once I redesigned her spreadsheets, she was able to accomplish the same daily two-hour updates in less than 15 minutes.

    Do the math. Every week, she now saves almost nine hours of time. That’s 450 hours a year, assuming that she gets two weeks of PTO.

    This single post can be turned into an entire course topic – it’s way too much information to include in a single blog post. But the takeaway is that most tables are best built with efficiency in mind.

    It may be more efficient to create two or more tables as I did for my colleague than to simply create one master table.

    Step 4: Populate your tables entirely with sample data.

    This step is critically important if you will share this spreadsheet with others or rely on its calculations to make important decisions.

    For instance, every time you cut and paste something, you will remove the cell’s formatting and any formula that it may contain. If you don’t correct this at the moment it occurs (and you probably won’t), your table – at best – will use the spreadsheet program’s default formatting and fonts.

    Also, if you use complex formulas like I do, they may not always carry down the way you want them to. Or, maybe they won’t reference the cell you need them to reference, which will only become evident at row 132 and on.

    So, run sample data through your spreadsheets before sharing them with anyone.

    Step 5: Format your spreadsheets.

    Okay, I fully admit to being guilty of formatting my spreadsheets too early in the process because I dig pretty spreadsheets. But, I pay for it with tons of time fixing screwed up borders, font changes, etc. Just sayin’.

    The takeaway of this post is to put some thought into creating well-structured tables that emphasize efficiency. Doing so will save you time, mental energy, and propel you into spreadsheet goddess territory.

    Now, I’d love to hear from you. Feel free to share your thoughts or ask questions in the comments section below.

    And, if you have spreadsheets that could use an overhaul (and maybe a little love), drop me a line. I have been known to take on the occasioal freelance project. Thanks! Until next time…

    Continue reading

  • How to Use Conditional Formatting to Identify Important Outcomes

    Do you ever wish the cells in your spreadsheets would somehow alert you if certain criteria were met?

    For example, say you are tracking your budget and want to be alerted if your expenses exceed a certain threshold.

    Recently, my 9-5 wanted me to review our client ROI delivery schedule for 2017. It looked something like this:

    1. Client’s measurement period ends.
    2. Client has a runout period of (ideally) three months for final claims to be processed.
    3. The IT smarty pants guys take about a month to ingest a bazillion datasets for said client.
    4. My team gets three weeks to complete the ROI.
    5. The reporting team gets five weeks to package the ROI results up for the client.
    6. The client is presented with the ROI report and presentation.

    Now repeat for a bazillion clients, and make sure that they are staggered such that my team never has more than so many ROIs happening at a given time, and the reporting team never has more than so many ROIs happening at a given time. Plus, there are financial penalties if we do not meet client deadlines.

    Can you see a migraine coming on?

    My friends, conditional formatting is a game changer.

    Conditional formatting is available in both Google Sheets & Excel, and it’s an exceptionally wicked tool to have in your toolbox.

    With conditional formatting, you can make certain cells do any of the following when a certain condition is or isn’t met:

    • Change color
    • Make the text effectively “disappear” (by changing it to white)
    • Display icons like checkmarks and stoplights

    And, the (non-exhaustive) list of conditions you can evaluate can includes:

    • Dates & values
    • No values
    • Text (e.g., yes, no, I hate cotton candy, etc.)
    • Relative dates & values (e.g. the bottom 10%, > or <, etc.)
    • Duplicate values

    To use conditional formatting, you simply tell your spreadsheet program what cells you want to apply the formatting to, the condition these cells must meet for the formatting to be applied, and the type of formatting to be applied.

    Learn how to make your spreadsheet results really *pop* using conditional formatting.Click To Tweet

    The process is slightly different for Google Sheets than it is for Excel. Here goes.

    How to apply conditional formatting in Excel

    To change the color of a cell that contains a negative value:

    Select the cells you to which you want to apply conditional formatting => click the ‘Conditional Formatting’ icon in the ‘Home’ tab => select ‘Highlight Cells Rules’ => select ‘Less Than’ => enter in the field => from the ‘Format with’ drop-down list, choose ‘light red fill with dark red text’ => ‘OK’.

    Adjusting these instructions for positive values should be self-explanatory, but if you run into issues, please let me know in the comment section below. I’m happy to help!

    To change the color of a cell that contains the word ‘No’:

    Select/highlight the cells you to which you want to apply conditional formatting => click the ‘Conditional Formatting’ icon in the ‘Home’ tab => select ‘Highlight Cells Rules’ => select ‘Text that contains…’ => enter ‘No’ in the field => from the ‘Format with’ drop-down list, choose ‘green fill with dark green text’ => ‘OK’.

    And here’s a short gif screencast on how to apply conditional formatting to both cells that contain text and currency values in Excel:

    How to change a cell's color based on its value with conditional formatting. Make it easy to identify only those values you care about!

    Here’s how to apply conditional formatting in Google Sheets:

    To change the color of a cell that contains a negative value:

    Select/highlight the cells you to which you want to apply conditional formatting => right-click => select ‘Conditional formatting…’ => in the ‘Format cells if…’ drop-down list, select ‘Less than’ => enter in the field below => from the ‘Formatting style’ drop-down list, choose red box => ‘Done’.

    Adjusting these instructions for positive values should be self-explanatory, but if you run into issues, please let me know in the comment section below. I’m happy to help!

    To change the color of a cell that contains the word ‘No’:

    Click the ‘Add new rule’ text below your newly created rule => change the range in the ‘Apply to range’ field to reflect those cells to which you want the rule applied => from the ‘Format cells if…’ drop down list, select ‘Text contains’ => enter ‘No’ in the field => from the ‘Format style’ drop-down list, choose the green box => ‘Done’.

    And here’s a short gif screencast on how to apply conditional formatting to both cells that contain text and currency values in Google Sheets:

    How to change a cell's color based on its value with conditional formatting. Make it easy to identify only those values you care about!

    There are plenty of other conditional formatting options.

    There are so many ways that you can apply conditional formatting to your cells!

    And, you don’t need to limit yourself to the less-than-pretty shades of red and green used in the above examples because, as with everything, color customization options exist. 🙂

    Also, you can apply multiple conditions to a single cell. Just repeat the process for each rule.

    In the ROI scenario I mentioned, I ended up applying about 15 different conditional formatting rules to my spreadsheet. It might sound like a lot of work, but it probably took me three minutes in total.

    My suggestion is to spend five minutes testing out the options so you have a sense of what they are capable of.

    If you read this post on creating a profit & loss statement, why not try adding some conditional formatting to the profit & loss statement you created in and/or downloaded from it?

    Oh, and in case you were wondering how I managed to get an automatic ‘yes’ or ‘no’ value in a cell, stay tuned for a future blog post on the subject, or sign up below to get exclusive, subscriber-only content.

    Learn how to make your spreadsheet results really *pop* using conditional formatting.Click To Tweet

    So, what do you think of this slick feature? Do you have any favorite conditional formatting tricks or tips? Do you have any questions about how to use it to accomplish your specific task? Start a conversation in the comments below! Till next time…

    Continue reading