intermediate level

  • 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

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

    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 files 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 50 income and 50 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 gallery of screenshots detailing the process that follows

    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 use it exclusively 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.

    *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

  • How to Create Drop-Down Lists in Spreadsheets

    If you caught last week’s video tutorial that walked you through how to organize your Pinterest boards and pins using spreadsheets, you may have noticed that there were drop-down lists in the example.

    Perhaps you thought to yourself how marvelously wicked drop-downs were.

    Maybe you even got a wee bit of spreadsheet envy?

    After all, you know that your own spreadsheets would seriously benefit from drop-down lists.

    Drop-down lists can:

    • Save you time (less typing)
    • Increase your efficiency (less typing, less thinking)
    • Reduce error (no auto-populating the wrong thing, reducing grammatical errors)
    • Reduce the number of similar but different entries

    So, why aren’t you using them?

    My guess is that you are just simply not aware of how ridiculously easy they are to create.

    Add some magic to your spreadsheets with easy-to-create drop-down lists.Click To Tweet

    How to create drop-down lists in Excel

    Option A: Create your list in a separate spreadsheet (don’t worry – you can hide this tab so that it doesn’t clutter up your workbook).

    1. Create a new spreadsheet and rename it drop-down lists.
    2. Add a descriptive phrase to describe your list in the first row (A1).
    3. In A2, list the value you want displayed at the top of your drop-down list.
    4. Continue on in this vein (e.g. enter the value you want displayed in the #2 spot in A3) until you have entered every item you want displayed in your list.
    5. Now, go back to your original spreadsheet and select the cell or range of cells where you want your drop-down menu to appear. Then, click ‘Data Validation’ (or ‘Validate’) in the ‘Data’ tab => choose ‘List’ from the drop down list under the ‘Allow’ field => click in the ‘Source’ field => go back to your ‘drop-down lists’ spreadsheet and select your list, excluding the header text => hit ‘Enter’ => click ‘OK’.

    Option A considerations: Create separate drop-down lists for each location (but keep them in the same spreadsheet), even if the values are the same. This will make it easier to see at a glance what your drop-down lists are, and it allows you to change individual lists as needed.

    Here is a gif tutorial of this process:

    If you aren't using drop-down lists in your spreadsheets, why aren't you? Having drop-downs in your spreadsheets can save you time, increase your efficiency, and reduce errors. Plus, creating them is easy to do! In this post, I show you how to add drop-down lists to your spreadsheets, gif tutorials included!

    Option B: Create your list within the cell or range of cells.

    Select the cell or range of cells where you want your drop-down menu to appear. Then, click ‘Data Validation’ (or ‘Validate’) in the ‘Data’ tab => choose ‘List’ from the drop down list under the ‘Allow’ field => click inside the ‘Source’ field and type in each word or phrase for your drop-down list followed by a comma (do not add a space after the comma) => click ‘OK’.

    Option B considerations: I recommend using this option for short drop-down lists.

    Here is a gif tutorial of this process:

    If you aren't using drop-down lists in your spreadsheets, why aren't you? Having drop-downs in your spreadsheets can save you time, increase your efficiency, and reduce errors. Plus, creating them is easy to do! In this post, I show you how to add drop-down lists to your spreadsheets, gif tutorials included!

    How to create drop-down lists in Google Sheets

    Option A: Create your list in a separate spreadsheet (don’t worry – you can hide this spreadsheet so that it doesn’t clutter up your workbook).

    1. Create a new spreadsheet and rename it drop-down lists.
    2. Add a descriptive phrase to describe your list in A1.
    3. In A2, list the value you want displayed at the top of your drop-down list.
    4. Continue on in this vein (e.g. enter the value you want displayed in the #2 spot in A3) until you have entered every item that you want displayed in your list.
    5. Now, go back to your original spreadsheet and select the cell or range of cells you want your drop-down menu to appear. Then, right-click ‘Data validation’ => select ‘List from a range’ in the ‘Criteria’ field => click the grid icon => click back to the spreadsheet => click on the ‘drop-down lists’ tab => select all of the cells in the list, excluding the header => ‘OK’ => ‘Save’.

    Option A considerations: Create separate drop-down lists for each location (but keep them in the same spreadsheet), even if the values are the same. This will make it easier to see at a glance what your drop-down lists are, and it allows you to change individual lists as needed.

    Here is a gif tutorial of this process:

    If you aren't using drop-down lists in your spreadsheets, why aren't you? Having drop-downs in your spreadsheets can save you time, increase your efficiency, and reduce errors. Plus, creating them is easy to do! In this post, I show you how to add drop-down lists to your spreadsheets, gif tutorials included!

    Option B: Create your list within the cell or range of cells.

    If needed, select the cell or range of cells where you want your drop-down menu to appear. Right-click ‘Data validation’ => select ‘List of items’ and then type in each word or phrase for your drop-down list followed by a comma (do not add a space after the comma) => click ‘Save’.

    Option B considerations: I recommend using this option for short drop-down lists.

    Here is a gif tutorial of this process:

    If you aren't using drop-down lists in your spreadsheets, why aren't you? Having drop-downs in your spreadsheets can save you time, increase your efficiency, and reduce errors. Plus, creating them is easy to do! In this post, I show you how to add drop-down lists to your spreadsheets, gif tutorials included!

    How to force your spreadsheet users to only select items from your drop-down list

    Both Excel and Google Sheets allow you to force users to only select choices from the drop-down list. If you would like to enable this option, in the data validation pop-up:

    • In Excel: Click the ‘Error Alert’ tab and choose ‘Stop’ in the ‘Style’ drop-down list, then enter whatever error message you’d like your user to receive if something invalid is entered.
    • In Google Sheets: Click the ‘Reject input’ option.

    That’s it!

    There are a few other options you have when it comes to how your drop-down lists behave – just play around with them in the ‘Data Validation’ box if you are curious.


    Did you find this tutorial helpful? Do you have any questions about creating drop-down lists in your spreadsheet program of choice? Leave your questions or comments below!

    Add some magic to your spreadsheets with easy-to-create drop-down lists.Click To Tweet

    And, if you haven’t already done so, why not sign up now to receive my weekly spreadsheet tip, blog post teaser, special offers, and other goodies, plus access to all of TSA’s wicked freebies? Just click here or sign-up below 😉 Woo-hoo!

    Continue reading