Category: Excel

Excel Rules for Creating Formulas and Functions

There are lots of rules out there in the world, and we don’t always follow them. Most everyone I know has broken the speed limit at some point, and I hear some people are actually eating laundry detergent recently (ugh!). There are clearly marked speed limits on the side of every highway, and I trust there must be a label on the side of detergent boxes that says do not consume. But breaking those rules isn’t necessarily going to ruin …

Excel Rules for Creating Formulas and Functions Read More »

Tagged with: , ,

New Text Box Shortcut works in PowerPoint, Word and Excel

Many users feel condemned to using the mouse to hunt and peck through the Microsoft Ribbon. They just don’t know a better way! Today’s tip is going to drill down on one shortcut, how to add a new text box in Microsoft PowerPoint, Word or Excel using the keyboard. Once you have the program open and are ready to perform this technique, just press the Alt key and the letter N at the same time, then let go of those …

New Text Box Shortcut works in PowerPoint, Word and Excel Read More »

Tagged with: , , , , ,

When Excel Columns become Numbers instead of Letters…

aka Turning the R1C1 Address Option On and Off A client called me a while back with a terrifying problem: all of the column letters in Excel had become numbers and she had no idea how it got that way or how to write formulas anymore. Rest assured, it was not the beginning of the robot rebellion, and we were able to get her back up and running right away. The problem was that R1C1 notation had gotten accidentally turned …

When Excel Columns become Numbers instead of Letters… Read More »

Tagged with: , , ,

Password Protect Macros in Excel

I confess that today’s tip was motivated by a student asking a question in a recent class. During a discussion about all of the different ways you can password protect a document, the question was asked about how to password protect just a macro. This technique will allow you to protect your macro code from snoops and ne’er-do-wells, but still allow the document to be opened without a password AND the macro will run without a password. In order to …

Password Protect Macros in Excel Read More »

Encrypt Excel with a Password

The most complete protection you can provide an Excel document is Password Encryption. It requires that someone have your password before they can open or view your workbook, and it is virtually bullet-proof. I’m not aware of any ways you can break into an encrypted Excel workbook, but I wouldn’t put it past some cyber-fiend to find a way! That being said, two critical cautions come to mind: Don’t forget your password. There is NO password recovery feature. Don’t give …

Encrypt Excel with a Password Read More »

Tagged with: ,

Nested Functions and Formulas in Excel

Lots of Excel experts love to nest functions and formulas inside of each other. This is actually a great way to combine several calculations in one cell, but it can be downright confusing if you are looking at it without understanding the Orders of Operation. The most important mathematical symbols in Excel are the parentheses. Everything inside of parentheses happens first in any formula, and if there are parentheses inside of parentheses, then you always work from the innermost set …

Nested Functions and Formulas in Excel Read More »

Tagged with: , ,

Concatenate(): Your $10 Excel Function of the Day

You may have already read my previous post on how to string text together in Excel. That technique has been around forever, but it is so… 1995. Modern Excel users know that there is a very powerful function built into Excel that makes stringing text together as simple as 1-2-3. Before we get started, let’s review a few salient points: You can’t use math to add columns of text together; and Whenever you are combining columns of text you will …

Concatenate(): Your $10 Excel Function of the Day Read More »

Tagged with: , , , , ,

Stringing Text Together in Microsoft Excel

I showed you how to break text in one column into multiple columns using the Text to Columns tool in a previous Tip, but today I’m going to show you how to string text together. In other words, same problem, just going in a different direction. You can’t add words together… As you can see, I’ve got a list of first names in Column A and last names in Column B. You might be inclined to write a formula that …

Stringing Text Together in Microsoft Excel Read More »

Tagged with: , , , , , ,

Text to Columns in Microsoft Excel

The need to break one column into multiple columns is one that every Excel user needs upon occasion. In the accompanying example, you can see that both the first and the last names are combined into one column. If I was handed this worksheet, the first thing I would want to do is break the two names into separate columns. Back in “days of yore” this would entail hours of Cutting and Pasting. Thanks to a feature in Excel called …

Text to Columns in Microsoft Excel Read More »

Tagged with: , , , , ,

Why things don’t always add up in Excel

Number formatting is a great way to change the appearance of a number, including the number of decimal places of accuracy, but it can also yield some strange results. If you’ve ever had the experience of puzzling over a column of numbers that AutoSum can’t seem to add up, or perhaps you’ve got percentages of a whole that add up to more than 100%, then this article is for you! Number Formatting Number Formatting is a quick and easy way …

Why things don’t always add up in Excel Read More »

Tagged with: , , , , ,