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
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.”
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.
“Just start. Start slow if you have to. Start small if you have to. Start privately if you have to. Just start.”
James Clear
Open Power Query Shortcut
Woohoo ! A new shortcut to open the Power Query window.
Works in the latest subscription version of Excel.
Excel has a function to find the last day of the month. To find the last weekday of the month you can combine a couple of functions. Here is a custom function that also works.
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.
Hence a wealth of information creates a poverty of attention.
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.
The Carbon Almanac (Penguin, 2022)
Facts. Connection. Action.
With a forward by Seth Godin this is an unusual book.
Filled with FACTS about climate change, it was created by people from around the world.
The authors had different backgrounds and specialisations.
The book covers all the aspects of climate change.
There are many different problems we are facing caused by many different industries.
This book explains the problems and covers what we can do to avert a climate disaster.
It is not too late, but we don’t have much time – we need to act now!
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.
Power BI for the Excel Analystby Wyn Hopkins (2022)
If you want to start using Power BI and you are an Excel user then this is great book to start with.
It will give you the basics plus it explains lots of “gotcha” issues you can avoid when starting out.
Power BI is powerful but it is also complex and always evolving. This book will get you started. Highly recommend.
Task Manager
Wow – if you right click the Taskbar (bottom of the Windows screen) you can access the Task Manager.
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.
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.
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.
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.
We act as though comfort and luxury were the chief requirements of life, when all that we need to make us happy is something to be enthusiastic about.
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.
Amazon again
My book was published in 2014 and I bought the Kindle version from Amazon.
That doesn’t stop them trying to sell me the paperback version of my own book.
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.
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.
Check out the winners of the Data Literacy awards.
Related Posts