Excel tips and tutorials
Do you sometimes get an Excel error that says "Cannot shift objects off sheet" or "Can't push objects off the sheet"? Why is Excel complaining, and how can you stop it? I was able to fix the problem with a keyboard shortcut: Ctrl + 6. Maybe that will work for you too.
Continue reading "Excel Error Cannot Shift Objects Can’t Push Objects"
If you have a list of names or topics in Excel, here's how you can quickly create PowerPoint slides from that list. Unlike most of my macros, these run in PowerPoint, to pull the data from Excel. That was easier than writing Excel macros to make things happen in PowerPoint!
Is the Excel IF function underused, overused or too often abused? Or some combination of all three? This short video shows how to make a simple IF formula. It checks if a cell is empty, then returns a result, based on that test. Too often though, the IF function is nested about 15 layers deep. Those formulas are confusing, and could usually be replaced by a much simpler solution. What are your nested IF Formula horror stories?
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.
