Finding an existing file & its path using Excel VBA When writing Excel VBA code that works with external files like XML or JSON, it is important that you have controls in your code to check it the file exist. If your macro (VBA program) doesn’t find a file when […]
VBA To Find Last Row or Column in a Excel Wroksheet.
Get VBA code to find the last row or column in a Excel wroksheet. Pass sheetname as parameter and get last row/column returned.
How to Add Leading Zeroes in Excel – for Numbers?
Learn how to add leading zeroes in Excel for numbers in cell. You can add zeroes to the display formats or to values with formula and few simple methods.
Get Sheet Object Caption and Codename
Get Excel worksheet caption & name (or Object name or Codename) using VBA Macro. This is a readonly property and cannot be renamed using code without giving access to VBA Project Model.
Power BI – DAX
What is DAX? DAX stands for Data Analysis eXpressions and is the language behind Power BI. DAX is a formula expression language used in Analysis Services, Power BI, and Power Pivot in Excel. With the help of DAX, analysts discover ways to analyze data and come up with great ideas and insights […]
SUM and Average of Top N Values in Excel
In today’s world, the data sets we have is huge and we can have so many criteria to be followed on the data For Example: Sum Average etc. Let us say we have N products in our superstore and we have to find the total number of sales made in […]
Excel Sheet Protection Check
Detect Sheet Protection Whenever working with a worksheet ever wondered why some menu buttons are not working or ribbon icons are greyed out and no functionality doesn’t work, this happened to me and because of a silly mistake. I can’t say how many times this has happened to me while […]
LET( ) Function in Excel
What is a LET function in Excel? The LET function in excel is used to assign the name of the calculations of the result. This allows storing of intermediate results of some calculations, values, name of some functions defined inside a formula. These name only apply within the scope of […]
Excel TEXTJOIN Function
The TEXTJOIN function combines the text from multiple ranges and/or strings, and includes a delimiter you specify between each text value that will be combined. If the delimiter is an empty text string, this function will effectively concatenate the ranges. Syntax: TEXTJOIN(delimiter, ignore_empty, text1, [text2], …) For example = TEXTJOIN(” […]