If you have the latest Excel version or the subscription version, you may have noticed some refreshing improvements to PivotTables.
Let’s assume you need to fill a vertical range with all the whole numbers from 1 to 100. There are lots of ways to do this, but I think the keyboard could be the quickest.
Formatted Tables allow you to create formulas that automatically copy down as the table expands. To create a running total in a column you have a couple of options.
Timeline charts are an effective way to display events over time. You can use a new Excel 2016 feature to easily create a timeline chart.
Would you like to change the format of all your formula cells so they have a different fill colour or font? There is a way in Excel 2013 onwards.
In a recent webinar on conditional formatting I was demonstrating how to create a horizontal progress bar using conditional formats when someone asked an interesting question about creating a vertical progress bar. It is possible and in this blog post I will explain both techniques.
When you record a macro that refers to a particular cell or range on a particular sheet in Excel the range reference is hard coded into VBA (macro) code. Unfortunately this means if rows or columns are inserted or deleted in the reference range the code is not updated. There is an easy way to get around this.
When you are building a PivotTable based on two related tables you may see unusual layouts that don’t make sense. Don’t worry, when you add values to the table all will be fixed.
Recently Liam Bastick (Excel MVP) wrote an article about using the OFFSET function to calculate depreciation in financial models. You can check out the full article here.
When plotting Actuals and Forecasts on a single line chart you may want to use a vertical dotted line to identify where the Actuals finish and the Forecasts begin.
There is an easy technique to copy a single formula and paste it without affecting relative references but what if you wanted to paste lots of formulas?
Many people know that you can select the whole sheet with Ctrl + A but there are lots of other selections it can perform.
Here’s another way to create a Step Chart. This one is quicker. I wrote previously about using a scatter plot and error bars but it required a lot of chart changes. This one hacks a line chart and requires no chart changes.
Let’s say you have a filtered list and in each of the filtered cells you want to enter a sequential number, but in the hidden rows you don’t want to enter anything. There is a way, but it takes a few steps.
My book was published just over four years ago and part of the writing process was creating an Index. To make the task easier I wrote a macro to assist me. Adding an Index to a large document can improve its usefulness. I am sharing the file I used in this blog post.
When you only want to copy the visible cells Excel has a special option that allows you to select visible cells only before copying. But there a way to make that the default option.
When you create a checkbox you need to link it to a cell on a sheet to be able to use its result. The user could overwrite that linked cell with a value or text and affect formulas that are using the checkbox linked cell. You can add a validation to make sure the linked cell only contains TRUE or FALSE.
Have you tried using different line colours in Excel? I’ve been experimenting with using grey lines instead of the default thin black lines when formatting reports.
Gauge charts are not a standard chart in Excel and are quite complex to create. Gauge charts should be avoided for dashboards.
In a recent webinar I was asked about the “Too many different cell formats” error. This tends to be an error in Excel 2010 and earlier versions. In many cases this error is caused by having too many custom Styles.