Therefore, we need to set up a formula that forces the FigNum formula still to return 1, 2, or 3 if someone chooses multiple items in the slicer. But even if we specify single-select-which is the option we want-users still can override that setting. We can set up Slicers to be either multi-select or single-select. Third, replace the subtotal formula in cell B6 with this formula:ī6: =IF(SUBTOTAL(2,)>1,2,SUBTOTAL(109,)) Note, by the way, that cell B6 is the FigNum cell that the Camera tool references in your Report sheet, which you learned about in Interactive Dashboard Magic with Excel Slicers, To do so, select the range A6:B6 press Ctrl + Alt +A3 to launch the Create Names dialog if Excel ask if you want to change the definition of FigNum indicate that you do and then choose OK. Second, assign the FigNum range name to cell B6. That will give you a result like this figure.įirst, type FigNum to replace the Total label. In the Table Tools, Design, Table Style Options group, check Total Row.
#Create a slicer in excel how to
Because Excel set it up correctly by default, I then pressed OK.Īfter you set up the Table, here’s how to set up the Total row in row six,… I selected a cell in that little table, then pressed Ctrl + T to launch the Create Table dialog. To set it up, I first entered the text and numbers you see in rows 2 through 5. Here’s the Slicer Table I set up for the example at the top of this page. Here’s the general strategy for using slicers to control settings in your workbook:īecause slicers can control only PivotTables and Tables, you set up either a tiny Table or a pivot for your slicer to control, then you set up formulas that translate those changed tables into the settings you need. To see how I dealt with those issues, let’s set up my slicer from scratch… Set Up Your Slicer Table That’s fine, but what if you want to use them for other purposes? Second, they do take more work to set up than the other two methods. First, they can directly control only PivotTables or Excel Tables in the current version of Excel 365. On the other hand, they also do have a few problems. That means that you can’t set up validation lists on each sheet in your workbook to change the same setting for all sheets in your workbook. Third, they’re not as professional-looking.Īnd fourth, you can’t set up two validation list dropdown list boxes to control the same cell. Second, validation lists require more clicking than Slicers. First, I recently discovered a bug in Excel 365 that makes a validation list difficult to use when a Camera is on the same sheet as the validation dropdown list box. The validation list has at least four problems. Then, in this example, you’d set up a MATCH formula in your FigNum cell to return 1, 2, or 3-depending on which of the items you chose in your validation list. When you choose OK, your active cell will contain a dropdown list box with the choices in your list. Next, in the dialog, you choose Settings, Allow: List, and then you specify that three-cell list as the Source. Then you choose Data, Data Tools, Data Validation, Data Validation to launch the Data Validation dialog. You select a cell where you want the dropdown list box to reside. When you set up a validation list, you first set up the list of choices you want to offer your user.įor example, you could set up a list with Bar Chart, Line Chart, and Table in three adjacent cells. On the other hand, the validation list control is used widely. They’ve not been updated in years, and Microsoft never talks about them. To see form controls, in the Developer, Controls group, choose Insert.įorm controls might not officially be deprecated, but they’re on their way.
In years past, I might have used a form control to control this figure. Now, in this article, I’m going explain how I set up the slicer to change that setting, rather than using a validation list control or a form control.
In Interactive Dashboard Magic with Excel Slicers, I explained how to display one of three figures when you enter the values 1, 2, or 3 in a cell. The figure below is using Slicers to control a setting in a workbook, a setting that tells Excel’s Camera tool which of three images to return: a bar chart, a line chart, or a table.
0 kommentar(er)
