(I don’t usually post two days in a row, but since yesterday was so conceptual, I wanted to follow up with an Excel skill that you can use right away!)
I’ve written pretty extensively about the joys and merits of using the “Format as Table” technique in Excel and Google Sheets. In fact, these posts have far and away the most views out of all of my blog articles! (There might be a some sort of data glitch with WordPress because it’s not clear to me WHY these articles are orders of magnitudes more popular than others… so if you figure it out, let me know!). But in any event, Tables are like the bread and butter of a spreadsheet; Once you format as table… sorting, filtering, subtotaling, and more become easier and more intuitive.
But the problem is that you have to choose from Excel’s “built in” table formats, which might not exactly suit your needs! Maybe you want colors to match your organization’s logos, or you want to reverse the order of the table’s alternating colors row by row. Maybe you want to keep the color scheme, but change whether or not the first column has a thick border. Introducing… Custom Table Styles! The DIY approach to Excel Tables color schemes… and more!
Make your table, and keep it too!
Not as good as cake, but better than nothin! Here are step by step instructions for how to “format as table” and follow it up with your own Custom Table Style if none of the options appeal to you.
- Select the data that you want to be included in your table (top left, highlighted blue)
- Select “format as table” in the toolbar, or use the keystroke combination “CTRL + T”
- Open the “Custom Table Style” dialogue box
- If you are in the “Home” menu, (pictured above), you will see the “New Table Style” button at the very bottom of the Table Styles menu (highlighted in yellow)
- If you are in the “Design” menu (Design tab would be highlighted, and you would see some Table Styles previews in your menu bar), click the arrow buttons on the far right to expand the Table Styles preview and then select “New Table Style” at the very bottom.
- This will launch a new window where you can select title rows, subsequent rows, subtotal rows, as well as columns, and create formatting rules. Make sure to select the “Format” button to see additional options for colors, patterns, and borders. As you format different aspects of the table, they will appear bold in the dialogue box. Don’t forget to give your Custom Table Style a descriptive name!
- After you create your first Custom Table Style, it will show up in the “Format as Table” dialogue box above the “light” colored options. You can click on it to apply the Style to your table. You can right click it if you need to add further modifications or even delete a Custom Table Style that’s no longer in use.
In the privacy of my own Excel window, I created Table formats with stripes, polka dots, and unconventional color choices! Be creative!
HOWEVER… just because you CAN create wild and crazy Custom Table Styles, does that mean you SHOULD? This is a very important question, and one that I hope changemakers will ask themselves early and often!
Sharing is caring
Here is what I hope changemakers will internalize. A spreadsheet isn’t a solo activity. In fact, spreadsheets make the most sense as a shared resource, whether that’s a program budget, RSVPs, a schedule, or a grocery list. Whatever the purpose, you might think it’s just for you at first, but USUALLY spreadsheets end up getting shared, and sometimes that’s where the confusion begins. If you go into a project expecting it to be shared, you can leave a “breadcrumbs trail” to help your collaborators or your successors make heads or tails of what’s going on in Excel.
Depending on your audience, the colors and formatting you select can make a big difference. I would argue that other features you might include -like Named Ranges and a “Read Me” tab – are even more important, but don’t let that stop you from utilizing this super easy technique to make your sheet more visually appealing! Whatever you determine for colors and formatting, make sure that your spreadsheet is clear, crisp, and easy to read. To many colors and patterns can add confusion!
Here are my tips for WHEN to use Custom Table Styles:
Change my mind
I would recommend using Custom Table Styles when your spreadsheet is for an external audience and you are looking for a consistent “look and feel” (as a communications or branding expert would say!) across your website, flyers, logo, and presentations. If you are making spreadsheets to persuade an elected official or corporate CEO, or a budget report to high-falootin’ board members, the added touch of color can go a long way. Make the Style once, and then use it whenever it is needed!
X marks the spot
Your data may be structured unconventionally, so it becomes helpful to highlight where the important information is. Use a Custom Table Style to add a highlight or bold or border and guide your users to find the info! Other options might could be manual formatting or using conditional formatting – but if you need a repetitive pattern (like every other row), Custom Table Styles are definitely the best choice.
An apple a day
Teachers might enjoy using Custom Table Styles to make more engaging and whimsical designs in Excel, whereas the standard styles are geared towards a “business” audience. Make a “Table Style” once and save it as a template, and you can make charts for your classroom all year long!