Exclude Hidden Rows from Totals [How to?]
Denice, an Excel School student emailed me an interesting problem. I have a bunch of data from which I want to find the sum of values that meet a criteria. But I also want to exclude any rows that are hidden. Well, we know how to find sum of values that meet a criteria – […]
How to Get “Either-Or” Condition in Data Validation?
In a recent consulting assignment I had a tricky data validation problem. The customer wanted to have an either-or condition in the data validation, like this: My initial reaction to this requirement was “hmm… that is not possible“. But before shooting the email back to client, I got curious and checked if excel data validation […]
Automatic Rolling Months in Excel [Formulas]
Often when we are making spreadsheets for forecasting or planning we would like to keep the starting month dynamic so that rest of the months in the plan can automatically rolled. Don’t understand? See this example: This type of setup is quite useful as it lets us change the starting month very easily. We can […]
Writing “Either Or” formula in Excel [Formula Howtos]
We all know the AND, OR & NOT formulas in Excel using which you can perform simple logical operations And, Or & Negate. But what if you are the chief of HR at ACME Company, where they have a strange rule on extra allowance like this: Now, to calculate the dates in a month that […]
How do you consolidate data from multiple sheets in to one? [open thread]
Long time PHD reader and mother of a lovely kid, Michelle, sent me a question in email that provoked me to write this post, I was wondering how to tabulate large amount of information gathered through surveys. Where I work customers are constantly handed survey sheets in order for us to measure how the service […]
Fix Incorrect Percentages with this Paste-Special Trick
Sometimes we get values in our Excel sheets in such a way that the % sign is omitted. So instead of the value being 23%, it is 23. Now, you can very easily correct this by editing the cell and adding a % sign at the end. But what if you have 100s of rows of data. You can’t do this to every cell. (You can not just format the cells to % format either, excel shows 23 as 2300% then). There must be some simple and intuitive solution for this … umm.
Pivot Table Tricks to Make You a Star
We, data junkies, love pivot tables. We think pivot tables are solution for everything (except for may be global warming and that broken espresso machine down stairs).
Today, we are going to learn 5 awesome pivot table tricks that will make you a star.