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.
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.
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.
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.
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.
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.
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.
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.
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.
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 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.
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?
Using numbers in automated text sentences can be frustrating. Typically you don’t want to display decimals, but you do want to use the comma format.
Excel has had an AutoCalculation feature for many versions. This means you can see the result of common functions without typing a single formula.
The Format As Table feature has many useful features that are worth taking advantage of. The previous post listed them. The video of this blog is shown at the bottom of the post.
Templates allow you to create blank sheets and blank workbooks that have customised formats as well as customised Page Setup settings, including headers and footers.
The new FORMULATEXT function in Excel 2013 will make my Excel training job a little easier. It also has a formatting use.
Keyboard shortcuts can really speed up your work in Excel. Here are some of my favourites that use the Ctrl key. I’ll share some more in later blog posts.