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.
Horizontal or Vertical Progress Bar in Excel
Conditional Format or Sparkline take your pick
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.
Pain is inevitable. Suffering is optional.
Old Buddhist saying
Range Names and Macros
Range names rule
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.
Take care of your body. It’s the only place you have to live.
Jim Rohn (1930 – 2009)
Related Tables and PivotTables
Don't Panic!
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.
Conditional Format Feedback
The Conditional Format webinars I ran in August were popular and well received- 94% feedback score.
An email I received recently is shown below.
To watch the recording and download the materials click here.
Power Query
Its always great to receive e-mails like this. Power Query is a game changer.
Free Excel Webinar Recording – Conditional Formatting
Feedback score 94%
My free Excel webinar for August 2018 covered Conditional Formats. Download the materials using the button below and watch the video.
Content applies to Excel 2010 and later versions.
This session will take you through the basics, as well as an introduction to formula-based formats.
- Data bars
- Creating a progress bar using a Data bar
- Colour scales – traffic light colours
- Amending the default settings – getting the result you want
- Icon sets – icons can be better for colour blind people
- Cell-based rules – make the most of built-in features
- Working with dates automatically
- Formula-based rules – use formulas and functions to gain total control over conditions
Depreciation using the INDEX function
Alternative to OFFSET
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.
Adding a Vertical Dotted Line to an Excel Line Chart
An error bar solution
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.
Import PDF Update
The Power Query engine keeps getting better.
Hopefully soon it will handle PDF documents.
If you want to watch the presentation mentioned above here is the link.
Copying Without Affecting Relative References
Works for Excel 2013 onwards
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?
Selecting All with Ctrl + A
Some other useful techniques
Many people know that you can select the whole sheet with Ctrl + A but there are lots of other selections it can perform.
An Easier Step Chart
A Line chart solution
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.
Printing Tip
If your sheet has a lot of colour and you want to print it on a black laser printer, one of the Page Setup options can improve the print.
On the Sheet tab of the Page Setup dialog there is a Black And White option – see image below.
This removes all the colour and prints in black only,
You can access the Page Setup dialog from the Page Layout tab. Click the small arrow on the bottom right of the Page Setup section.
Sequential numbers in a filtered list
A formula solution
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.
Data entry and calculating
In some large models Excel may calculate for a few seconds after you make an entry.
In most cases you don’t have to wait for Excel to finish calculating before you make your next entry.
Type your entries as fast as you like. Excel will finish calculating once you are done.
Creating a Book Index in Excel
A macro to simplify and speed up the process
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.
A listing of all the DAX functions with their syntax.
DAX functions are used in Power BI and PowerPivot for Excel.
It is a reference site, not a training site.
It has been created by Marco Russo and Alberto Ferrari – legends in Power BI and all things DAX.
Link to DAX Guide