All articles with 'Microsoft Excel Formulas' Tag

Sorting text is such a day to day activity that it always surprises me why Excel hasn’t provided a simple spreadsheet formula for doing it. Of course you can use the sorting menu command (menu > data > sort) but this requires manual steps (or VBA). Most of the times we get raw textual data […]

Often when you are working on 2 incompatible systems and try to import data from one to another, excel can be a handy tool. I have used excel plenty of times to generate SQL insert / update statements which I could later execute on the database console. Here is a tutorial if you ever have […]

In India salary is usually paid on the last working day of a month – the payday. It is slightly different in countries where payrolls are processed every 2 weeks. For eg. in US most companies pay salary on every 2nd Friday / Thursday. We can calculate the paydays / payroll periods in excel with […]

We have a major power outage in Ohio due to a wind storm on Sunday. AEP, Our electric provider says that power would be restored to my area by this Friday midnight, that is almost 6 days without power. I will not be able to blog regularly during this period. So here is a list […]

Often when you are processing text using excel it is important to split the text in to multiple parts based on a delimiter. For eg. you may want to divide this|needs|to|be|split in to five parts this needs to be split. There is a simple way to do this in excel, using “import text” option. But […]

Often when you are processing customer records or doing mail merge, it might be useful to get initials from a given name, like JFK for John F Kennedy. You can do this using simple text formulas (left(), mid(), find()) combined with if(). Here is how: Assuming cell B3 has the full name, then this is […]

Creating KPI Dashboards in Microsoft Excel is a series of 6 posts by Robert from Munich, Germany. This 6 Part Tutorial on KPI Dashboards Teaches YOU: Creating a Scrollable List View in Dashboard Add Ability to Sort on Any KPI to the Dashboard Highlight KPIs Based on Percentile Add Microcharts to KPI Dashboards Compare 2 […]

Google docs spreadsheet app is going mainstream with each passing day. Recently they have introduced split() formula to divide text in to multiple cells using a user specified delimiter. The syntax for this function is =split(text value, delimeter), for eg. =split(“pointy haired dilbert”, ” “) would result in 3 individual cells with “pointy”, “haired” and […]

Creating KPI Dashboards in Microsoft Excel is a series of 6 posts by Robert. This 6 Part Tutorial on KPI Dashboards Teaches YOU: Creating a Scrollable List View in Dashboard Add Ability to Sort on Any KPI to the Dashboard Highlight KPIs Based on Percentile Add Microcharts to KPI Dashboards Compare 2 KPIs in the […]

If you ever had to simulate random outcomes in excel sheet, you might have already heard of about the spreadsheet function rand(), this little function generates a random fraction between 0 and 1 whenever you use it. So I usually write =round(rand()*12,0)int(rand()*12)+1 if I need a random number between 0 to 12. Of course, if […]

Excel formulas can always be very handy, especially when you are stuck with data and need to get something done fast. But how well do you know the spreadsheet formulas? Discover these 15 extremely powerful excel formulas and save a ton of time next time you open that spreadsheet. 1. Change the case of cell […]

Who said Excel takes lot of time / steps do something? Here is a list of 15 incredibly fun things you can do to your spreadsheets and each takes no more than 5 seconds to do. Happy Friday 🙂 1. Change the shape / color of cell comments Just select the cell comment, go to […]

I was doing some weird analysis on corporate mission statements and I had to count the number of words in each cell. That is when I realized there is no formula to calculate the number of words in a cell, I was too lazy to write an UDF for that, so I figured out a […]

Excel has various functions, including functions to calculate inverse cosine of a given value, to multiply 2 matrices, to estimate the internal rate of return. But, most of us(well, just me then..) use just about 5-6 formulas to do our jobs. And IF formulas are a majority of these, so it doesn’t harm to learn […]

Excel concatenate() is seriously crippled, it can add 2 or more strings together, as long as they are supplied as separate parameters. This means, when you have a range of cells with text which you want to add up to create a large text, you need to write an ugly looking biggish concatenate() or use […]