Tutorials

  • How to Hide or Replace #DIV/0! Errors in Excel or Google Sheets

    Error messages detract from a table’s aesthetic.

    A common practice of spreadsheet creators is to pre-populate tables with formulas.

    You know, so that when data magically appear, the table magically spits out results.

    This is all well and good, but when division is involved and blank cells are referenced in the denominator, the result is an ugly #DIV/0! error.

    In tables that are several rows deep, this error can really be hard on the eyes.

    One place where we will almost certainly encounter this issue is in budgeting workbooks.

    For example, we may want to know what proportion of our total monthly income is attributable to each income source.

    Given that I wrote this post in June, the July table below doesn’t yet contain any income figures.

    The result is a lovely error message in column C. What happened is that the formula was trying to divide income by an empty cell, which is the equivalent of zero*:

    div0 error

    *Note that I am using Excel screenshots in this post, but what I am teaching you applies equally to Google Sheets.

    Changing or hiding the error text #DIV/0! is easy!

    Note: the same solution applies when it comes to hiding other errors.

    It’s super simple to change or altogether hide the error text – just use this function if you don’t want an ugly error message staring you down:

    =IFERROR(value, value if error)

    • Replace ‘value’ with the formula or function (do not include =) – in this case, it’s B2/B6; AND
    • Replace ‘value if error’ with what you want Excel to show instead of an error message. If you want the cell to appear blank, enter “”; if you want to replace it with ‘Hello! It isn’t July yet!’, enter “Hello! It isn’t July yet!”.

    So, enter this in C2 if you want column C to not display anything until income data have been entered:

    =IFERROR(B2/B6,””)

    Or, enter this in C2 this if you want column C to instead display ‘Hello! It isn’t July yet!’:

    =IFERROR(B2/B6,”Hello! It isn’t July yet!”)

    *Note: if you plan to fill or drag the formula down instead of copying and pasting it, be sure to change the B6 cell reference to B$6. Adding ‘$’ tells Excel or Google Sheets to always reference the column or row that immediately follows.

    Here’s the result:

    div0 text changed

    Simple, no?!

    Spreadsheet DIV/o! error messages suck. Here's how to hide or replace them with playful text.Click To Tweet

    If you’d dig other tips like this, download my free From Spreadsheet Virgin to Goddess in 30 Minutes. Also, here’s another cool post about different (easy!) ways to add drop-down lists to your spreadsheets. Enjoy 🙂

    Do you leave #DIV/0! errors alone, or do you use this function to hide or change them? Do you have any favorite clever error replacement messages you like to use? Share below!

    How to hide error messages in Excel or Google Sheets. Busy spreadsheets filled with errors are hard on the eyes, but it's easy to hide these unsightly errors or replace them with fun text. Let me show you how!

    Continue reading

  • How to Make Your Excel Spreadsheets Look Sleek & Sexy

    If I had collected $1 every time I heard someone lament over how ugly most spreadsheets are, I’d probably have half of my daughter’s college education funded by now.

    Styling spreadsheets seems to be a rare after thought for many, which is a shame.

    Why do people create & share ugly spreadsheets?

    My take is that people fail to style their spreadsheets for one of two reasons:

    • They either don’t know how; OR
    • They piecemeal them together without keeping design in mind (I am sooo guilty of this!).

    Whatever the reason, ugly spreadsheets are a bitch to look at and an even bigger bitch for others to look at.

    The more complex a spreadsheet is, the more important it is to style it.

    That’s because the eyes need a road map in order to follow what’s going on.

    But, even for simple spreadsheets, a styled one evokes more professionalism than an unstyled one. And, if your spreadsheets are styled with your branding in mind, the professionalism vibe is magnified.

    Seriously, it will take you maybe five minutes to prettify a single spreadsheet.

    What are you waiting for?!

    [Note: as usual, I created this post/tutorial using a Mac, and the instructions provided within reflect this. While there are some differences between pc and Mac versions of Excel, most are subtle and primarily have to do with where an element is located in the ribbon. If you need assistance, please leave a comment!]

    Here’s how to style and add a logo to your spreadsheets to make them match your branding:

    First of all, I need to be upfront and tell you that this tutorial is intended for Excel users only because Google didn’t get the memo from Microsoft about how to format spreadsheets.

    Seriously.

    But, if you are a Google Sheets user and haven’t already done so, check out my FREE guide From Google Sheets Virgin to Goddess in 30 Minutes, which walks you through how to format your spreadsheets in a similar fashion (minus adding a logo – that will have to be a future blog post).

    *Psst – my FREE guide is for Excel users, too.

    Okay, so I’m going to assume that you already have open an Excel workbook that’s ready to be styled. If you don’t, you can borrow mine.

    Also, in order to effectively brand your spreadsheets, you will need to know the exact RGB color code you want to use (pc users) or either the Hex or RGB code (Mac users).

    Wait – don’t be scared! It’s easy to find out what they are if you don’t already know.

    Step 1: Decide whether you want to change the background color of your spreadsheet.

    If you don’t, just skip this step (don’t do this simply to hide gridlines – we’ll do that in Step 4). If you do want to change your background:

    1. Click the arrow in the upper left corner of your spreadsheet to select the entire sheet.
    2. Click the paint bucket icon (‘Fill Color’) => ‘More Colors’. Then,
      • If you have an exact color that you want to use, enter the RGB or Hex code in the fields provided; click ‘OK’.
      • If you don’t have an exact color that you want to use, just choose one that you like; click ‘OK’.

    Step 2: Add color to your spreadsheet’s table(s):

    1. Select your entire table => ‘Table’ tab => click the down arrow below the ‘Table Styles’ box => ‘New Table Style’
    2. In the ‘New Table Style’ pop-up, name your table.How to Style Your Spreadsheets
    3. Select the items that you want to format for your table from the ‘Table Elements’ list. In this example, I show you how to format a table that has subtle gray dotted borders, and has one color for the header row, and alternating colors – white and a pale pink – for the remaining rows. Here are the formatting options I chose:
      • ‘First Row Stripe’ => ‘Format’ => ‘Fill’ => ‘Color’ dropdown in the ‘Background’ box => ‘More Colors’ => ‘Hex Color #‘ fce3e7 => click ‘Enter’ => ‘OK’. Then,
      • ‘Header Row’ =>’Format’ => ‘Fill’ => ‘Color’ dropdown in the ‘Background’ box => ‘More Colors’ => ‘Hex Color #‘ f7aab8 => click ‘Enter’ => ‘OK’. Then,
      • ‘Whole Table’ => ‘Format’ => ‘Border’ => the dotted line in the ‘Style’ box => the top grey option in the ‘Color’ drop-down => clicked on ‘Outline’ and ‘Inside’ options under ‘Presets’ => ‘OK’ =>’OK’ => ‘OK’.

    That’s it – you just created a customized table option with your (or my) unique branding! It’s now saved at the top of your table options in the ‘Format as Table’ menu, and will remain available for you to use whenever you like.

    Yay!

    Step 3: Change your fonts

    How often do you change the fonts in your spreadsheets?

    Seriously, this simple little tweak can really make your spreadsheets come to life!

    I like to limit the number of fonts I use to no more than 2: one for my column and/or row headers, and one for everything else (in this example, I use the same font for both).

    To do this:

    1. First, change the table’s font:
      1. Select the entire table. Then,
      2. Select your desired font from the drop-down list (I chose ‘AireBoldPro’, which is a custom font I own). Then,
      3. Change the size of your font, if desired.
      4. If your row height doesn’t change to accommodate the new font, hover your mouse at the bottom of the row number until a double-ended arrow appears. Double-click or manually drag the row upward/downward to re-size it. You can also right-click on the row =>’Row Height’ and enter in the height you wish => ‘OK’.
    2. Next, change the font size for the header row:
      1. Select row 1. Then,
      2. Change the size of your font (I chose 16).
      3. Re-size the row if needed (see Step 3 #1.4).
    3. Finally, re-size your columns if the font changes whacked them up:
      1. To re-size all of them at once: Select all of the columns => hover at the right edge of the last selected column until a double-ended arrow appears => double-click.
      2. To re-size them individually: Select the column to be re-sized => hover at the right edge of the column until a double-ended arrow appears => either double-click or manually drag the column outward/inward. Boom!

    How to Style Your Spreadsheets

    Step 4: Hide gridlines, headings, and/or the Formula Bar

    1. Go to the ‘Layout’ tab in the ribbon.
    2. Deselect either of the following if you’d like an uncluttered-looking spreadsheet:
      • ‘Gridlines’
      • ‘Headings’
    3. Alternatively, go to the ‘View’ menu option at the very top of the window (for pc users, just go to the ‘View’ tab) and select/de-select the relevant options in the drop-down menu.

    Step 5: Add your logo

    If you’d like to add your logo to either the header or footer of your spreadsheets (did you even know that you could do this?!), do the following:

    1. Go to the ‘Layout’ tab in the ribbon => ‘Header & Footer’. A ‘Page Setup’ box will appear.
    2. Choose ‘Header/Footer’ => select ‘none’ from the ‘Header’ dropdown list => click ‘Customize Header’. In the ‘Header’ box that appears, click in the ‘Center section:’ box => choose the image icon => select your file and click ‘Insert’. Now,
    3. Unless the file you uploaded has tiny dimensions, you will need to make some formatting adjustments*. Click ‘Format Picture’ => change ‘Height’ to something like 0.45″ (that’s what I chose), and keep the aspect ratio and relative size boxes checked => ‘OK’ => ‘OK’ => ‘OK’.
    4. Click on ‘Page Layout’ under the ‘View’ box to see what your logo looks like. You can tweak it by clicking on ‘Header & Footer’ => ‘Customize Header’ => click on ‘&[Picture]’ => click on ‘Format Picture’ => change as needed => ‘OK’ => ‘OK’ => ‘OK’.

    *Just a note that if you don’t re-size your logo sufficiently, it will “spill over” into the background of your spreadsheet (not entirely dissimilar to the way that a watermark would behave). You may or may not dig this effect, but know that if you change the background color, you will end up coloring all but the header area and your logo will look terrible. So, consider this when re-sizing your logo. Stick to a height of 0.4″ and you shouldn’t run into any issues.

    How to Style Your Spreadsheets with Logo

    Woo-hoo!

    Your spreadsheet is now more wicked than 99.9999% of every other spreadsheet on the planet!

    Step 6: Freeze panes*

    If you’d like to always be able to see your header row and/or first column (or columns):

    1. Scroll to the beginning of your spreadsheet so that A1 is visible. Then,
    2. Click in the cell that is immediately below and to the right of the information you want to continuously view. In our example, I always want to be able to see the header row, and the ‘Account Name’ and ‘Account Type’ columns. So, I clicked in C2. Then,
    3. Go to the ‘Layout’ tab in the Ribbon => ‘Freeze Panes’ button => ‘Freeze Panes’.

    *Note that if you added your logo in Step 5, you will not be able to view your spreadsheet in the ‘Page Layout’ view (the view that will display your logo – like the image displayed above).

    Step 7: Save your workbook as a template

    If you dig your newly branded workbook, why not save it as a template so that it can be used over and over again?

    1. First, make sure to save your workbook. Then,
    2. Do a ‘Save As’ => choose ‘Excel Template’ (.xtsx extension) from the drop-down list => name it something cool like ‘My Awesome Branded Template’ => ‘Save’. Then,
    3. Delete any information contained in your workbook that you don’t want to remain as a part of your template. Your workbook may contain only one sheet with your logo in the header, or it may contain 20 sheets with lots of budgeting tables that will only be receiving minor monthly updates. The goal is to make your life easier, so do whatever works.
    4. ‘Save’.

    The next time you want to create a spreadsheet that already has your branding, just open up this template and do a ‘Save As’ so that you always retain a branded template for future projects.

    Yippee!!

    So, there you have it! Your spreadsheets are tailored to match your branding!

    Now, why not use your newfound styled spreadsheet wickedness and offer your readers a content upgrade using pretty spreadsheets?

    People LOVE spreadsheets (seriously!), and offering them up may just kickstart your email list’s growth!

    Stop creating & sharing ugly spreadsheets! Let me show you how to make them pretty & on-brand.Click To Tweet

    Anyway, I’d love to hear from you! Do you brand your spreadsheets? Do you have any additional tips to offer up? And, as usual, please feel free to ask me questions. Thanks!

    There is nothing worse than ugly spreadsheets! Learn how to make your spreadsheets sleek and sexy in Excel by adding a log and styling them to match your branding.

    Continue reading