Skip to content

Articles and Tutorials

Excel Tutorials

Functions

Using Wildcards in Excel SUMIFS and COUNTIFS Functions

Rounding Numbers & Removing Decimals in Excel

FORECAST.LINEAR & FORECAST.ETS Excel 2016 Functions

Lookup on Each Duplicate Value in Excel

Using Comparison Operators in Excel’s COUNTIFS & SUMIFS Functions

Excel’s AVERAGEA Function: Evaluate Text Values in Average Calculations

Excel AGGREGATE Function: Ignore Error Values, Subtotals & Hidden Rows

VLOOKUP Across Multiple Tables Using the CHOOSE Function

Home Mortgage Payment Calculator Using an Excel Spreadsheet

Using Wildcards *, ? and ~ within COUNTIFS and SUMIFS Functions

Excel VBA User Defined Function, VBA Functions & Worksheet Functions

Using ABS Function with SUM, AVERAGE and MAX (Array Formula)

Perform a Case Sensitive Lookup in Excel

Using Excel’s SWITCH Function including SWITCH with VLOOKUP

VLOOKUP Multiple Sheets Names Using the INDIRECT Function

Using Excel’s SUMPRODUCT with IF Criteria/Conditions

Excel’s SUMPRODUCT with Multiple Criteria Including AND and OR Criteria

Perform Weighted Average Calculations Using Excel’s SUMPRODUCT Function

Text Functions

Replace Text in Excel using SUBSTITUTE & REPLACE

Excel Text Functions LEFT MID LEN SEARCH CONCATENATE

Excel TRIM Function: Remove Trailing Spaces in Text

Excel VBA – Text to Columns with VBA Macro

Excel Text Functions LEFT MID LEN SEARCH CONCATENATE

Excel 2013 Flash Fill – Concatenation & Text to Columns

Combine Values & Delimiters Using Excel’s TEXTJOIN Function

Conditional Formatting

Excel VBA Macro to Apply Conditional Formatting Based on Value

Excel VBA – Apply Icon Set Conditional Formatting with VBA Macro

Excel VBA – Apply Conditional Formatting Data Bars with VBA Macro

Excel VBA – Apply Colour Scale Conditional Formatting with VBA Macro

Create a Christmas Tree with Sparkling Lights in Excel

Excel VBA – Conditionally Format Top/Bottom 10 Using VBA Macro

Excel VBA – Format Duplicates or Uniques with VBA Macro

Dealing with Errors

Remove the #DIV/0! Error in Excel

Remove the #N/A Error in a VLOOKUP

How to Avoid Excel’s #VALUE! Error

The Excel #NAME? Error

Excel’s #NULL! Error and the Intersect Operator

The Excel #REF! Error

Data Validation

Create a Dependent, Cascading Drop Down List in Excel

Excel VBA – How to Apply Data Validation Drop Down List Using VBA Macro

Excel Drop Down List with Dynamic Source Data

Charts

Create a Bubble Chart in Excel 2013

Create a Pie of Pie Chart in Excel 2013

Excel 2013 Recommended Charts, Secondary Axis, Scatter & PivotCharts

Example Radar Chart Created in Excel 2013

Create Histogram Charts in Excel 2016

Creating Pareto Charts in Excel 2016

How to Make a Gantt Chart in Excel

Excel 2016 Forecast Sheet – New Feature

Excel 2016 Sunburst Chart – New Chart Type

Working With Data

7 Irresistible Reasons to Convert Your Data into an Excel Data Table

Perform a Case Sensitive Filter in Excel

Excel 2013 Table Slicers: Filter Data Using a Visual Dashboard

Excel VBA – How to Sort within an Excel Table with VBA Macro

Excel VBA – How to Convert Range into Excel Table Using VBA Macro

Excel VBA How to Sort Data

Working With Dates

Calculating Working Days in Excel Using NETWORKDAYS & WORKDAY

DATEDIF Calculates Years Months & Day Difference Between 2 Dates

Calculate Number of Working Days to the End of the Current Month

Data Protection

Using Excel’s “Allow Users to Edit Ranges” Command

VBA Macro to Protect all Worksheets in an Excel Workbook

PivotTables

Excel 2013 Combining Multiple Tables in a PivotTable using the Data Model

Understanding the Excel PivotTable Cache

Using Named Sets in a PivotTable

Useful ways to use the GETPIVOTDATA Function

Excel 2013 Timelines: Filter Date Fields in PivotTables

Excel VBA Macro to a Create Slicer for PivotTable

Excel VBA Macro to Repeat Item Labels in a PivotTable

