A frequent question on internet forums everywhere is how to do a least squares fit of a non-linear trend line to a set of data.
The most frequent answer is to plot the data on an XY (“scatter”) chart, and then use the "Fit Trendline" option, with the "display equation on chart" box checked. The chart trendlines have the options of: Linear, Exponential, Logarithmic, Polynomial (up to order 6), and Power. There is also a "Moving Average" option, but this does not provide a trendline equation.
The chart trendline solution is OK if what you want to do is display the trendline equation on a chart, but if you want to use the numbers in some further analysis, or even just display them elsewhere in the spreadsheet, or copy them to another document, it is far from convenient. Fortunately it is straightforward to get the trendline equations (and other statistics) for each of the chart trendline types using the LINEST worksheet function.
Full article: Using LINEST for non-linear curve fitting
With Excel 2013, Microsoft introduced several new functions, one of which is worth a separate mention. FORMULATEXT displays as text the formula in a cell.
Full article: The Excel FORMULATEXT function
I've been toying around with Excel Waffle charts (sometimes called Square Pie Charts). It's an interesting visualization that I've recently used to display progress toward goal.
In this post, I'll walk you through the steps to set up a waffle chart template and how to duplicate it for as many metrics as you need.
Full article: Not available
The next time you notice something being done in Excel where you work, take a moment to question whether it's the right tool for the job, or whether you or someone in your organisation is a tool for allowing its use.
No, not my words, but from the FT's consistently excellent Alphaville blog. The point is, it's easy to use Excel. But it's very hard to use Excel well.
Full article: The right tool?
In many offices, there always seems to be at least one person who is referred to as an Excel expert/master/wizard. Their masterful status often originates from one fact: they can make a PivotTable. Are you jealous of this person? Have you ever wished you could have that glory for yourself instead? Do you secretly wonder what a PivotTable even is?
Full article: Choose your own PivotTable
Data Validation is an excellent way to control data entry to meet a certain condition.
Suppose you want to insure that numbers entered in the yellow cells are OK for decimals, AND that only numeric increments of a quarter of a number are allowed. For example, the entry of 6.75 or 3.25 are allowed, but 4.35 or 1.62 are not allowed.
The following steps show how this can be done.
Full article: Using Data Validation to force a decimalized numeric entry
I've emphasized before that the key behind visual design is that every formatting decision you make should have a purpose behind it. In the following post, I plan to demonstrate more of the visual style I use in Excel:
- Dark background and white font.
- Soft gray lines.
- Dotted lines.
- White borders.
- Custom trim.
- Uniform column widths.
- Pastel highlighting.
- Excel data bars instead of color scales.
Full article: Excel visual design tricks
The SUBTOTAL function is great for calculating totals on a filtered list in Excel. Unlike the SUM function, SUBTOTAL ignores the values in rows hidden by the filter, and can even ignore manually hidden rows, so the total includes only the visible cells.
A new function, AGGREGATE, introduced in Excel 2010, is similar to SUBTOTAL, and has a couple of advantages.
Full article: Sum a filtered list with AGGREGATE function
Over the next few articles we will explore the usage of Excel tables which are a powerful feature of Excel.
We will look at when to use them and when not to use them, the advantages over using plain tables - simple ranges - and a few of disadvantages of using them.
We will also go through a practical example of building a spreadsheet to manage your current account, which will highlight many key points of using Excel tables.
See:
Full article: Excel Tables
