democratizing data and analytics
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 →
Generating data for populating Date Dimension table using familiar Microsoft Excel
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 →
Hi readers..!! Here I go posting a white paper on one of the classical and intellectually challenging problems that an analyst might come across. The paper is titled - "Understanding and Solving Relational Division problems in SQL - The Analyst way". Hope you enjoy reading this paper. Please leave your comments and suggestions, if any, here.... Continue Reading →
Use case of SQL CROSS JOIN: Although CROSS JOIN is available in SQL, you rarely find any instance where you can use it. Here is one use case for CROSS JOIN in SQL: Example with fictitious data: Assuming a tournament of Soccer to happen among four teams, what are the all possible matches between each... Continue Reading →
Here I share a tricky way to create in-cell charts in Excel with an example. Below is the data of a few employees and the respective volume of requests completed by them. Now enter the below formula in cell C2 and copy it till C8. =REPT("n", B2/50) Column C... Continue Reading →
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 and delivery dates as in... Continue Reading →
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 below screenshot: Challenge: Now the... Continue Reading →
Although VLOOKUP in Excel is a very useful function, it suffers a drawback. If the lookup value to be searched is not in the first column of lookup table, VLOOKUP wouldn’t work. In the last part of this learning series, titled "VLOOKUP: Right to Left Lookup" I have taken you through a trick to address this. Here... Continue Reading →
We all now know that VLOOKUP has one major constraint that the column in the lookup table from where the lookup value is to be searched should be the first column in the lookup table. Here is a tricky way that you can use to overcome this constraint. The trick is, if we can build... Continue Reading →
