When you want to make a note or add a comment to a specific cell within a worksheet, simply right-click the cell you want to comment on, then click Insert Comment. Type your comment into the text box, and click outside the comment box to save it. Cells that contain comments display a small, red triangle in the corner. To view the comment, hover over it.
If you've got a ton of different sheets in one workbook -- which happens to the best of us -- make it easier to identify where you need to go by color-coding the tabs. For example, you might label last month's marketing reports with red, and this month's with orange. Simply right click a tab and select "Tab Color." A popup will appear that allows you to choose a color from an existing theme, or customize one to meet your needs.
Whether you're logging social media posts, or keeping track of tasks you're checking off your to-do list, you might want to add a date and time stamp to your worksheet. Start by selecting the cell to which you want to add this information. Then, depending on what you want to insert, do one of the following:
Have raw data that you want to turn into currency? Whether it be salary figures, marketing budgets, or ticket sales for an event, the solution is simple. Just highlight the cells you wish to reformat, and select Control + Shift + $. The numbers will automatically translate into dollar amounts -- complete with dollar signs, commas, and decimal points. Note: This shortcut also works with percentages. If you want to label a column of numerical values as "percent" figures, replace "$" with "%".
Need to open, close, or create a workbook on the fly? The following keyboard shortcuts will enable you to complete any of the above actions in less than a minute's time.
Perhaps you're crunched for time. I mean, who isn't? No time, no problem. You can select your entire spreadsheet in just one click. All you have to do is simply click the tab in the top-left corner of your sheet to highlight everything all at once. Just want to select everything in a particular column of row? That's just as easy with these shortcuts:
There's a great article that likens Excel's RANDOMIZE formula to shuffling a deck of cards. The entire deck is a column, and each card -- 52 in a deck -- is a row. "To shuffle the deck," writes Steve McDonnell, "you can compute a new column of data, populate each cell in the column with a random number, and sort the workbook based on the random number field." In marketing, you might use this feature when you want to assign a random number to a list of contacts -- like if you wanted to experiment with a new email campaign and had to use blind criteria to select who would receive it. By assigning numbers to said contacts, you could apply the rule, “Any contact with a figure of 6 or above will be added to the new campaign.”
Start with a single column of contacts. Then, in the column adjacent to it, type “RAND()” -- without the quotation marks -- starting with the top contact’s row.
RANDBETWEEN allows you to dictate the range of numbers that you want to be assigned. In the case of this example, I wanted to use one through 10. bottom: The lowest number in the range. top: The highest number in the range,
This one is an oldie, but a goodie -- and it's a bit more in depth than some of the other formulas we've listed here. But it's especially helpful for those times when you have two sets of data on two different spreadsheets, and want to combine them into a single spreadsheet. My colleague, Rachel Sprung -- whose "How to Use Excel" tutorial is a must-read for anyone who wants to learn -- uses a list of names, email addresses, and companies as an example. If you have a list of people's names next to their email addresses in one spreadsheet, and a list of those same people's email addresses next to their company names in the other, but you want the names, email addresses, and company names of those people to appear in one place -- that's where VLOOKUP comes in. Note: When using this formula, you must be certain that at least one column appears identically in both spreadsheets. Scour your data sets to make sure the column of data you're using to combine your information is exactly the same, including no extra spaces.
Lookup Value: The identical value you have in both spreadsheets. Choose the first value in your first spreadsheet. In Sprung's example that follows, this means the first email address on the list, or cell 2 (C2). Table Array: The range of columns on Sheet 2 you're going to pull your data from, including the column of data identical to your lookup value (in our example, email addresses) in Sheet 1 as well as the column of data you're trying to copy to Sheet 1. In our example, this is "Sheet2!A:B." "A" means Column A in Sheet 2, which is the column in Sheet 2 where the data identical to our lookup value (email) in Sheet 1 is listed. The "B" means Column B, which contains the information that's only available in Sheet 2 that you want to translate to Sheet 1. Column Number: The table array tells Excel where (which column) the new data you want to copy to Sheet 1 is located. In our example, this would be the "House" column, the second one in our table array, making it column number 2. Range Lookup: Use FALSE to ensure you pull in only exact value matches.
The TRIM formula in Excel is denoted =TRIM(text). This formula will remove any spaces entered before and after the text entered in the cell. For example, if A2 includes the name " Steve Peterson" with unwanted spaces before the first name, =TRIM(A2) would return "Steve Peterson" with no spaces in a new cell. Email and file sharing are wonderful tools in today's workplace. That is, until one of your colleagues sends you a worksheet with some really funky spacing. Not only can those rogue spaces make it difficult to search for data, but they also affect the results when you try to add up columns of numbers. Rather than painstakingly removing and adding spaces as needed, you can clean up any irregular spacing using the TRIM function, which is used to remove extra spaces from data (except for single spaces between words).
Text: The text or cell from which you want to remove spaces. Here's an example of how we used the TRIM function to remove extra spaces before a list of names. To do so, we entered =TRIM("A2") into the Formula Bar, and replicated this for each name below it in a new column next to the column with unwanted spaces. Below are some other Excel formulas you might find useful as your data management needs grow.
INDIRECT makes it easy to set up tables which reference larger tables without a lot of referencing work or cutting and pasting; especially for dynamic spreadsheets.