If you have time series data in your worksheet, visually showing changes over time can be useful. This will make it easy to look for any patterns, trends or other useful information in your data and make decisions based on them. You can visualize time series data with Conditional Formatting in Excel, where rules are applied to data to show increases in green and decreases in red.

Note: This tutorial uses Excel 2013. In other Excel versions, there may be some slight differences in the described steps.

Watch the video below to see how to visualize time series data with Conditional Formatting in Excel. If you prefer written instructions, then continue reading.

To visualize time series data with Conditional Formatting in Excel, two rules will be applied. To apply Conditional Formatting rules, take the following steps:

1. Have your data ready in Excel. For the purpose of this tutorial, suppose you have sales data for 12 months for 14 stores.

2. Select the sales figures except for the first month and click on the Conditional Formatting button in the Styles group on the Home tab.

3. In the opened menu, click on Manage Rules…, which is the last option. This opens the Conditional Formatting Rules Manager dialog box. Alternatively, you can use the keyboard shortcut ALT+O+D to open it. This dialog box offers all options you need to create, edit, delete and view all Conditional Formatting rules in your workbook.

4. In the Conditional Formatting Rules Manager dialog box, click on the New Rule button. The New Formatting Rule dialog box opens.

5. In the New Formatting Rule dialog box, select Use a formula to determine which cells to format option.

6. In the Format values where this formula is true box, enter the formula =C2>B2 and click on the Format button. The Format Cells dialog box opens.

7. In the Format Cells dialog box, click on the Fill tab, and then select the green color, the sixth color from the left in the bottom row.

8. Click OK in the Format Cells dialog box, and then click OK in the New Formatting Rule dialog box. The newly defined rule is now shown in the Conditional Formatting Rules Manager dialog box.

9. For the second rule, click on the New Rule button again in the Conditional Formatting Rules Manager dialog box.

10. In the New Formatting Rule dialog box, select Use a formula to determine which cells to format option.

11. In the Format values where this formula is true box, enter the formula =C2<B2 and click on the Format button.

12. In the Format Cells dialog box, on the Fill tab, select the red color, the second color from the left in the bottom row.

13. Click OK in the Format Cells dialog box, and then click OK in the New Formatting Rule dialog box. The new rule is added to the list of rules in the Conditional Formatting Rules Manager dialog box.

14. Finally, click OK in the Conditional Formatting Rules Manager dialog box to apply the rules to the selected cells.

The visualization of time series data with Conditional Formatting in Excel is ready. Now, when looking at the data, it is easy to see that, for example, there was a continuous decrease in sales in Store 1 from March 2017 to September 2017. Or, sales continously increased in Store 9 except for September 2017. You can also observe that there were more decreases in sales in the first half than in the second half of the year. You can then examine these figures in more detail, but for now, this visualization gives a good first overview about the data.

As you see, Conditional Formatting is a convenient way to present time series data clearly and effectively in Excel. Now, it will be easy for you or the people you share your data with to visually explore and analyze your data quickly.

What other ways of visualizing time series data do you find useful? Write in the comment section below.