Excel for Librarians: Tips and Tricks on Working with Excel 2010
As a follow up to the earlier Excel blog, here are a few introductory tips to working with Excel 2010. These are intended to give you a few ideas about how to find your way around the application, as well as point out some shortcuts that are available.
One thing to keep in mind about all of the Office 2010 applications is that there are multiple ways to do any single action. Often there may be a button on the ribbon, a pop-up screen that can be accessed, and a short-cut way to access the same tool.
What Does Format Painter Do?
Format Painter is available in all of the Office 2010 tools. It is simple to use and the perfect shortcut to redesigning cells or text to match something that you already have. The steps are quite simple:
-
Highlight any text or cell in your spreadsheet or document.
-
Click the Format Painter button, located on the Home tab in the Clipboard group on the left-most side of your screen.
-
Highlight the new text or cell.
-
That's it!
If you want to reformat multiple areas, simply click twice on the Format Painter button and then continue to highlight the sections to reformat. What is great about this button is that it lets you change Font, Font Size, Style, Color, and other formatting elements all with one button.
The All-Powerful Right-Click
Another great shortcut for all of the Office 2010 applications is the right-click. If you are ever in doubt about how to do something or where to find the button that you need - right-click! This will pull up not only a mini-formatting toolbox, but also a list of possible actions that change depending on what you've right-clicked on. If in doubt, right-click.
What does $ do?
One of the trickiest parts about Excel is that not everything is a button. In fact, some of the more advanced ways that Excel can be used, can really only be learned by taking a course or reading a book about Excel. There is no way to know how to complete the action just by looking through all the ribbon buttons. One of the most important and most needed examples of this is the $ key. What is this for? The $ key is used to create what are called absolute references in cells. Without the addition of this key, cell values will always be relative.
In other words, adding the $ when describing a cell means that no matter what cell you copy the formula to, it will always refer back to that cell containing the $.
For example, if you have a table that you want to create subtotals for, you may add the cells with A2+A3+A4. Then if you copy this formula to a new column, say column B, it will automatically change to B2+B3+B4.
But what if you then want to add a new value to each of those subtotals?
For example, you may have a calculated tax that needs to be added to each subtotal. Say this tax rate value is listed way over in cell E3. So, to create totals for columns A and B, you would start with column A. You would add A2+A3+A4+$E$3. There are those $$ keys. Now, when you copy this new formula to column B, it will automatically change to B2+B3+B4+$E$3. Notice that the E cell did not change, but the other cells did - that's the magic of $!
Excel 2010 has lots of features and shortcuts that make creating spreadsheets a breeze!
Do you know any tricks about Excel 2010 that you would like to share? Do you have questions about Excel 2010? Comment below with your thoughts!
Are you interested in learning more about Excel 2010? Do you wish you had the skills to create advanced level, professional spreadsheets?