If you're making a dashboard-style report in Excel, you may prefer that a chart not be cluttered with all of the available data, but instead allow the report consumer select which set of data to analyze.
This post shows you how to create simple interactive charts in Excel, using the built-in list and option controls.
Full article: Simple interactive charts in Excel
The File System Object (FSO) provides access to a computer's file system. The particular object contains 3 object collections, 4 other objects, as well as several properties and methods.
This post focuses on accessing three useful properties of the File System Object using VBA:
- Drive Exists.
- Folder Exists.
- File Exists.
Full article: Determine the drive, folder & file existence (VBA functions)
Sometimes you need to label the result of a function, but have no where to add it. Here's an option: Include the label in a custom number format. This article shows you how.
Full article: Where do I put the label?
Sometimes, understanding how a figure is calculated is difficult because the formula seems quite complicated; comprising of several intermediate calculations and logical tests. Luckily Microsoft Excel has a powerful auditing tool, Evaluate Formula, which will help you to unravel the different parts of a formula, in the order that it is calculated, to understand how the end result is constructed.
Full article: How to evaluate a nested formula one step at a time
Excel is a flexible and deceptively easy tool to use, which is also one of its dangers. The following is a checklist to assist you avoiding some of the most common problems:
- Scope the job.
- Is excel the right tool for the job?
- Use the formula builder.
- Design it in stages and check as you go.
- If there is an error.
- Use formulas to help you.
- Use formula tools.
- Use conditional formatting.
- Make the input stage simple to use.
- Review.
- Lock it down.
Full article: Top 11 tips to avoid spreadsheet errors
If you import data to Excel from another program chances are the dates will come in formatted as text, which means they're not much use to you in formulas or PivotTables.
There are many ways to fix the dates and the method you choose will depend partly on the format they're in and partly based on your preference for a formula or non-formula solution:
VALUEfunction.DATEVALUEfunction.- Find & Replace.
- Text to columns.
VALUEandSUBSTITUTEfunctions.- Error checking.
Full article: 6 ways to fix dates formatted as text in Excel
Knowing how to use Pivots is one of the MUST KNOW features of Excel.
This blog shows you, in a step-by-step manner, how to produce a summary report/analysis using a Pivot table.
Full article: Excel - Basics of pivot tables
This short article shows you how to sort a range of (unique) data using formulas.
The technique uses the COUNTIF function in a helper column to extract the order range in numeric values. It then uses a VLOOKUP function to create the dynamically sorted list.
Full article: Sort data using formulas
In the old days, we could use a combination of the IF and ISERROR functions to clean up reports. This method was good, and worked for many years... but starting with Excel 2007 there is an easier alternative.
This post explores the IFERROR function.
Full article: Clean up reports with IFERROR
