Tutorials

  • 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.

    And this was plenty fine with me. After all, I dig troubleshooting more than running numbers.

    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).

    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 random, unmarked spreadsheet, which were allegedly about the feasibility of expanding into some newly unregulated territory. Could I decipher this spreadsheet for him? Like 10 minutes ago? In full disclosure, I may have received an email from him at 2am warning me that my expertise was needed. Not kidding.

    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, and that required two-hour manual updates. EVERY. FREAKIN’. DAY.

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

    Simply put, that job taught me more than any other just how cumbersome poorly designed spreadsheets are.

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

    And, it 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. How are these spreadsheets going to help you accomplish your goals?
    2. What are these spreadsheets going to contain?
    3. How will you structure them?
    4. Do you have enough expertise to create that which you envision?

    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 picking out fonts, border styles, etc. because you will change them 20 times over before you’re done).

    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.

    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 all of the time! – 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 (actually, I’m already working on it); 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), check out my signature Spreadsheet Alchemy service. Thanks! Until next time…

    Continue reading

  • How to Use Conditional Formatting to Identify Important Outcomes

    Just a heads up that if you are on my list, there is a mistake at the bottom of today’s email (10.19.16). In the spreadsheet tip of the week, I told you to enclose any text values in your formulas in ‘ ‘. Actually, you must enclose text values in  ” “. Sorry for the error!


    Now, onto today’s post.

    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.

    And, it’s not just for financials.

    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 sources of data for said client.
    4. My team then gets three weeks to complete the ROI.
    5. The reporting team then 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 X ROIs happening at a given time, and the reporting team never has more than Y ROIs at a given time. Oh, and there are financial penalties if we do not meet client deadlines.

    Can you see a migraine coming on?

    My friends, conditional formatting is your 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, a non-exhaustive list of the types of conditions you can require can include:

    • 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.

    Here’s how to apply conditional formatting in Excel:

    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 => 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 rules 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. Then, reply to your confirmation email and request to receive ‘In the Sheets’ for 10.19.16 (*Psst – it’s the tip of the week).

    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

  • How to Create a Basic Profit & Loss Statement (FREE DOWNLOAD)

    [10.28.19 UPDATE: I’ve added a premium spreadsheet – Profit & Loss Tracker – to my shop. I have also added a FREE version of this premium file, which is pretty wicked. If you need something more robust and automated than the free download included with this post, definitely check either of these other options out!]

    One benefit of subscribing to my list is that I offer you plenty of opportunities to engage with me (which is a benefit because I translate that into posts you want, spreadsheets you need, etc.).

    Subscribers often touch base with me after receiving my quasi-weekly broadcast (you can sign up to receive it here), where I offer a new tip for both Google Sheets & Excel users, a brief intro to the current blog post, promotional content & discounts, and other rad information.

    Well, one seriously awesome gal replied to said broadcast with a request for a profit & loss spreadsheet (aka an income statement).

    I’m an economist – not a finance or business expert (bet ya thought that they were synonymous, right?!), but nonetheless, I wanted to fulfill my awesome reader’s request.

    So, I googled examples of P & L statements that would be simple for y’all to set up on your own.

    My favorite example came from this post written by Dwight Fujimoto. The post is short and clarifies what to include/not to include in each section of the P & L statement, so please be sure to check out his post if you aren’t already well-versed on the topic.

    Okay, let’s dive right into this profit and loss beast!

    What’s included in a profit & loss statement?

    In order to create a profit & loss (or P&L) statement, it’s helpful to know what one tends to contain.

    Ground breaking, I know.

    For all of you business newbs, your P&L statement is also known as your income report.

    An income report contains:

    1. Your biz’s itemized revenue
    2. Your biz’s itemized costs/expenses
    3. Your biz’s net income

    Some exceptionally wicked P&L spreadsheets even come with charts.

    Can you see how awesome it would be to have this information readily available, even if you didn’t have a freakin’ clue as to what a P&L statement was?

    You can thank Selena (I’d link to her blog but I do not believe that she has one) for inspiring today’s post on how to create a basic P&L statement.

    And, you can also thank Dwight Fujimoto for inspiring this P&L statement’s layout.

    Finally, snag the exact same spreadsheets – those that we’re about to create – for free by clicking here 🙂 By clicking this link, you will be taken to my Google Drive, where you may access Google Sheets and Excel versions of this workbook. If you run into any issues, please let me know in the comments below!

    [UPDATE! While you can certainly download the free file above, I have a much more badass FREE P&L Tracker available here. It’s exactly the same as the one I sell in my shop except that it’s limited to 5 income and 5 expense categories, and 100 income and 100 expense transactions.]

    Take the mystery out of P&L statements with this tutorial and my free spreadsheets.Click To Tweet

    *Psst – This tutorial assumes a basic understanding on how to use spreadsheets. If you are a total spreadsheet virgin, please first work through my FREE From Spreadsheet Virgin to Goddess in 30 Minutes.

    *Psst again – If you have any questions about the formatting in this tutorial, please take a peek at this brief post.

    Here is a slideshow with screenshots of the process

    Here’s how to create a basic P & L statement

    I created this tutorial using Excel, but the process is the same for Google Sheets.

    Step 1: Open a new workbook

    Create the following spreadsheets:

    • P & L Summary
    • Income

    Step 2: Set up your Income sheet

    1. Create the following headers in A1:D1:
      • Date
      • Income Source
      • Income Type
      • Amount
    2. If you want to get really snazzy, add a drop-down list containing your different income types in column C (see this post if you don’t know how to do this – it’s super easy!).
    3. Select/highlight column D => right-click => ‘Format Cells’ => select ‘Currency’ from the ‘Category’ list in the ‘Number’ tab.
    4. Select A1:D1 => click the ‘Data’ tab => click the ‘Filter’ icon.
    5. Format your spreadsheet however you want! I would suggest changing the default font, adding color to your header row, and resizing your columns (*Psst – I cover how to do this here).
    6. Rename the spreadsheet by double-clicking on the ‘Sheet2’ tab and typing Income.

    Note: An example of an ‘Income Source’ would be Amazon affiliate commissions, while an ‘Income Type’ would be affiliate commissions (which would encompass affiliate commissions from all sources).*

    Step 3: Set up your Costs sheet (and create your Expenses sheet)

    1. Save yourself some time by right-clicking on the ‘Income’ tab => ‘Move or Copy’ => select ‘Move to end’ => check the ‘Create a copy’ box => click ‘OK’. Repeat.
    2. Double-click on the ‘Income (2) tab and rename it ‘Costs’.
    3. Double-click on the ‘Income (3) tab and rename it ‘Expenses’.
    4. Go back to your ‘Costs’ sheet and delete column C.
    5. Replace the word ‘Income’ with ‘Cost’ in B1.
    6. Resize your columns as needed.

    Note: Your costs are only those that are directly associated with the selling of your product, service, etc.*

    Step 4: Set up your Expenses sheet

    1. Go to your ‘Expenses’ sheet and replace ‘Income’ with ‘Expense’ in B1 and C1.
    2. Resize your columns if needed.
    3. If you created a drop-down list for your ‘Income Type’ column, you will want to update it to reflect your expense types.

    Note: Your expenses are those that are required for operating your biz that exist even if you stopped selling your product, service, etc. (e.g. rent, website hosting, etc.).*

    Step 5: Set up Your P & L Summary sheet

    1. Add your biz’ name in A1 => select A1:C1 => ‘Merge’.
    2. Type Profit & Loss Statement in A2 => select A2:C2 => ‘Merge’.
    3. Enter the time period in A3 => select A3:C3 => ‘Merge’.
    4. In A5, type INCOME.
    5. In A6, click the indent icon in the ‘Home’ tab (Excel only) and then type your first income type. Repeat for each income type (for simplicity, I’m assuming three types).
    6. Select/highlight column C => right-click => ‘Format Cells’ => select ‘Currency’ from the ‘Category’ list in the ‘Number’ tab.
    7. In C8, click the border icon in the ‘Home’ tab and choose ‘Bottom Border’.
    8. In A10, type TOTAL INCOME and bold it.
    9. In C10, type =SUM(C5:C8
    10. In A12, type COSTS and bold it.
    11. In C12, click the border icon in the ‘Home’ tab and choose ‘Bottom Border’.
    12. In A14, type GROSS PROFIT and bold it.
    13. In C14, type =C10-C12
    14. In A16, type EXPENSES and bold it.
    15. In A17, click the indent icon in the ‘Home’ tab and then type your first expense type. Repeat for each expense type (for simplicity, I’m assuming five types).
    16. In C21, click the border icon in the ‘Home’ tab and choose ‘Bottom Border’.
    17. In A23, type TOTAL EXPENSES and bold it.
    18. In C23, type =SUM(C17:C21)
    19. In A25, type NET INCOME and bold it.
    20. In C25, click the border icon in the ‘Home’ tab and choose ‘Double Bottom Border’. Then, type =C14-C23

    Note: Despite my love for beautiful spreadsheets, I would advise you to not add color to the P & L Summary sheet if you intend to print and distribute it. However, feel free to resize it, change its fonts and font sizing, and resize your blank rows, etc.

    Step 6: Populate your workbook & save it as a pdf or print it

    1. Fill out your ‘Income’ spreadsheet. Then, click on the filter arrow in your ‘Income Type’ header in C1 => deselect all but the income type you entered in A6 of your ‘P & L Summary’ sheet => select/highlight the cells that contain entries in column D and note the total in your progress bar at the bottom of your spreadsheet. Enter this in C6 of your ‘P & L Summary’ sheet.
    2. Repeat for your other income types.
    3. Now, fill out your ‘Costs’ spreadsheet. Then, select/highlight all cells that contain amounts in column C and note the total in your progress bar at the bottom of your spreadsheet. Enter this in C12 of your ‘P & L Summary’ sheet.
    4. Now, fill out your ‘Expenses’ spreadsheet. Then, click on the filter arrow in your ‘Expenses Type’ header in C1 => deselect all but the income type you entered in A17 of your ‘P & L Summary’ sheet => select/highlight the cells that contain entries in column D and note the total in your progress bar at the bottom of your spreadsheet. Enter this in C17 of your ‘P & L Summary’ sheet.
    5. Repeat for your other expense types.
    6. To print the ‘P & L Summary’ sheet, select A1:C26 => ‘File’ => ‘Print’. Although unlikely, you may need to check the ‘Scaling: Fit to’ box.
    7. To create a pdf of the ‘P & L Summary’ sheet, select A1:C26 => ‘File’ => ‘Print’ => select ‘Save as PDF’ from the drop-down in the lower left corner => adjust the name, etc. in the new box that appears => ‘Save’.

    That’s it!

    Feel free to download the exact P&L workbook that we just created in this tutorial by clicking here (no email required!). This link contains both Google Sheets and Excel versions. Please make sure that you are logged into your Google account if you want to use the Google Sheets version. Simply right-click on the files and then select ‘Make a copy’. That’s it!

    If you’d like a more robust and exceptionally wicked profit & loss/income & expense series of spreadsheets, be sure to check out my premium Profit & Loss Tracker. I’ve used it exclusively for several months now and freakin’ love it’s automations and ease of use. Just sayin’. ?

    Take the mystery out of P&L statements with this tutorial and my free spreadsheets.Click To Tweet

    Now it’s your turn! I’d love to hear from any of you lovelies who are finance professionals. What would you include in a P & L statement? As for the rest of you, what do you struggle with when it comes to tracking your biz’ finances? Please share your thoughts, suggestions, and questions below! Thanks a bunch 🙂

    Make your P & L spreadsheet even more wicked…

    If you are already exceptionally badass with spreadsheets and are comfortable defining names in Excel or Google Sheets, you can increase the functionality of these sheets by defining names for each income/expense type and entering ‘SUM’ functions referencing those names in column C of your ‘P & L Summary’ sheet.

    The spreadsheet tip of the week that all DLS subscribers received today (10.12.16) covers how to define names. If you would like to receive this email broadcast, subscribe now by clicking here or using the form below and reply to your confirmation email (or another DLS email) requesting the 10.12.16 broadcast so that I know to forward it to you.

    *HUGE DISCLAIMER: I am not a financial advisor, accountant, or budgeting authority (I’m just a recovering economist who digs spreadsheets). The purpose of this post is simply to teach you how to create a basic spreadsheet to track your income and losses. I strongly encourage you to work with a legit financial consultant to ensure that you are properly recording your income, losses, and other such tidbits that the IRS and/or other governmental entities require for reporting and tax purposes. 


    SaveSave

    SaveSave

    SaveSave

    SaveSave

    SaveSave

    Continue reading