If you click it, you can use the mouse to multi-select items without having to hold the Ctrl key down. There is a small icon with three ticks displayed at top right of the slicer header. You can hold the Ctrl key down to select multiple states on the slicer. Selecting a single state on the slicer will display one line on the chart. Select the States Margin % column and click OK. With a cell selected in the formatted table, click on the Design or Table Design ribbon tab and click on the Insert Slicer icon.Ī dialog box will display that enables you to select the column that you want to filter. It is much easier to use a slicer to filter a formatted table. I will explain this in the companion video, which you can watch by clicking the button on the top of this page.Įxcel’s normal filter interface is a bit cumbersome. It will enter a formula that is related to the formatted table. Click the Chart Title, click in the Formula Bar, press = and click cell A1. Filtering out rows removes them from the chart. The default setting for charts is to only plot the visible data.
SLICERS FOR EXCEL TABLES UPDATE
For example, if you use the filter icon in column A to show WA and NSW, then the chart will update accordingly, as per Figure 3. If we filter the formatted table, it will amend the chart. As we can see, this has quite a few lines that are all close to each other, making it difficult to read. It is worthwhile experimenting with the Switch Row/Column icon on all your charts, as it provides another way of viewing the data. The chart should now show the months going across the bottom axis as per Figure 2. To fix the chart, you need to have the chart selected and clicked in the Design or Chart Design ribbon tab before selecting the Switch Row/Column icon. The chart created is probably not the structure that you would have expected. Select a cell in the formatted table, click on the Insert ribbon tab and select a Line chart. If you have blank rows or columns, you will need to manually select the range of the table. Note: Blank rows and blank columns in the data can affect Excel’s ability to automatically select the correct range. Using the shortcut key to create a formatted table will apply the default table colour. If there are no blank columns or blank rows in your data, the range displayed should be correct. To convert the table into a formatted table, click a cell in the table and use the keyboard shortcut Ctrl + T.Ī dialog box displays, showing the table range to include. Adding a slicer makes it easy to pick and choose the states to plot on the chart. Plotting all five states on a chart can make it difficult to read (see Figure 2). The table we will use is shown in Figure 1. When you use a slicer with a formatted table, you can create an interactive chart. I have covered formatted tables in detail in a previous article.