In our everyday life, the words Merge and Combine have almost the same meaning. However, when you’re working in Microsoft Excel, there are important differences in their meanings!
Continue reading “Should You Merge Cells or Combine Text in Excel”
Excel Footer Font Fix and Custom Footer Macro
When you’re printing financial reports in Excel, do you set them up to print one page wide? That feature saves paper, but it can make the header and footer text too small to read, especially if it’s in a small font size already. Here’s how you can avoid that problem. And there’s a macro to create a custom worksheet footer too!
Continue reading “Excel Footer Font Fix and Custom Footer Macro”
Spreadsheets in the News 2023-06-29
It’s almost the end of June, and there’s a long weekend coming up! To celebrate, let’s take a look at some of the spreadsheet news from the past month. Here are 3 stories that caught my eye, and let me know if there was other noteworthy news that I missed!
EVE Online
Do you play EVE Online? It’s a role-playing game, where things can get so complicated that people use Excel to keep track of their activities.
Now that will be much easier to do, because Microsoft and CCP Games just released a free add-in to integrate Excel with EVE Online.
It’s the first and only game to have Excel data integration!
Read the details on the game’s official website, and get the free Excel add-in.
Create Spreadsheets with Google Bard
Google announced improvements to its AI chatbot, Bard, and now you can export tables to Google Sheets.
For example, I could enter this prompt:
- Please make a table to compare the populations of all Canadian provinces and territories
When Bard finishes the table:
- Check the table, to see if it looks okay – if not, try rewording your prompt
- Next, click the Export to Sheets button.
- Then, at the bottom left of the Bard window, click the Open Sheets link, to see your new spreadsheet.
Unfortunately, Bard isn’t available in Canada yet, so I’ll have to make my spreadsheets the old-fashioned way!
Life in a Spreadsheet
Some days, it can feel like you’re living in a spreadsheet! But have you ever considered graphing your life in a spreadsheet?
Emmett Shear, a co-founder of Twitch, is turning 40 soon, and tweeted the spreadsheet version of his life so far. He also posted a link where you can download your own copy of the spreadsheet, so that’s what I did!
Here’s a small section of Emmett’s life graph.
I was going to deduct points for the merged cells in the Month heading row, but then I noticed the merged cells in column A. It has two features that aren’t available in Excel:
- When selected, the merged cell range appears in the name box
- The merged cell gets vertical and horizontal scroll bars if the entire cell isn’t visible
I try to avoid merged cells, but if people insist on using them, these features seem helpful!
_____________________
Spreadsheets in the News 2023-06-29
____________________
How to Compare Two Lists in Excel-New Items
Do you ever need to compare two lists in Excel, and see if there are some items in one list, that aren’t in the other list? The video below shows how to check for new items, and only add those to your existing list. You don’t want to have a list that’s full of duplicates entries!
Continue reading “How to Compare Two Lists in Excel-New Items”
Excel Formula Troubleshooting Tips and Tools
What’s your favourite way to troubleshoot Excel formulas? Here are a few tips for current versions of Excel, and a preview of new features that are coming soon, for Excel 365.
Use the Formula Bar
If there’s a problem with a simple Excel formula, I usually click in the formula bar, so Excel colour codes the precedent cells on the worksheet.
Sometimes the wrong cell is in the formula, so I can drag the coloured borders, and choose the correct cell instead.
Video: Audit Excel Formulas
This short video has a few more formula auditing tips, from my Audit Excel Formulas page. There’s another video further down, with new features that are coming soon, for Excel 365.
Go to Precedent Cells
For a formula that’s more complicated, you can go to the precedent cells, with this keyboard shortcut:
- Â Ctrl + [
That shortcut will even take you to a formula cell on a different worksheet, but only if it’s the first reference in the formula.
Formula Troubleshooting Tool
For bigger formula auditing projects, you can use an Excel macro, to see where formulas have been copied down, across, or both.
In the screen shot below, I used a macro, written by Dermot Balson, which applies fill patterns to the formula cells.
- Fill lines go down, across, or crosshatch, to show the direction in which the formulas were copied on the worksheet.
- Solid coloured cells contain the original formulas
This colour coding makes it easy to spot cells where a formula is missing or different. That can give you a quick start in your troubleshooting!
And don’t worry – the colour coding is done on a new sheet, so it doesn’t mess up your worksheet!
New Excel Formula Tools
If you’re using Excel 365, there are new features coming soon, including improvements to the formula bar.
- In current versions of Excel, you can select part of a formula in the formula bar, then press the F9 key, to evaluate that part of the formula.
- In the upcoming updates, you’ll be able to click on an argument name in the formula tooltip, and see the values from that range of cells.
In this video, Mike Tholfsen, from Microsoft, shows 8 new Excel features, with the formula bar feature shown at the 6:28 mark.
Get the Excel Files
–1) Audit Tips: To get the formula auditing tips workbook, go to the How to Audit Excel Formulas page on my Contextures site.The zipped file is in xlsx format, and does not contain any macros.
–2) Formula Cell Colour: To get Dermot’s colour coding macro, and read more about it, go to the Formula Cell Colour Code Macro page on my Contextures site. The zipped file is in xlsm format, and contains the macros to colour coded the formulas. Be sure to enable macros when you open the workbook, if you want to test the macro.
_________________
Excel Formula Troubleshooting Tips and Tools
________________
Excel Find and Replace with Wildcard
With the Find and Replace feature in Excel, you can look for specific characters, and replace them with different characters, or replace them with nothing. You can also use wildcard characters in the Find What box, to make the find feature more flexible. The short video below shows you the steps.
Excel Template Guess the Word Game Cards
Since you’re reading this blog, you probably love spreadsheets. And do you love word games too? If you do, keep reading for a couple of ways to have fun with words in Excel!
Create Excel Monthly Calendar From 6 Formula Cells
Create a monthly Excel calendar, based on a few short formulas. Next, add formatting and a heading, and if you’re feeling fancy, show a list of the month’s holidays too! This example uses functions that are available in Excel 365, and in Excel for the Web.
Continue reading “Create Excel Monthly Calendar From 6 Formula Cells”
Unpivot Excel Data with Power Query Step by Step Video
This step-by-step video shows how to unpivot data in Excel, using Power Query. This creates better source data that you can use to build flexible pivot tables. And it doesn’t change your original data – you can leave that as is!
Continue reading “Unpivot Excel Data with Power Query Step by Step Video”
Happy Excel Day 45000
Where does the time go? Only 556 days ago, we were celebrating Excel Day 44444, and suddenly it’s Excel Day 45000! And we’re still cleaning up from yesterday’s Pi Day Party! Who says numbers are boring?