Objects tagged with excel


Posts

← Back to the Posts

Excel IFERROR in Power Query M or Power BI

Published on September 02, 2021 at 11:39PM

if `try otherwise` not remembered then read this or else just `IFERROR`

Left Pad a Number with a Zero if the Length is Odd in Excel

Published on July 27, 2021 at 03:02AM

Whenever you open a CSV file in Excel and it has a text field with numbers, and that text starts with a zero, then Excel will drop the zero.

Return Database Schema for Table Using Power Query

Published on January 29, 2021 at 01:12AM

Point this query to your database table and return all columns and schema meta data using Power Query in Excel

Formula for Username in Excel

Published on April 27, 2020 at 12:44AM

You want a formula to return the the username of the current user working on the excel file.

An Elegant Way to Clear the Excel VBA Immediate Window When Debugging

Published on October 17, 2018 at 04:45AM

Your Immediate Window is so jammed with debug.print that you just want to clean slate it... quickly... and elegantly... like a gazelle.

5 Level Cascading Validation Lists in Excel using VBA

Published on October 11, 2018 at 12:18PM

You need validation lists to change based on selections... like a cascading validation list

Edit Data Validation Lists in Excel using Arrow Keys

Published on October 11, 2018 at 06:16AM

You are trying to edit a Data Validation List and when you click the arrow keys things turn crazy and annoy you sooooo bad!

[SOLVED] Fix Duplicate Conditional Formatting Rules in Excel 2016

Published on September 18, 2018 at 04:48AM

You have conditional formatting rules in Excel 2016 and all of a sudden they are duplicated all over the place and super messy. This fixes it.

Building Your Own (And Better) Autosave Feature for Excel

Published on June 07, 2018 at 11:13PM

Because the current Excel Autosave is just so bad XD

Automate Excel Table Filters Using VBA and Activate them Using Hyperlinks

Published on February 07, 2018 at 06:07AM

Now you can click a metric in one of your Worksheet reports and be taken straight to the underlying data in a way that is easy to see.

[SOLVED] Excel VBA Get Financial Year from Date - No Formula

Published on January 23, 2018 at 01:42AM

Convert a date to the Financial Year (Fiscal Year) like '01/01/2017' = '2018'. Australian example used. Pure VBA without Excel formulas. Just the solution.

IFNIL formula in Excel

Published on August 30, 2017 at 07:00AM

Often you don't want to wrap an IF statement when you just want to turn a 0 result into another value such as blank

Macro to Backup Excel VBA Code within an Excel Workbook

Published on August 24, 2017 at 06:20AM

Backup all the VBA codes to Git

Quickly Switch Between Worksheets in Excel using Shortcut (Alt+Backtick)

Published on June 19, 2017 at 01:31AM

Copy this to your PERSONAL.XLSB to quickly switch between Worksheets on the ActiveWorkbook

Excel Actually Sucks

Published on May 18, 2017 at 03:23AM

Just a place where I can write all the things I hate about Excel. Remember to just breathe

VBA for Last Saved At Timestamp with Hours Minutes and Seconds Ago

Published on May 04, 2017 at 06:50AM

Useful to have as a button or invoke overtime to alert when you have forgotten to save

Use a Table Column as a List in a Validation List

Published on April 18, 2017 at 07:07AM

This is easy to do but not as easy as you would first think.

Boilerplate Excel VBA Functions module

Published on February 15, 2017 at 05:59AM

A basic module I load to each project for shared functionality

Single and Multi Line Comments in Power Query Advanced Editor

Published on February 13, 2017 at 11:01AM

Just a simple reminder on how to do single and multiple line comments

Countif in Power Query or Powerbi using M Language

Published on February 13, 2017 at 10:57AM

You know how to countif in Excel so why is Power Query so difficult?

Get Row Number in Worksheet in Excel Using VBA

Published on February 13, 2017 at 10:54AM

A simple function to return the row number of a table where a value is matched in a column. Like a VLOOKUP or INDEX MATCH for VBA.

Screencasts

← Back to the Screenasts

Introduction to Structured References with Tables

Published on May 18, 2017 at 05:46AM

Excel formulas are better with Structured References for Tables.

VLOOKUP vs INDEX MATCH

Published on May 18, 2017 at 05:45AM

What's better than a VLOOKUP? INDEX MATCH!!

Introduction to Visual Basic for Applications VBA

Published on May 18, 2017 at 05:32AM

A short introduction to VBA by building something simple

Introduction to the Visual Basic Editor VBE

Published on May 13, 2017 at 01:55PM

Go under the Excel hood by getting to know the Visual Basic Editor. The power house of the Excel workbook.