Excel tips and tutorials
To show text on separate lines in an Excel worksheet cell, use a shortcut, Alt+Enter, to add a line break. If you want to remove them later, use Ctrl+J in Find and Replace. This short video shows the steps.
Continue reading “Quick Tip: Find and Replace Line Breaks in Cells”
In Excel, use a pivot table to quickly count the duplicate items in a list. No formulas needed with this Excel tip! See how to do this in the short video, and written steps, below.
Continue reading “Count Duplicates in Excel List With Pivot Table”
If an Excel drop down has a long list of items, it can take lots of scrolling to find the item you need. To make things easier, add single-letter headings in the list. You’ll be able to get to any starting letter quickly. See Roger Govier’s technique for setting that up, and an updated version of his macros.
Continue reading “Add Letter Headings in Excel Drop Down List”
Here’s a quick Excel tip, to get you off to an easy start in 2021! This short video shows how you can quickly divide a set of numbers, by using the Paste Special command. No formulas required!
Continue reading “Quick Tip: Divide Excel Numbers Without Formulas”
Happy New Year! To wrap up the old year (goodbye 2020!), here’s a roundup of Excel articles and links. I hope you find a few interesting things to read, and new Excel features to try.
How can you count specific words in a text string, using an Excel formula? For example, a cell has this text – air, hair brush ,hot air, hair. The word “air” should be counted once. It shouldn’t be counted if it’s part of other words or phrases.
Continue reading “How to Count Specific Words in Excel Text String”
In Excel for Office 365, there are threaded comments, where you can have discussions with your co-workers. Or, reply to your own threaded comments, with updates on the data. The old-style comments are now called Notes, and they still work the way they always did. Here are a couple of macros that you can use to work with the new Excel threaded comments.
In all versions of Excel, you can use a simple formula, with the & operator, to combine values from different cells. If you want the numbers formatted a certain way, use the TEXT function to set that up.
Continue reading “Combine Text and Formatted Numbers in Excel”
Spill functions can fill neighbouring cells with their results, to create dynamic ranges. Thank you to Dermot Balson, who sent me his Excel workbook with examples for all the new functions (dynamic arrays) in Excel for Office 365. The examples show how VBA functions can spill too, and even some old Excel functions will spill automatically. Continue reading “Excel Spill Formula Examples”
Happy Spreadsheet Day 2020! We’re celebrating on a Saturday this year, so you have the whole day to relax, and play with spreadsheets. (Just like we do every day, right?)
