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

• 
  How to choose your favourite online spreadsheet
  Unsure of which online spreadsheet program to choose? Find out here the features and benefits of both Microsoft’s Excel and Google Sheets, as well as some tips on how to pick the right one for your needs.
• 
  What’s the point of the Excel Information Functions?
  What are the information functions in MS Excel? How can you make use of the data they add into your spreadsheet. Read the post to learn more.
• 
  How to use Date and Time Functions in Excel
  Unsure how to work with dates and times in Excel? This guide will show you how, with a variety of date and time functions explained.
• 
  VLookup in Excel: What Do You Need to Know Step by Step
  Learn how to use the VLookup function step by step and you’ll find that it’s actually not as difficult as you might think. Check out this guide to some of the basics!
• 
  Excel IF Statement: What do you need to know
  Unsure of how the IF statement in Excel works? This guide will walk you through all the basics, as well as some more advanced features, so that you can start using this function to its full potential.