The TreeMap is like a square pie chart, but it has the added ability to show a hierarchy.
Over the last five and a half years I have run more than 20 free Excel training webinars. The topics covered are varied and I have had requests to list the best order to watch the recordings.
It is common knowledge (or it should be) that running a macro clears the undo list. In general you can’t undo a macro. However some macros also clear the clipboard which can stop you copying and pasting. I have found a workaround for the clipboard problem.
Over the years I have had many requests to help people insert blank rows between entries is a list. Apparently it is for an input routine that requires blanks. My normal solution is a macro because it automates the process, but there is a manual technique that is quick and easy.
The Step chart is not a standard Excel chart but it is a useful way to display values over time. You have probably seen a step chart but you may not have known what it was called. It sort of looks like the city skyline or something you would create on an Etch-a-Sketch.
Let’s assume you have a large table that you are filtering. Based on the current filter you want to work out the earliest date and the latest date. You may be surprised to learn the SUBTOTAL function can help you.
Pie charts have a lot of drawbacks and limitations. One major limitation is they can’t handle negatives. One of Excel’s new charts can help out.
How do you know if a cell is locked in Excel? There is an easy was to find out. And it also makes it easier to lock or unlock the cell.
Let’s say you are creating a new table in a new sheet using a macro and you need to create the headings in row 1. There is a reasonably easy way to do it.
Grouping is a powerful feature in PivotTable reports but sometimes Excel won’t let you apply grouping. There are a few reasons for this.
Recently a client wanted help in summarising a large data list of employees. They wanted to identify the years of service in terms of 5, 10, 15 years and other milestone years based on a start date.
I recently ran my first public webinar in nearly a year – Materials Link and streaming Video below.
I run free and paid Excel training webinars. Watch a recording or come along to a free session and see if you like them.
My aim is to make my live training webinars as enjoyable, useful and practical as possible, which means
- no PowerPoint slides EVER
- zero marketing or up selling – just Excel training
- no recorded content – its all live and interactive – comments and questions can be sent via a chat box
- detailed instruction manuals – yes, even the free sessions have a detailed pdf manual
- the free session recordings are always available – no time limits on watching
- paid recordings can also be downloaded
- annual attendance listing for CPD purposes when you attend a live session (sent out late January each year)
- you must record your own CPD when you watch a recording (like the one below)
In Excel it is quite common to test a cell for either a zero or a blank. If either of these two entries are found then you do a particular calculation. There is an easy way to handle this.
I recently read a blog post about using Excel for SEO (Search Engine Optimisation). It mentioned a function to extract a domain from a URL. The function was from Google docs, not Excel. So I wrote an Excel formula to extract the domain from their list of URLs.
Rounded values in Excel can pose a few issues. There is a formula you can use that can round a range of values and then SUM the results. This can be used as a check total for rounded values.
You may have noticed that Excel gives every chart a unique number when it creates the chart. It is displayed in the Name Box in the left corner above the grid. You have the ability to change that name and make it more descriptive.
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.
We’ve all been there, our charts are looking just right and then some one inserts a column or changes the column width and throws out all our perfectly proportioned charts.
Power Query can easily combine data from multiple Excel files. A problem can arise if one or more of the files is open. Power Query will generate an error and the import will fail. The solution involves an old school Excel feature.
Let’s say you have codes that have differing numbers of characters and you need to analyse them based on how many characters a code has. There is one function that can SUM and COUNT based on the number of characters in a code.