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.
If you change a formula in someone else’s file you should tell them. In fact there are a few things you should do to make it easier. This can apply to changes to your own files as well.
Getting dates into order is usually a job for Power Query, but not everyone has it or uses it so I still get requests for formulas to fix text dates.
Let’s assume you have three state codes and four department codes and you want to create a table of all the possible 12 combinations (3 x 4). How do you do it so that it is flexible? i.e. if you add a new state or department it must be easy to update the combination table.
Excel will automatically decrease the print zoom % to fit to one page, but it won’t increase the zoom % to fit to one page. E.g. if you want to print on A3 instead of A4. I had a request to do this, so I wrote a macro to do it.
There are certain situations when you can’t “unhide” columns on the left and/or rows at the top and it is not a sheet protection issue.
There are a number of mouse and keyboard shortcuts for copying. But there is one type of copy that can be frustrating. Copying dates can be challenging because, in general, Excel wants to increment them, not copy them. There is a simple technique to instruct Excel to copy a date.
Excel 2016 has introduced a new type of IF function to simplify handling multiple conditions. It is called IFS.
Some systems add DR and CR to the end of numbers when they export into Excel. This renders the values useless for normal calculations. You can use data cleansing techniques to remove the characters using formulas or Power Query. There is one function however that can perform calculations on these types of entries.
Charts have a behaviour that many people don’t realise. That behaviour can also be turned off. If you hide a row or column in the data range used by a chart, the values will also be hidden on the chart.