Excel Tutorials and Templates by AlexJ

Contextures - Microsoft Excel Tips and Tutorials
Search Contextures Sites

Home
Excel Tips
Excel Files
Products
Blog
Contact
RSS

AlexJ's Excel Tutorials and Templates

Data Validation

Pivot Tables

Filters

VBA

About AlexJ

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 Short or Full Drop Down List. 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. AlexJ_DynamicDataVal.zip 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 Different Excel Drop Downs from One Source. APJ_Universal_DD.zip 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 Show or Hide User Tips in Excel. APJ_ToolTips.zip 9kb 11-Feb-09

Pivot Tables

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. This technique from AlexJ uses one line of programming, to refresh the pivot table. alexj_MonthScrollBar.zip 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. AlexJ_MasterReportFilter_v01.zip 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 Count Missing Pivot Table Data as Zero. 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. AlexJ_DoubleClick_Slicer.zip 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 Add Filter Markers in Excel Pivot Table. AlexJ_PivotFilter.zip 23 kb 19-Nov-10

Filters

FL0002 – Filter Excel 2010 Table With Slicers -- With this technique from AlexJ, you can use Slicers with a table in Excel 2010. alexj_TablesSlicers.zip 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. For details see the Contextures Blog article Count Unique Items in Filtered List. UniqueInView.zip 12 kb 04-Oct-10

VBA

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 Hide Excel Rows With Outlining. APJ_PaneControl_Sample_v1.zip 38kb 02-Feb-09

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.

Last updated: May 22, 2013 7:47 PM

Privacy Policy

	AlexJ's Excel Tutorials and Templates Data Validation Pivot Tables Filters VBA About AlexJ 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 Short or Full Drop Down List. 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. AlexJ_DynamicDataVal.zip 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 Different Excel Drop Downs from One Source. APJ_Universal_DD.zip 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 Show or Hide User Tips in Excel. APJ_ToolTips.zip 9kb 11-Feb-09 Pivot Tables 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. This technique from AlexJ uses one line of programming, to refresh the pivot table. alexj_MonthScrollBar.zip 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. AlexJ_MasterReportFilter_v01.zip 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 Count Missing Pivot Table Data as Zero. 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. AlexJ_DoubleClick_Slicer.zip 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 Add Filter Markers in Excel Pivot Table. AlexJ_PivotFilter.zip 23 kb 19-Nov-10 Filters FL0002 – Filter Excel 2010 Table With Slicers -- With this technique from AlexJ, you can use Slicers with a table in Excel 2010. alexj_TablesSlicers.zip 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. For details see the Contextures Blog article Count Unique Items in Filtered List. UniqueInView.zip 12 kb 04-Oct-10 VBA 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 Hide Excel Rows With Outlining. APJ_PaneControl_Sample_v1.zip 38kb 02-Feb-09 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.