Category Archives: Excel Blog
SUBTOTAL and Dynamic Arrays in Excel – Part 1
The SUBTOTAL function in Excel is quite flexible. The single function allows you to perform 11 different calculations. It can also ignore hidden rows, something that not many Excel functions can do.
Highlighting Weekends in Excel
When you have a list of dates in Excel it can be useful to know which of those dates are weekends. You can automate a format for weekends using a conditional format.
Excel Custom Function to Return Unique Random Whole Numbers
In last week’s blog post I covered a complex formula to return unique random whole numbers. In this weeks’ post we will look at how we can convert that complex formula into a custom function.
Unique Random Whole Numbers in Excel
Excel has three functions that can provide random numbers. But the random numbers created may not be unique random whole numbers. Here is one way to get a list of unique random whole numbers.
Custom Calendar Function in Excel
A few years back I posted a series of three posts about developing a formula to create a month calendar in Excel. I thought I would revisit that and convert that long formula into a custom function using LAMBDA.
Average Top and Bottom Values in Excel
When analysing data you may want to check for outliers. You can use MIN and MAX to get minimum and maximum values but you may want to average a certain number of top or bottom numbers. Here’s how you can do it.
TEXTBETWEEN function in Excel
Excel has new TEXTBEFORE and TEXTAFTER functions. It doesn’t have a TEXTBETWEEN function. Let’s make one.
Extract Text Before a Number in Excel
Excel’s new TEXTBEFORE function simplifies extracting text from the left. In this example I share how to extract all the text before a number in a code.
Excel Dashboards #1 Guidelines & Techniques
In this recording of a live webinar I ran in March 2023 you will learn about guidelines and technique for building dashboards in Excel.
Use the button below the video to download the materials.
This is the first session is a series of five webinars on Excel dashboards. The other four sessions are paid sessions.
In this free session we will focus on chart and dashboard guidelines plus some techniques used to create better dashboards. We will also look at creating a bullet chart which is a better alternative to a gauge or dial chart.
The session covers
- general chart guidelines
- questions to ask yourself to develop better charts
- best charts to use
- charts and formats to avoid
- using text boxes on dashboards
- how to create small charts
- chart templates
- lining up and re-sizing charts
- useful number formats for dashboard charts
- using the #N/A error with charts
- how to create a bullet chart
As always I will share a few other tips.
Forcing a Spill with the Magic Plus Sign
I posted recently about a technique to force a function to spill if it didn’t spill automatically. I have since learned of a much easier way.
Excel VBA and the TRUNC Function
Excel has a TRUNC function that truncates numbers. For example =TRUNC(15.75) returns 15. It doesn’t round it just removes the fraction leaving the whole number. Excel VBA does not have the TRUNC function. It does have the Fix function that works the same.
Forcing a Function to Spill in Excel
Dynamic arrays can spill. The means a single formula populates a range. Most Excel functions can refer to and use a spill range. This causes the formulas to spill to match the spill range. There are some functions that don’t work with spill ranges. Here is a technique that forces a function to work with a spill range.
Customised Workbook Toolbars in Excel
You can customize the Quick Access Toolbar and improve your productivity by adding icons that you use frequently. You also add icons that only appear for certain workbooks.
Excel Power Query Errors List
Sometimes Power Queries generate errors. Some errors stop all data being returned and others will return a blank cell for the error in the returned table. You can list the rows that are generating the errors. This can help you identify what is causing the error.
If you think you understand everything that is going on, you are hopelessly confused.
Walter Mondale
Excel VBA to Get to the Top Left of the Screen
Sometimes when you are using Excel VBA you need the screen to always display at the top, left of the sheet. Here is how you do it.
Dreams are the seedlings of realities.
James Allen
Excel Power Query and Multiple Files 2023
In this recording of a live webinar I ran in late January 2023 you will learn how to import multiple files in a single Power Query. If you use data at all then Power Query is an essential skill to possess.
Use the buttons below the video to download the materials.
Building on the skills covered in the Introduction session, we will start working with multiple files. For example you may have 12 separate CSV files in a folder. All with the same layout, one for each month of the year. Power Query can import all 12 files as if they were a single file and create a table for the whole year
This session covers
• importing multiple CSV files from a folder
• a technique to capture the file name in a field (column)
• importing multiple Excel files
• merging data from multiple tables
• Extracting header information into a column from multiple CSV files
As always, I shared other tips and tricks along the way.
Introduction to Excel Power Query 2023
In this recording of a live session ran in late January 2023 you will learn how to automate data importation in Excel with Power Query. If you use data at all then Power Query is an essential skill to possess.
Use the buttons below the video to download the materials.
Power Query allows you to automatically perform data cleansing routines on your data sources – no manual intervention required. Simply refresh and your data is ready to use. You can use csv files; txt files; databases and existing Excel tables as your data sources.
Learn the basics, plus an advanced technique to automate data cleansing routines on your data sources.
This session covers
- fixing dates so that Excel can recognise them
- formatting columns as text – retaining leading zeroes in CSV files
- deleting unwanted rows and columns from your data
- removing leading and trailing spaces
- populating blank values with zeroes
- populating blanks with entries from above
- correcting trailing minus signs
- unpivot a report – converting a report layout into a table layout
- converting a MYOB report into a data table
- capture header information in a column
As always, I shared other tips and tricks along the way.
Percentage of the Year in Excel
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.
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
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.