Make a random sentence with Excel formulas
Ever wanted to make a random sentence or text? You can use Excel formulas to make totally random sentences. This is a great way to generate test data or dummy data-sets.
#awesome trick – Extract word by position using FILTERXML()
This is CRAZY!!!. I stumbled on a weird use for FILTERXML() while reading a forum post earlier today. So I couldn’t wait to test it. I am happy to share the results.
Say you have some text (sentence / phrase / keyword etc.) in a cell and you want to extract the nth word. Unfortunately Excel doesn’t have SPLIT() formula. So we end up writing obscenely long array formulas or use gazillion helper columns.
Here is the super sneaky trick. Use FILTERXML() instead.
How to trace precedents in Excel formulas? [tip+music from Prague]
Here is a very useful and almost secret Excel tip for you. Imagine you are looking at a big, complex workbook with lots of calculations. You want to understand where everything is pointing to and how the workbook is set up.
You can use trace precedents in Excel to do this. Read this tip to learn how it works.
VLOOKUP multiple matches – trick
We all know that VLOOKUP can find first match and return the results. But what if you want all the matches? Use this simple trick instead.
Top 10 Excel Formulas for any situation
Excel has hundreds of formulas. But as a new learner or user, you may want to just focus on top 10 formulas to get the most out of it. Assuming you already know the basics (check out Beginner Excel page if you are complete newbie), here is a list of top 10 Excel formulas for you.
Quickly Change Formulas Using Find / Replace
So you have built that excel report your boss wanted. And you were all eager to use the spreadsheet in your presentation. But in the last minute, your boss asked you to change average sales to total sales figures. You also want to grab an espresso before rushing to the meeting. Now what?
Elevator problem – Excel homework
The other day while I was in lift (elevator), it made an alarm like sound and won’t close the doors. Turns out there are one too many people in the lift for it to operate safely. As soon as a couple of people volunteered and stepped out, it started fighting gravity and took us upstairs. […]