Category: Advanced Excel
Present Value of Annuity Formulas in Excel
For anyone working in finance or banking, the time value of money is one topic that you should be fluent in. Knowing exactly what it means to discount something or to get the future value of a particular investment vehicle is necessary to do the job. Excel can be an…
Read More
Calculating Time with Excel Formulas
How often have you looked at your watch and typed the current time into your worksheet? Or tried to convert from one time zone to another and produced an error? Or wondered why a time shows up looking like “0.9285”? Fortunately, these questions are easy to answer once you understand…
Read More
How to Use the Excel Match Function to Find Data
In Excel, some functions exist to be used to increase functionality when paired with other functions. MATCH is a good example. MATCH helps you search a range of references to find a “match” to your query. It then returns the position of that match. In short, you can use MATCH…
Read More
Calculate Range in Excel: 3 Easy Formulas
An essential skill any Excel user should know is how to determine the range of values in Excel. Most businesses don't have time to waste sorting through rows and rows in Excel in order to find the highest and lowest values of revenue, sales, or other information. The difference between…
Read More
Compound Annual Growth Rate: Comparing Investments with the Excel CAGR Formula
Does your company offer several types of IRA accounts? Are you unsure of your current IRA strategy? How do you know if the choices you make for your future are the best available to you? Say you review your IRA returns and consider changing your mix of investments. Five years…
Read More
Visually Display Composite Data — How to Create an Excel Waterfall Chart
Have you ever needed to present a chart that displays component data that roll up to a total? If all of the values are positive, Excel’s stacked column chart works beautifully; but it breaks down when your data includes both positive and negative values. The answer is an Excel waterfall…
Read More
Where is the Chart Wizard in Excel?
Microsoft eliminated the Excel Chart Wizard in Excel 2007, and it has not returned in the successive versions. In fact, the company rebuilt the entire chart system, and the team decided it would not be worthwhile to update the wizard and all of its options. On the bright side, the…
Read More
Organize Projects and Count the Time between Dates with the Help of Excel Date Formulas
Have you ever entered a project due date into a spreadsheet by manually counting the number of days and typing in the number? If so, then you’ve also experienced the tedium of recalculating those dates by hand and retyping them. Fortunately, Excel has all the functions to do the date…
Read More
Excel Dynamic Charts: A Great Time Saving Tool
If you use Excel for reporting on results each month, you have probably needed to adjust your charts automatically to add a new month, state, or sales rep. Typically, that means clicking on each chart and adding a new data series or expanding the range of the current series. Unfortunately,…
Read More
Automatically Stop Duplicate Values on Input
Preventing duplicate values from being entered in a range of cells may be a requirement in some of your worksheets. Although not obvious, enabling this capability in Excel is very easy. You can prevent duplicate values from being entered in a range of cells through Data Validation. 1) Select the…
Read More