pivotronics

Blog

  • A quick trick to create in-cell charts in Excel

    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 […] Read more

  • 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 and delivery dates as in […] Read more

  • 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 below screenshot: Challenge: Now the […] Read more

  • INDEX and MATCH – an efficient alternative to VLOOKUP

    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 […] Read more

  • VLOOKUP: Right to Left Lookup

    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 […] Read more

  • VLOOKUP: Approximate match example

    In the first part of this VLOOUP series, I have taken you through a simple example of VLOOKUP where while searching for the lookup value, we were doing an exact match. Now lets see an example scenario of approximate match. Scenario: You have a table showing the percentage of discount to be given to the […] Read more

  • Mastering LOOKUPs in Excel learning Series

    Here I thought of compiling a learning series covering the basics to classics of Excel Lookup functionality. So I have divided them into parts organized in some logically organized fashion. However, you are free to access and learn them in any order. Part 1: Introduction to VLOOKUP Part 2: VLOOKUP: Approximate match example Part 3: […] Read more

  • Introduction to VLOOKUP in MS Excel

    Hi friends… one of the important features of Excel which every user of Excel is expected to be aware of, is VLOOKUP function. Here I take you through the nuts and bolts of VLOOKUP: What does VLOOKUP do ? VLOOKUP function looks up (searches)  a value in one column and then returns the corresponding value […] Read more

  • Complement Negation: SQL Design Pattern to solve a frequently occurring interesting business data analysis problem

    Here is an interesting business data analysis problem that I have come across multiple times in the recent times. Since I have seen this problem surfacing multiple times and the solution boils down to the same technique, it becomes an SQL Design Pattern and I named this baby as “Complement Negation”. Have shared the justification […] Read more

  • Truncate time part from Datetime column or literal in Oracle SQL

    I have shown how to truncate the time part from a datetime column value in SQL Server T-SQL in one of my previous posts here – http://pivotronics.com/2012/12/19/comparing-datetime-column-with-a-date-literal-in-t-sql/ Here is the same (how to truncate the time part from a datetime column value) in Oracle SQL: TRUNC(datetime_column) or TRUNC(datetime_expression) very straight forward…!! isn’t it.. ? 🙂 Read more

  • Microsoft Office Excel 2003 menu to 2010 ribbon mapping Workbook and Interactive Reference Guide

    Friends.. Here is the link where Excel workbook containing the mapping between menu of Excel 2003 and Ribbon of Excel 2010 can be downloaded: http://office.microsoft.com/en-in/templates/results.aspx?qu=Excel%202010%20Menu%20to%20ribbon%20reference%20workbook&queryid=6b2d60e7%2D5451%2D4a60%2Daee7%2Dc4f4983aa767#ai:TC101842354 Here is the link where an interactive guide showing the the mapping between menu of Excel 2003 and Ribbon of Excel 2010 can be downloaded: http://www.microsoft.com/en-us/download/details.aspx?id=16642 Hope you will find this […] Read more

  • Microsoft Office Excel 2003 menu to 2007 ribbon mapping Workbook and Interactive Reference Guide

    Friends.. Here is the link where Excel workbook containing the mapping between menu of Excel 2003 and Ribbon of Excel 2007 can be downloaded: http://office.microsoft.com/en-in/templates/excel-2007-menu-to-ribbon-reference-workbook-TC010212862.aspx?CTT=5&origin=HA010149151 Here is the link where an interactive guide showing the the mapping between menu of Excel 2003 and Ribbon of Excel 2007 can be downloaded: http://www.microsoft.com/en-us/download/details.aspx?id=14650 Hope you will find this […] Read more