Category: Excel and Power BI
-
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:…
-
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: As on today, the output of the above formula is: I am now on 5…
-
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…
-
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…
-
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…
-
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…
-
Generating Date Dimension data in Microsoft Excel
Generating data for populating Date Dimension table using familiar Microsoft Excel
-
Truncating time part from date & time data in Excel
Did you ever come across a situation where you had to compare two dates ignoring their time part to arrive at a status indicator ? Let’s see an example and the trick to address it: Example Scenario: Here you have some order ids and their respective order dates, target dates…
-
Find and Replace carriage return in MS Excel
Scenario: Here is a scenario that I have come across recently in Excel. I have the data in column that is split across multiple lines in each cell, more technically speaking, separated by carriage returns. i.e., the text in the cell is separated by pressing ALT + ENTER. see the…