Displaying decimals versus Rounding in Excel
This article describes a situation where there was a significant difference between the calculated and expected values.
The problem was caused by the way that Excel rounds values for display purposes, but the formula is calculating using the actual cell content.
ROUND function can help resolve this type of problem.
Visualizing parts to a whole in Excel charts
When you think of visualizing parts to a whole in Excel charts the first thing that's likely to come to mind is the pie chart, or if you're Homer Simpson then you might think of doughnut charts!
Using football as inspiration, let's look at our options for visualizing parts to a whole data using a standard set of football statistics.
Chart types explored in this article include:
- Pie chart.
- Doughnut chart.
- 100% Stacked Column/Bar chart.
- Stacked area chart.
- Line chart.
- Layered bar chart.
Excel PivotTable error handling
Obviously, we don't want our PivotTables littered with
#NULL! errors if we're presenting them in a report. That would just create unnecessary questions and we're busy enough.
Thankfully Excel PivotTable error handling is easy to control via the PivotTable Options. This article shows you how.
As a bonus, this article also discusses what to display when the prior period was zero. Specifically, is the percentage change from nothing to something equal to 100%?
10 things you should never do in Excel
Mastering Excel takes some experience and patience, but it's easy to make mistakes even if you've been using it for a long time. Sometimes, choices seem like a brilliant idea—until they're not, and the resulting problems are hard to troubleshoot.
In this article, I share 10 ways to avoid actions that seem good... at the time:
- Rely on multiple links.
- Destroy data.
- Rely on default settings.
- Ignore Table objects.
- Use Excel as a database or Word processor.
- Forget to protect your work.
- Leave blanks.
- Use numbers as column headings.
- Allow error values.
- Sluff off backups.
Excel IFERROR function with formula examples
The tutorial shows how to use
IFERROR in Excel to catch errors and replace them with a blank cell, another value or a custom message.
You will learn how to use the
IFERROR function with
INDEX MATCH, and how it compares to
IF ISERROR and
4 alternatives to nested IF formulas
IF formulas are extremely useful for complex decision making on a spreadsheet, but they can also be long, messy and convoluted.
This blog post explores 4 alternatives which are easier, faster and cleaner than the classic nested
VLOOKUPfor an exact match.
VLOOKUPfor a range lookup.
- The fantastic
7 common VBA mistakes to avoid
When it comes to VBA, it's almost too easy to make a mistake. These mistakes can cost you greatly, both in time and in frustration.
In this post, I'd like to help you avoid these typical mistakes and make you a better VBA programmer:
- Using the
- Not using
Application.ScreenUpdating = False.
- Referencing the worksheet name with a string.
- Not fully qualifying your range references.
- Making your
- Going down the nested
5 reasons to use an Excel Table as the source of a Pivot Table
Learn why using an Excel Table as the source of a Pivot Table can save time and prevent errors.
The reasons why you should use Tables for the source data range of your Pivot Tables are:
- Adding new data & preventing embarrassment.
- Eliminate maintenance on multiple Pivot Tables.
- Prevent errors when creating Pivot Tables.
- Avoid whole column references.
- Prevent the Filter controls error with connected Slicers.
Microsoft Excel: Rules for designing Excel workbooks
Q. Is there a list of standard Excel design rules we should be following as we create new Excel worksheets?
A. By following a common set of spreadsheet design rules, companies can produce more consistent workbooks that may be easier to review, edit, and use by others in their organizations:
- Table of contents.
- Print macro buttons.
- Avoid embedded assumptions.
- Well-organized worksheet assumptions.
- Assumptions in yellow cells.
- Name assumption cells.
- Error-checking formulas.
- Organize your template by worksheets.
- Simplify complex calculations.
- Consistent look and feel.
- Add File Properties.
- Cross-footing and error-checking formulas.
- Worksheet protection.