Excel VBA Macro to Show/Hide Grand Totals in a PivotTable

Excel VBA Macro to Show/Hide Subtotals in a PivotTable

Excel VBA – How to Create a PivotTable with Excel Macro

Excel VBA Macro to Display a Blank Row After Each Item in PivotTable

Using “Show Value As” Calculations in Excel 2010 PivotTables

Excel VBA Macro to Control PivotTable Layout

Automatically Refresh a PivotTable When its Data Source is Updated

PowerPivot

Grouping Date Fields in Excel’s Power Pivot

Recorded Macros

Delete Filtered Records in an Excel Database with a Recorded Macro

Tips and Tricks for Excel Macro Recording

VBA

Excel VBA – How to Add Rows and Columns to Excel Table with VBA Macro

VBA Macro to Protect all Worksheets in an Excel Workbook

Excel VBA Macro to Apply Conditional Formatting Based on Value

Excel VBA – Apply Icon Set Conditional Formatting with VBA Macro

Excel VBA – How to Apply Data Validation Drop Down List Using VBA Macro

Excel VBA – How to Sort within an Excel Table with VBA Macro

Excel VBA – Apply Conditional Formatting Data Bars with VBA Macro

Macro to Resize and Reposition All Comments on an Excel Worksheet

Excel VBA – How to Convert Range into Excel Table Using VBA Macro

Excel VBA Macro to a Create Slicer for PivotTable

Excel VBA Macro to Repeat Item Labels in a PivotTable

Excel VBA How to Sort Data

Excel VBA – Apply Colour Scale Conditional Formatting with VBA Macro

Excel VBA – If Then Else Structure (7 Example Macros)

Excel VBA Message Boxes – MsgBox Function

Excel VBA – Select Case Structure (7 Example Macros)

Excel VBA Formatting Cells and Values

Excel VBA – How to Group by Date Field in a PivotTable

Excel VBA Do Loops – Do While, Do Until & Exit Do

Excel VBA Macro to Show/Hide Grand Totals in a PivotTable

Excel VBA – Text to Columns with VBA Macro

Excel VBA User Defined Function, VBA Functions & Worksheet Functions

Sort Worksheets Alphabetically in Excel with a Macro

Excel VBA – For Each Next Loops (Looping through Collections)

Excel VBA How to Filter Data Using AutoFilter

Excel VBA InputBox Function & Application.InputBox Method

Excel VBA Macro to Show/Hide Subtotals in a PivotTable

Excel VBA – Conditionally Format Top/Bottom 10 Using VBA Macro

Excel VBA – Format Duplicates or Uniques with VBA Macro

Excel VBA – How to Create a PivotTable with Excel Macro

Excel VBA Macro to Display a Blank Row After Each Item in PivotTable

Excel VBA Workbook Events, Worksheet Events & OnKey Events

Excel VBA Conditionally Format Above/Below Average Using VBA Macro

Excel VBA Debugging Techniques

Excel VBA Error Trapping and Handling

Excel VBA Macro to Control PivotTable Layout

Excel VBA – Working on Rows and Columns

Excel VBA – Storing Data in Arrays & Dynamic Arrays

Excel VBA File Management Using The FileSytemObject

Word Tutorials

Multiple Table of Contents/ TOC for Each Section in Word

Mail Merge: Applying Currency Formatting to Numbers

MS Word Auto update Table of Contents on Print & Export to PDF

Collapse and Expand Heading in your Word 2013 Document

PowerPoint Tutorials

Insert, View & Print Comments in PowerPoint 2013

Removing Hyperlink Underline in PowerPoint & Format Hyperlink Font Colour

Presenter’s View in PowerPoint 2013: Show Presentation Notes & Use New Tools

Align & Distribute Shapes in PowerPoint 2013: Smart Guides & Master Guides

How to Show the Outline Pane in PowerPoint 2013

PowerPoint 2013 Start Screen: How to Use It & How to Disable It

Publisher Tutorials

Microsoft Publisher – Starting Numbering on Page 2 Onward

Outlook Tutorials

No Colour Categories in Outlook 2013? Cannot Assign Colour Categories?

Visio Tutorials

Connecting Your Visio Drawing to an Excel Database in Visio Standard

Project Tutorials

Understanding the Earned Value Table in MS Project

Create Budget Resources in Project 2010 to Compare with Project Costs

Setting up a Resource Pool to Share Resources in MS Project 2010

Project 2013 Task Path Highlighter – NEW FEATURE

Access Tutorials

Access Field Validation Rule Based on Value in Another Field