All articles in 'Excel Howtos' Category
Last week, we had a home work on Calculating Donation Summaries using Excel Formulas. This is a good case where array formulas can help us. So today, we will learn how we can use Array Formulas to compare lists of values and calculate summaries. Towards the end of this post, you can see a video that explains the solution to Donation Summary Calculation problem.Continue »
How often have you had a simple table where you want to lookup a value or add up some values meeting a criteria?
This post looks at a simple way to do a 2D Lookup or 2D Sum of a values from within a 2D range with multiple criteria, without complex Sum, Offset, Index, Match, Sumifs or other functions, simply using SumproductContinue »
- Cue, alluring music.
Just like Jack and the crew of Oceanic Airlines flight 815, Excel has a number of LOST functions.
This post will look at some of Excel’s Undocumented, Unloved and Rarely used Functions
Ok, You’ve just finished the mother of all Spreadsheets. Not only does it solve your companies budget, it tracks production, reports variances, makes a mean cup of coffee and some say, “it May Even Solve World Peace ?”.
But the boss walks in and asks “I have a list of items which I need to distribute evenly into colored buckets”
I had never thought of that option, Maybe I can constrain my model and let it work it out for me ?
Solver I need your help !
Don’t know which way to go ?
Can’t make up your mind between alternatives?
Using collected or known data is the best when developing Excel models, but from time to time this may not be available when you are developing your model. This post will look at some options for setting up Dummy Data using Excels Random number functions.
How to make a 5 Star Chart (Similar to Amazon)
Last week Chandoo presented Give more details by showing average and distribution
At the top of the post was a small screen capture from Amazon.com showing a 5 Star chart showing that Twilight had a 3.5 Star Rating (way over-rated if you ask me).
I received an email shortly afterwards from Rajiv, “How can I make one of those charts ? ” with the Stars Circled
It’s actually very simple and this post will show you how.Continue »
For most of us, the prospect of inheriting a large, undisclosed sum of money is bleak. But we have high probability of inheriting a complex Excel workbook with 19 worksheets and 2300 rows of data and 195 formulas. The kind where entire rainbow colors are used to color code accounts receivable statuses. Then what do we do? We spend a whole afternoon (and then the rest of the month) breaking our head trying to figure out why the total revenues are only $ 41.2 million when profits are $ 99.23 million.
So how do we deal with our inheritance?
Here is a quick tip to help you get started. Disable “Direct editing mode“.Continue »
Ok, Excel Hyperlinks may not be able to rescue you from an imploding planet, but they can add to the useability of your Excel Projects.
This post looks at Hyperlinks. What they are and how to use them in your workbooksContinue »
Excel can be a bland collection of cells with text and numbers or it can be a playground where you can have interactive engagement with your models.
The choice is yours!
Excel provides a small number of tools to allow you to interact with your models.
These tools are called Form Controls and can be added into and linked to your models data.
This post will look at the various types of Form Controls and discuss their application and use.Continue »
Here is a fun formula to write.
Given a number in cell, I want you to find the sum of digits in it. So, for eg. if you have the number 3584398594 in a cell, the sum would be =3+5+8+4+3+9+8+5+9+4, equal to 58.
Now, how would you write a formula to find this sum automatically based on the number entered in the cell?
Go ahead and figure it out. If you can, come back and check your answer with mine below.Continue »
Transferring data regularly between Excel and Word for the production of Word based forms or reports cab be tedious if it involves much more than a few numbers.
This post looks at a technique for automating this process.
Lets say you have some data in 2 columns and you want to compare row by row to spot the differences. Of course you can write a formula or apply conditional formatting. But there is a quick and dirty solution that works just as fine.Continue »
In the final post of the Are You Trendy? series we will look at the use of Excel Charts and associated Trendlines for trend analysis and I will give you a free tool (Normally valued at $200, I wish) which will allow interactive assessment of a Charts Trendline Y value for any X value.Continue »
Does your data hold hidden secrets?
In Part 2 of Are You Trendy? we will examine the Excel functions that assist us in Trend Analysis.
The Properties button on the Developer Tab in Excel 2007/10 can be used to access a number of Worksheet properties normally only available through VBA and even a few which aren’t available through VBA.
Lets take them for a spin.