I even did something similar to your video but I didnt know how to handle multiple selections. Very cool.We have created a PivotTable and related PivotChart, and, since we are nice, we have also provided a Slicer so that the user can easily make selections.As with anything in Excel, there are multiple ways to accomplish this.
Excel Using Slicers In Formulas How To Handle MultipleIn order for the user to be able to pick a region or regions to include in both reports, we have created a slicer, as shown below. However, the slicer may be located on a different worksheet or control several reports throughout the workbook. Wed like each report to clearly let the reader know which regions are included in the report. So, well just set up a dynamic report title, something along these lines. The helper PivotTable has one job: display the items that the user selected in the slicer. To do this, we first create a PT based on the same data source and insert the field used for the slicer into the Columns layout area. At this point, all items in the field are displayed in the report, as shown below. So far so good. Our next step is to create a helper column to combine the values. Probably the easiest way to do this is with the TEXTJOIN function. Instead, you could use the SUBSTITUTE, TRIM, and CONCATENATE functions. For example, if the helper PT labels appear in B23, C23, D23, and E23, you could use something like this formula (which is included in the sample file below for reference): SUBSTITUTE(TRIM(CONCATENATE(B23,,C23,,D23,,E23)),,, ). Excel Using Slicers In Formulas Manual Input CellThe title is a manual input cell (C17), and the subtitle is created by the helper formula written above (C18). For example, for the PivotTable report, we could just write two formulas in cells above the PivotTable to retrieve the values from C17 and C18. To do so, select the chart title and begin the formula by typing into the formula bar, as shown below. Insert a Textbox into the chart, just under the chart title, by clicking Insert Textbox and clicking into the chart area. Enter into the formula bar, navigate to and select the subtitle helper formula cell, and hit the Enter key on your keyboard. You can format the color and size of the title and subtitle as desired. You can unsubscribe anytime, and I will never sell your email address. Gamification ensures it is the most fun you can have learning Excel:). I even did something similar to your video but I didnt know how to handle multiple selections.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |