Formatting and it’s Impact on Excel Analytics

One of the features of Excel is it’s ability to store large amounts of data in a logical format. Not only to store the information but also to perform analytics on that information.

As our data gets larger, often we add formatting – to make it easier to read, or to see specific information. However, did you know that some formatting can stop you from using these analytic features?

While some formatting has no impact on the features, others can stop the analytics functions from working.

Analytic Functions

A good place to start is which analytic features am I talking about?

Sorting

Sorting is one of the most used options when dealing with information.

Excel allows you to sort on a specific column of data, and re-sort on another as many times as you need.

While the default is to sort on a single column, you can also perform a custom sort – sorting on multiple columns to get your data into the perfect order for you.

Filter

The default filter options, allows you to interrogate your data, showing and hiding rows based on what “questions” you’re asking.

The filter is switched on. when drop-down arrows show in the first row. However, look at the rows numbers – in filtered rows they appear blue.

How can you tell that a filter has been applied?

You can see the filter is switched on, when you have arrows in the first row of your list. But you can see that a filter has been applied, by looking at the rows numbers – they will appear blue.

Formatting that doesn’t work with your Analytics

What formatting options will have an impact on using these functions?

Blank Line/Row

This is most often the culprit for problems in sorting and filtering not working as expected.

Leaving a blank line (or two) between sections of your data, can make it easier to see and read your data, but as I mentioned in a previous blog (Moving Around Excel – Do you know these shortcuts?), Excel sees it as the end of the information.

Solution – keep all your information together, removing any blank rows.

Merge and Centre

As you can guess from it’s name, this option, will merge the selected cells together, and centre the text.

It’s a quick and easy option – great for title(s) of your sheet.

However, if it’s part of the selected data, when you sort or filter Excel will display an error message.

Solution – there are two alternatives that you can use.

1. use ‘Center Across Selection’ (available in the Formatting Dialog Box), which doesn’t merge the cells.
2. use blank lines to separate the title from the data.

Formatting that does work with Analytics

Most of the font formatting will not effect any of these analytical options.

Including:

• Font
• Colour
• Size
• Bold
• Italic
• Underline
• Number Format (Currency, Percent, Decimal Places)

You can use Cell Styles:

• Conditional Formatting
• Format As Table
• Cell Styles

When thinking of formatting – pick the options that will make it easier to view your data, but won’t stop you using an of Excel’s commands.

Related Posts

• 
  Excel Paste Values – Get Exactly What You See, Every Time
  If you’ve ever copied something in Excel and ended up with a completely different result than you expected, you’ve probably fallen foul of one of Excel’s little quirks. The good news is that once you understand Excel Paste Values, you’ll know exactly how to handle it – and it only takes a couple of extra… Read more: Excel Paste Values – Get Exactly What You See, Every Time
• 
  Excel Sparklines: The Tiny Charts Making a Big Difference
  If you’ve ever stared at a column of numbers and thought “I just want to see the pattern” — Excel Sparklines are about to become your new favourite thing. These miniature charts sit inside a single cell, right next to your data, giving you an instant visual snapshot without the faff of building a full… Read more: Excel Sparklines: The Tiny Charts Making a Big Difference
• 
  Do You Know All the Excel Flash Fill Tricks?
  If you’ve ever exported data and thought “why is everything jumbled together in one column?”, Excel Flash Fill is about to become your new best friend. It’s one of those features that looks impressive but is actually really simple to use — and once you know it, you’ll find yourself reaching for it again and… Read more: Do You Know All the Excel Flash Fill Tricks?
• 
  The Complete A-Z Guide to Excel Keyboard Shortcuts: Master Your Productivity
  When most people think about keyboard shortcuts, Microsoft Word typically comes to mind first. However, as an Excel enthusiast, I’m here to tell you that Excel has its own powerful set of keyboard shortcuts that can dramatically improve your productivity. Today, I’ll guide you through the complete A-Z of Excel’s keyboard shortcuts for Windows users.… Read more: The Complete A-Z Guide to Excel Keyboard Shortcuts: Master Your Productivity
• 
  Complete List Of Options Where You Can Customise the Colour
  When it comes to colour in your office files, we rarely move beyond changing the font or fill colour. But there is so much more to play with when you customise the colour. Read through the post, or jump to your favourite office suite – Google or Microsoft. Google Let’s start with Google, as it’s… Read more: Complete List Of Options Where You Can Customise the Colour