Grouping is a powerful feature in PivotTable reports but sometimes Excel won’t let you apply grouping. There are a few reasons for this.
Category Archives: Excel Blog
Identify Milestone Years in Excel
MOD function to the rescue
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.
My free Excel webinars are back
All webinars are not the same
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)
Excel Test For One of Two Conditions
A shorter alternative
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.
Excel Formula to Extract the Domain
Using the SUBSTITUTE function
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.
Checking Rounded Totals in Excel
Another SUMPRODUCT technique
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.
Naming Charts in Excel
Two techniques
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.
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.
Stop Charts Resizing
Works with other graphic objects like text boxes
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 and Open Excel Files
Old school solution
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.
SUM or COUNT based on code length in Excel
SUMPRODUCT solution
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.
Good Excel Ettiquette
Common sense
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.
Another date solution
Formula to the rescue
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.
Creating a table of all combinations in Excel [Video]
Power Query solution
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.
Expand to Fit to One Page in Excel
Macro solution
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.
Unable to Unhide Columns or Rows in Excel
It may not be a protection issue
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.
Copying a Date Down a Long List in Excel
Two techniques make it quick and easy
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.
Towards a Simpler IF Function
New function in Excel 2016
Excel 2016 has introduced a new type of IF function to simplify handling multiple conditions. It is called IFS.
Handling DR and CR at the end of numbers in Excel
Sorting out debits and credits
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.
Hide and Seek with Excel Charts
Hiding rows and columns
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.