As we get used to the new year we may want to perform some calculations based on the old year. A recent inquiry requested a formula that could calculate the percentage of a year that an employee had been employed. He suggested using an IF function. See the solution below, but it doesn’t involve the IF function.
Category Archives: Excel Blog
Format As Table Webinar Recording 2023
In this session you will learn all about Excel’s formatted tables. Using Formatted Tables is an essential skill in Excel. Use the buttons below the video to download the materials and completed file.
Many of Excel’s features and functions work seamlessly with formatted tables. They can help you improve the structure and reliability of your spreadsheet files.
Formatted tables can allow you to create powerful reports like those in a relational databases.
Topics covered
- advantages and limitations of formatted tables
- keyboard shortcuts
- using formatted tables with formulas
- solutions to some of the limitations of formatted tables
- using range names with formatted tables
- using formatted tables with data validations
- creating a running total
- using PivotTables
- Relationships (Data tab)
- introduction to dynamic arrays
As always I shared a few other tips.
Rounding to the Nearest 9 Cents in Excel
A client recently requested a formula to round to the nearest 9 cents. This avoids getting to a price point. This is a common requirement in retail businesses. The solution was simpler than I thought it would be.
“Every action you take is a vote for the type of person you wish to become. No single instance will transform your beliefs, but as the votes build up, so does the evidence of your new identity.”
James Clear – Atomic Habits
Atomic Habits
Custom Function for Age in Excel
Calculating age with years months and days takes several functions to create. Whenever you need multiple functions to calculate an entry you may have a good use case for a custom function.
Custom Function for Days in the Month
Excel’s EOMONTH function is great. Here is a tweak using a custom function to calculate the number of days in a month.
Excel’s New Navigation Pane
If you have the subscription version of Excel then you have access to the Navigation Pane. This allows you to navigate between sheets and see the structure of the sheets in your file.
One Minute to Excel #30 – Extract Data Based on Sheet Name
Shortcut to speed up name creation
Here’s a technique I use a lot to speed up report development.
Sheet names have to be unique, so they can’t be duplicated. This makes them great for department names or states.
This short video combines a few techniques to extract from a data set based on the sheet name.
All in less than a minute.
Excel Ranking with Sequential Numbers
Excel has three functions that can rank values. Unfortunately none of them provide a way to create a sequential list of ranking numbers when there are duplicates. Luckily we can adapt the RANK function to handle duplicates and create a sequential list of ranking numbers.
Excel Constant not a Constant
In Excel you can use the Go To Special dialog to find constants. These are cells that won’t change. Constants are things like labels, entered text, numbers, or dates. But there are cells that won’t change that Go To Special won’t identify as a constant.
Hyperlink with Search
I wrote a post in 2016 about using INDEX and MATCH to create a hyperlink based on a search result. I thought I would update the technique using XLOOKUP.
Excel Power Query Saving Issue
There was a recent discussion of this topic on LinkedIn. When you are in the Power Query window you can’t save the query. If you Close and Load and Excel crashes, you could lose your work. If you have spent some time working on a query this can be frustrating. Here’s how you can capture the query before using Close & Load.
A Cell Reference Custom Function
The formula to return a cell reference is quite long. This makes it an ideal candidate for a custom function.
Date Alignment Trick in Excel
Text alignment in Excel is versatile. If the column isn’t wide enough to display the text, it will display over the next cell. Date and number alignments are not so forgiving. If the column isn’t wide enough the cell with display the ### symbols or the scientific format. Here is a function technique to get around the limitation.
Number Formats in Excel
When you link to a cell that has a number format you may be surprised to learn that as well as the value that is linked, the format also is updated based on the cell you linked to.
Pasting Trick in Excel
When you copy from another system and paste into Excel the result may not be what you expect. One way around that is to paste in to Excel and match the format of the destination cell. Here’s how you do that.
Circular Reference in Excel but the Circular Reference Button is Greyed Out
Sometimes Excel is not very helpful when it tells you that there is a circular reference in your file. If that is the case here is a technique to help you find where the circular reference is.
Excel VBA to Sort Sheets by Name
In an earlier post I shared some VBA code to sort sheets by their colour. In this post I have tweaked the code to sort the sheets by their name.
Not So Random Entries in Excel
Sometimes when you are testing or training in Excel you need to create random entries. That is easy to do with the RANDBETWEEN or RANDARRAY functions. What if you wanted to emphasise some entries more than others in the random list created? There is a way.
Array Syntax in Excel Formulas
With dynamic arrays making array calculations more accessible and easier to use here is a hack for using array syntax in Excel formulas.
The most powerful people are the ones who never stop learning.
Rejoice Denhere
Quotation Marks in Excel VBA
Sometimes when you are building a text string in VBA you may need to insert quotations marks. That poses a problem because quotation marks are used to surround the text you want to join and using them within the text is problematic. There is a solution.
Avoid the #DIV/0! Error in Excel
DIY Function
If you have the subscription version of Excel you can create your own functions. One that you may want to create avoids the #DIV/0! error.
Sparklines and Dates in Excel
A Sparkline chart is a single data series chart (graph) that exists in a cell. Usually, you only need to select one range to create a Sparkline. Sometimes you may need to handle missing dates.
We run the company by questions, not answers.
Eric Schmidt (former chairman and CEO of Google)
Selecting a Column Range within a Merged Cell in Excel [video]
I am not a fan of the merged cell format. It causes more problems that it solves. One issue you will face is trying to select a single column range within a range that has a merged cell. Here is how you handle it.
This post is a video post as it easier to show the problem and the solution in a video.
You don’t get harmony when everyone sings the same note.
Doug Floyd (the Spokesman Review – Washington paper)
Sparklines and Merged Cells in Excel
I am not a fan of Merged Cells but there may be a case to use them when creating a larger Sparkline chart.
One Minute to Excel #29 – Nine Names in a Minute
Shortcut to speed up name creation
In this short video I demonstrate how to create range names quickly based on labels. Range names are a powerful formula feature. I also demonstrate their use.
There are no old roads to new directions.
attributed to Boston Consulting Group