Here is a technique that allows you to turn off and turn on the conditional format without actually removing the conditional format. You may want to do this to print a sheet without the conditional formats being applied.
Tag Archives: formatting
Missing Sheet Gridlines in Excel
Its a format
Ever seen a sheet with what looks like missing gridlines? Wondered what happened? The issue is a format.
Free Excel Webinar Recording – Keyboard Shortcuts
Feedback score 93%
When I ran some face to face training sessions recently I was reminded how much people LOVE keyboard shortcuts. So I decided to update my keyboard shortcut webinar.
In October 2018 I shared lots of keyboard shortcuts. Download the materials using the button below and watch the video.
Using the mouse isn’t always the quickest way to perform tasks in Excel. Keyboard shortcuts can speed up your work and save you hunting through screens and dialogs. Some of the keyboard shortcuts you will learn in this session are:
- wrap text and other useful formats
- applying row and column grouping
- selecting a table quickly
- copying visible cells only
- apply and remove Freeze Panes
- returning after following a hyperlink
- how to avoid an annoying feature of formula and reference dialogs when you press an arrow key to move around
- get the most out of the Tab key
There will be lots of other shortcuts as well. Even if you prefer using the mouse you might learn a few useful new techniques.
Free Excel Webinar Recording – What If Techniques
Feedback score 92%
My free Excel webinar for September 2018 covered What If Techniques. Download the materials using the button below and watch the video.
Content applies to Excel 2010 and later versions. You will need to install the Solver Add-in – instructions in the manual and video.
- Goal Seek – simple what-if changes
- Solver – advanced what-if analysis
- Scenario Manager – handling different sets of inputs
- Data Tables – single and double variable sensitivity analysis
- NEW – Forecast sheet
Auto Format Formulas in Excel
Excel 2013 onwards
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.
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
Make Excel Grey Again
Grey isn't always a sign of aging
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.
Vote No for Gauge Charts
They take up too much space
Gauge charts are not a standard chart in Excel and are quite complex to create. Gauge charts should be avoided for dashboards.
Free Excel Webinar Recording – Copy and Paste Tips and Tricks
Feedback score 93%
My free Excel webinar for June 2018 covered Copy and Paste Tips and Tricks. Download the materials using the button below and watch the video.
The session focuses on the Paste Special dialog plus a little known pasting feature that is great for dashboards.
It covers the hows and whys of
- Paste Values, Paste Formulas, Paste Formats
- Converting negative to positives
- Fixing Text numbers in-situ
- Applying a Factor to a range
- Paste Link – how and why to use it
- Transpose (switching rows to columns and visa-versa)
- Paste Picture Link (great for dashboards)
The session includes lots of keyboard shortcuts. As always, I shared a few other tips during the session.
Fixing too many different cell formats
A VBA solution or two
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.
Excel Protection Tips
Letting users make input entries
When you are setting up a protected sheet that allows users to make inputs, a few option changes can make the user’s experience a lot easier.
Replace Merged Cells with Center Across Selection
Let the macro do the work
Unfortunately lots of people use the Merge & Center format in their spreadsheets. When working with other people’s files that contain Merged cells I will often remove the Merged cells format and apply Center Across Selection which is the preferred format to use. The macro below will convert Merged cells to Center Across Selection.
Getting Date Headings Right
Formulas rule
If you are using date-based headings in your reporting models please consider using dates in the headings rather than text. I’ll explain why.
Sorting By Colour in Excel
It is possible
Yes, you can sort by colour in Excel! This feature makes it easy for you to colour code cells and then place them together at the top of your data set.
Checkerboard Format in Excel
How to get alternate shading in cells
I was looking at a calendar and noticed it used alternately shaded cells, like a checkerboard, for all the dates and thought Excel could do that.
Clearing Input Cells with Colour
Let’s say you have a file that has numerous input cells, in numerous sheets. Each month you must clear the input cells and start the new month. There is a technique that allows you to clear the input cells if you have used a dedicated colour for those cells.
Japanese Yen Format in Excel
Applying it quickly
To apply the Japanese Yen format can take quite a few mouse clicks.
The macro that does it, on the other hand, is quite simple. Select the range, then run the macro.
Sub JapaneseYen() Selection.NumberFormat = "[$¥-411]#,##0.00" End Sub
If you are unsure how to use macros, see the link below.
Excel Flowchart Technique
Excel has great charts to help you visual your numbers, but it can also allow you to use flowcharts to help visualise numbers in a different way and help explain relationships between numbers and how they are formulated.
Excel – Change Format When Updated
Here’s the problem. You have a list of parts and prices, you want to manually update each price and you want the format to change for each price that has been updated. How can you do it?