Data Validation DV0004 - Short or Full Drop Down List- With this data validation technique from AlexJ, users can see a drop down list with all the items, or a short list of popular items. No macros are required - just formulas. For instructions, see the Contextures Blog article. 07-Feb-11 DV0003 - Dynamic Data Validation - With this data validation technique from AlexJ, users can see a drop down list with just the top projects, or all projects. A macro cleans up the selection cell, if necessary. 16 kb 16-Feb-11 DV0002 - Different Drop Downs from One Source - Instead of using a different source for each data validation list, AlexJ has devised a simple way to use the same source for all the lists. For details see Contextures Blog article.
25kb 22-Feb-09 DV0001 - Show or Hide User Tips - Excel template from AlexJ uses data validation to show messages for users on a worksheet. Users can choose from a drop down list to show or hide the messages. For details see Contextures Blog article. 9kb 11-Feb-09 Pivot Tables PT0005 - Pivot Table Report Diagnostics - Show a summary of all pivot tables in the workbook, and check their filter settings, before printing the reports. This technique from AlexJ uses Slicers connected to multiple pivot tables, and formulas to compare the filtersettings to their setpoints. 33 kb 15-Oct-14 PT0004 - Change Pivot Table Date Range With Scroll Bar - Click the scroll bar to change the ending date for a pivot table report. Set the number of months to be included.
Data Slicers. Place a checkmark beside each pivot table element you want to have displayed as a Slicer, by left clicking in the box to the left of the element and click the OK button when done – Excel will insert a Slicer for each of the pivot table elements you placed a checkmark next to – The Slicers can be moved by dragging them around on. Customize Analysis Group Sort Calculated Fields & Items Filter Slicers Pivot Charts Conditional Formatting Other CHARTS Smart Art Column Line Pie Bar Area Scatter Bubble Sparklines Formatting Charts Excel 2016 Charts Infographic.
This technique from AlexJ uses one line of programming, to refresh the pivot table. 41 kb 27-Feb-13 PT0003 - Change All Pivot Charts With One Filter - Change the filter at the top of the sheet, and all the connected pivot charts change. This technique from AlexJ uses Excel 2010 slicers, and no programming. 93 kb 12-Dec-12 PT0002 - Count Missing Pivot Table Data as Zero - With this tip from AlexJ, you add missing data to a pivot table. For details see the Contextures Blog article. 21-Mar-12 PT0001 - Pivot Table Slicer Detail - With this pivot table tip from AlexJ, you can see the difference when drilling to details in a pivot tables that has been filtered with an Excel Slicer. If the filtered field is not in the pivot table layout, results might not be what you expect.
19 kb 21-Mar-12 PT0000 - Pivot Table Filter Markers - In Excel 2003, there are no markers on a pivot table, to show which fields have been filtered. In this sample file from AlexJ, a symbol appears above those fields, to help you identify them. For details see the Contextures Blog article. 23 kb 19-Nov-10 Filters & Slicers FL0004 - Excel On Demand Slicers - Click a pivot table report filter field, to see the matching Slicer. Select criteria, then click the Get Data button to run an advanced filter macro that pulls the matching data.
Zipped file in xlsm format, contains macros. Slicers work in Excel 2007 and later versions. 30-Jul-17 FL0003 - Excel Pop Up Selector Tool - Save space with this technique from AlexJ. Click a worksheet button to show hidden Slicer.
Make selections and click button to see message box or special image. Zipped file in xlsm format, contains macros. Slicers work in Excel 2007 and later versions. 10-Jul-17 FL0002 - Filter Excel 2010 Table With Slicers - With this technique from AlexJ, you can use Slicers with a table in Excel 2010. 28 kb 12-May-13 FL0001 - Count Unique Items in Filtered List - With this formula from AlexJ, you can count the unique visible items, after a list has been filtered on the worksheet. 12 kb 04-Oct-10 For details see the Contextures Blog article.
VBA VB0003 - Show Table or Pivot Name on Sheet - With this User Defined Function (UDF) from AlexJ, you can show an Excel table's name and source, or a pivot table's name and source. 71kb 18-Jul-16 For details see the Contextures Blog article - VB0002 - Customize Context Menus - Excel file from AlexJ adds new commands to Context Menus, to filter by selection, and clear all filters. Copy code to your personal macro workbook, and items are added when the add-in opens.
34kb 28-Mar-15 VB0001 - Hide Rows With Outlining - Excel template from AlexJ uses outlining and VBA to hide rows on a worksheet. Users can click buttons to show or hide specific sections. Admin toolbar assists with worksheet setup. For details see Contextures Blog article.
38kb 02-Feb-09 Functions FN0001 - Set Minimum Row Height - Use the REPT and CHAR functions to set a minimum row hieght in tables or lists. 33kb 10-Jul-14 Excel Tips ET0001 - Compact Buttons With Captions - Use compact buttons, with captions at the side, to create clickable hyperlinks or to run macros. Saves space on the worksheet, and makes text easy to read. 38kb 04-Feb-15 About AlexJ AlexJ is an electrical engineer and MBA based in Toronto, and performs consulting assignments for clients in Canada.
He has over 30 years business experience, with a focus on industrial automation and control, project and engineering management, and 20 years' experience solving problems using Excel and VBA. Excel solutions have included project portfolio management, project reporting, ERP dashboards, engineering work process quality management, health and safety report dashboards, procurement tracking, and many more. Other experience includes management of application solution and ERP rollouts to businesses, project information management, work process mapping, training, and technical/business mentorship. Personal interests include cooking and music. Sample solutions files are provided 'as is' for the purpose of illustrating Excel techniques.
It is expected that readers will use these examples to develop their own solutions. There is no support provided for these solutions, and no warranty of usability is provided or implied. More Excel Files.
A dashboard is usually a collection of charts and tables combined with interactivity for the end user. Software allows the user to easily create interactive dashboards using pivot tables and slicers. This example is based on a but can be applied to any of the maps.
Prepare the data in a pivot table The starting point is a table with sales data. It usually contains some order dates, sales amount, categories, customer names etc. Based on this data let’s insert a pivot table into the Map sheet. It is crucial that this pivot table exists in the Map sheet because only then the auto refresh of the map will work. Once we have the table, we define Regions as Rows and Sales as Values.
It is necessary that the region names are consistent with the names that you can find the Data sheet. If not you need to correct that before creating a pivot table or create a new column in the source table and refresh the pivot table. Connect the pivot table with Excel Map data input Then go to Data sheet and use IFERROR and VLOOKUP functions to connect Data sheet to the pivot table – for the UK map this would be like this: Insert slicers to create interactive dashboard with a map Next choose Insert tab Filters Slicer and choose the fields to become filters for your dashboard. If your data contains date columns in the proper format and you are running Excel 2013+, you could also insert a timeline slicer, which is great to filter years and months. You can add more tables and charts by copying the existing pivot table – this way your slicers will be automatically connected to all new pivot objects (tables and charts). For example, you could create a pivot table showing Top 10 of your customers and turn it into a bar chart. Design an interactive dashboard using pivot tables, charts & map Now, using a slicer you select the data in both pivot tables and on the map.
You could add more pivot tables and maps to give more perspectives on a dashboard. Watch a video tutorial.