Let’s take a look. Here we have a pivot table showing sales by Region. Let’s add a column chart. Notice our chart is using the general number format. That’s because we haven’t yet set a format in the pivot table. Let’s do that now. Notice that both the pivot table and the pivot chart pick up the currency format. Now since they take up a lot of space, let’s remove field buttons that appear in the chart. Next, let’s make all the text in the chart a dark gray. Just click the chart, make sure you’re on the Format tab, and use the Text Fill button. You’ll see a live preview as you hover over colors. Let’s also make the chart axis, gridlines, and border gray. Carefully click to select the gridlines, then use the Shape Outline tool to set the color. For the axis, click the axis text, then use the Shape Outline tool. Finally, select the chart and set the border color. While we’re here, let’s also make the border thicker. Next, we’ll add a descriptive title and subtitle. For the Title, click once to select, then double-click to edit the text. [Total Chocolate Sales by Region] We’ll use a smaller type for the subtitle. With column charts, it’s a good practice to sort the columns by size. We can easily do this by clicking a value in the pivot table, and sorting Z-A. Now the chart is easier to read. Next, let’s get rid of the legend. We could apply a layout preset, but layouts make more than one change. Instead, we’ll just work with the controls on the layout tab. Here we can easily set the Legend to None. In this same area, you’ll find detailed controls for all the main elements in the chart. Let’s also add data labels inside the columns, and then set the text to white. As you can see, the numbers don’t quite fit, so let’s set the values to display in thousands. The Layout tab provides a way to do this directly, but the result requires another label to make things clear. So let’s Undo, and apply a custom format to the Sales values in the pivot table that uses “k” to indicate thousands. The comma in this case causes Excel to drop the last 3 digits in the number. As a final step, let’s make the top region a different color. Click once to select all columns, then once again to select a single column. Then format as desired.
Dave Bruns
Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.