Home > Templates > AlexJ

AlexJ's Excel Tutorials and Templates

Excel tutorials and sample files from Excel /Power Query consultant, and electrical engineer, AlexJ

fix any mismatched selections

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. Sample file: exceldropdownlongshort.zip 07-Feb-11

This short video shows the steps, and there are written steps on the Dependent Drop Downs page.

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. Sample file: 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. Sample file: APJ_ToolTips.zip 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 filter settings to their setpoints. Instructions: Excel Report Diagnostic Display. File: alexj_pivotdiagnostic.zip 33 kb 15-Oct-14

fix any mismatched selections

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. Sample file: AlexJ_PivotFilter.zip 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. excelondemandslicers_alexj.zip 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. alexj_popupselector.zip 10-Jul-17

To see how this technique works, watch this short video.

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. UniqueInView.zip 12 kb 04-Oct-10
For details see the Contextures Blog article Count Unique Items in Filtered List.

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. alexj_objname.zip. 71kb 18-Jul-16
For details see the Contextures Blog article - How to Show Excel Table Name on the Sheet

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 alexj_customcellmenu.zip. 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 Hide Excel Rows With Outlining. Sample file: APJ_PaneControl_Sample_v1.zip 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. Set Minimum Row Height. 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. apj_buttoncaption.zip Instructions: Save Space With Compact Buttons 38kb 04-Feb-15

This video shows the steps for setting up compact buttons with captions at the side.

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