formatting

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