It has been quite some time now a lot of talk around the future of VBA. Here comes the Office Scripts in Microsoft Office web apps like Excel (in its preview) with two important features to seed the beginnings of the end of VBA era: Actions recorder: Mimicking VBA macro recorder functionality Automation scripting: Mimicking... Continue Reading →
Blog
Infographic of Table Merge or Join Operations
Most data analyses start with organizing the data, at least starting with organizing in tabular format. This implies tables are the most fundamental formats to deal when analysing data. Here I crafted an infographic showing the most commonly performed merge or join operations among tables. Hope you will find this useful. Feel free to share... Continue Reading →
Share this:
Cascading or dependent drop-down lists using Data Validation tool in Excel
If you are thinking of bringing interactivity to the reports in Excel, you may have come across the need for having a simple drop-down list or a more complex cascading drop-down list (i.e., a drop-down list whose values are dependent on the value already selected in another drop-down). Example Scenario: For example, say, you have... Continue Reading →
Share this:
Calculating Age (duration or difference between two dates) in years, months and days in Power Query M Script
16-Jul turns out to be the birthday of Microsoft Power BI. Here I post a formula in M Script to calculate the age (i.e., duration or difference between two dates) in years, months and days: let Source= [ PowerBIBDay = #date(2014, 7, 16), Today = Date.From(DateTime.LocalNow()), BirthdayFactor = if Date.DayOfYear(PowerBIBDay)<Date.DayOfYear(Today) then 0 else 1, DayFactor... Continue Reading →
Share this:
A magical Excel trick for flattening data copied from a PivotTable report in tabular layout into usable format
Here is a scenario that I come across sometime ago when I received an Excel file with some apparently messy data for further processing to generate another report. The data in the file appear similar to below screenshot. This data is self-explanatory. It’s the sales in units and dollars of each product under each product... Continue Reading →
Share this:
Flash Fill Trick in Excel 2016
Here is a very common exercise that you might have or may come across – You have full names of employees in a column and you are expected to extract last names from these full names into a separate column. In versions prior to Excel 2013, you need to use formulas and functions to accomplish... Continue Reading →
Share this:
New function in Excel 2016 to display in a cell, the formula contained in another cell
You must have come across a number of situations when you were trying to explain a formula that you have written in Excel and the result of the formula. This kind of situation basically demands both the formula expression and the formula result to be displayed simultaneously. For example, from the below screenshot, if you... Continue Reading →
Share this:
Trick for Listing the names of all sheets in Excel workbook
Here is a 2 step technique to retrieve the names of all sheets in Excel workbook: Step:1. First define a named range using the below formula: Step: 1a. Click “Define Name” command in “Define Names” group under “Formulas” ribbon tab to invoke “New Name” dialogue Step: 1b. Let the name of the range be “SheetNames”... Continue Reading →
Share this:
Generating Date Dimension data in Microsoft Excel
Generating data for populating Date Dimension table using familiar Microsoft Excel
Share this:
Oracle SQL script to generate Date Dimension data for use in Microsoft Excel Data Models
Date dimension is that one dimension or business perspective without which an Excel Data Model cannot ever exist. Here is an Oracle SQL script to generate the data for a Date Dimension. All that you have to do is change the start date and end date values in the in the last line of the... Continue Reading →
