Charts vs. Pivot Tables: Displaying Dated Data
There are some great features in charts and pivot tables for Excel, especially when it comes to displaying data with dates and date ranges attached. Often, you may want to take a closer look at when you saw the most sales, what activities you had going on, or when you had leads coming to your business. Those date ranges may be divided by specific dates, by months, or by week, depending on how you want to sort your information–and Microsoft Excel makes it easy to sort and present that information in the most effective way possible. Whether you choose to use Charts or Pivot Charts to help display that data, there are several options in Excel that can make it easy to format and display your data.
Organizing Dates
If you’re starting with a list of dates in an Excel spreadsheet, you can quickly and effectively transition those dates to a chart. First, you can use Alt + F1 as a shortcut to create a chart.
If you put in a chart with a wide range of dates, you may have a number of empty dates in the space. For example, suppose that you put together a chart that covers a two-month span (with three separate months), as in the example provided in the video. The chart automatically fills in any missing dates, leaving you with multiple empty dates on your chart–which doesn’t offer the clean, sleek appearance you would normally expect from your charts.
Right click and select “Format Axis.” The menu will open on the right-hand side of the screen and offer you several options that will allow you to clean up your charts.
Check your date range and make sure that it accurately reflects the dates you want to show. You may, for example, want to use a smaller portion of that data, including eliminating dates before or after the start date that don’t fit the information you’re trying to show; or you may want to group those dates together in a neater, more effective way. Select the “units” option. You can organize your data by days, by weeks, or by months. Choose the option that offers the cleanest appearance while still displaying the data you want to include in your chart. In our example, you can see that organizing the data by months still offers a clear look at the information for those key date ranges, but no longer includes unnecessary space in the chart.
Date Formatting
You can also change the formatting of your dates in Excel so that it displays the way you want it to. Instead of displaying as a numeric date, or as a numeric date range, you can go to Number in the formatting menu, then down to the “Date” option. Under “Format Code,” select the format that makes the most sense for your specific needs.
For example, if you want to format your months as a three-digit abbreviation, you could use code “mmm,” which will give you the abbreviations for each month in your chart. You can also choose:
- m: 1-12 month format
- mm: 01-12 month format
- mmmm: Months in written format (January-December)
- mmmmm: Months as the first letter of the month
- d: 1-31 day format
- dd: 01-31 day format
- ddd: Days as three-letter abbreviation format (Sun-Sat)
- dddd: Days as written format (Sunday-Saturday)
By using a customized date entry form, you can ensure that your information displays exactly as you intend, no matter how you want that data to appear.
Pivot Tables and Pivot Charts
In some cases, you may want to use a pivot table or pivot chart to display your data. This format can make it easier to insert your data into a chart and may offer a cleaner, faster way to create the look you want for your data. To insert a pivot table, go to the “Insert” tab, then click on “Pivot Table.” Select the range you want to use for your pivot table, then select where you want to put it. You may want to put the pivot table in a new page to help keep your data clean.
A pivot table can provide you with additional insights into your specific data. It allows you to drag the information you want to see, based on the data in your cells, to the “Pivot Table Fields” option and organize it according to your specific needs. With a pivot table, you can organize by months, but open the additional information in the cell by clicking on the “+” icon, which will allow you to see each date individually–which means you can easily reference all the information you need based on what you have entered into Excel, rather than being limited by the terms of a specific chart.
Analyzing Pivot Table Data to Create a Pivot Chart
Once you’ve created those fields, you can click on “Analyze Pivot Table.” Select “Pivot Chart.” Make sure that the chart displays the information you want it to display, based on the data you have presented, then click “Okay.” With this strategy, you can get the same data that you would get in a standard chart, but much more easily and efficiently.
Once you have created your pivot chart, you may want to hide the information you don’t need, including the gray boxes that allow you to hide or change that data. Simply click in one of the boxes, then select “Hide All Field Buttons on Chart.” With this simple strategy, you can ensure that the information you present looks exactly like you want it to look in a smooth, effective way.
Creating charts and pivot charts can help you display information within a specific date range, make it easier to analyze, and provide you with a highly visual way to share that information with your team. With these strategies, you can more easily make Microsoft Excel work for you and ensure that you’re presenting that information–and creating your charts–in the most efficient way possible.
