A blog about Excel and its users
Applying borders to specific sides of a cell can be a very easy way to spruce up an area of an Excel spreadsheet. Borders help separate cells from each other, making it easier to differentiate one from another.
Perhaps the greatest function of borders is that they show up when you print your Excel spreadsheet, something the grid lines don’t do by default.
To get started select a cell, or a range of cells, which you want to apply a border to.
The shortcuts below are side specific, meaning you can only apply a border to 1 side at a time.
For this example I am applying a border to the bottom of the selected cells.
Next I am going to apply another border to the right of the cells to effectively block them off.
Let’s get even fancier - let’s extend both borders one cell and sum them all up:
If you ever want to remove a border you can execute the shortcut to apply a border to that specific side and it will be removed, or you can execute the Remove Borders shortcut to remove any border touching the cell.
The Remove Borders shortcut is:
And there you have it, the simplicity, and complexity, of borders in Microsoft Excel for Mac.XXXXX
This has to be the coolest shortcut for us Microsoft Excel nerds that happen to categorize or use the same labels for multiple cells. What does this shortcut do? It displays the AutoComplete list for your Excel spreadsheet.
I use this often when I’ve been adding comments next to cells and have created a pretty substantial AutoComplete list. I remember one spreadsheet where I had to manually verify how a list of invoices were paid. Options included everything from “Credited” to “Open” to payment methods like “Check” or “Wire”. What is cool about the Excel AutoComplete feature is I only had to type Cr for Credited to autocomplete and just a W for Wire to autocomplete.
Anyways! This shortcut basically drops down a list of all the saved phrases that have been added to the AutoComplete list in your Excel spreadsheet. This way you can see every comment you have entered more than once.
Step 1: You must have comments or duplicates in your spreadsheet for Microsoft Excel to recognize an autocomplete list is needed. Once you have built an autocomplete list (you will know when this is done if Excel tries to autocomplete cells for you) click on a cell that holds an autocomplete phrase.
I keep track of when I write up the blog posts for shortcuts so Excel has started to autocomplete the dates for me. For this shortcut to work a cell with an auto-completed entry must be active.
Step 2: Execute the shortcut:
Now the Autocomplete list will drop down and you can select an option, or just view what has been added to the list.
My autocomplete isn’t too interesting as you can see, but some lists can become rather large when you are dealing with multiple comments. This is a great way of keeping track of duplicate comments and such.
The title seems daunting, but this shortcut is in my Top 5 favorite Microsoft Excel shortcuts. What it does is scrolls you to the last cell with data in the row or column that you’re in. Gone are the days of scrolling around your spreadsheet trying to find that last cell in the column. Now you can execute this shortcut and be zoomed right to the end.
For this shortcut you will need data in neighboring cells as seen below:
Execute the shortcut. In this instance I will execute the shortcut with the Up arrow to bring me to the cell with 1 in it, but it can be executed in any direction.
This shortcut is absolutely amazing for moving around your Microsoft Excel spreadsheet quickly and accurately. You can jump from data groups to data groups as long as there is a space in between the 2 to stop you. Give it a try, it’s extremely easy to get use to!
The VLOOKUP function, when mastered, is one of the most useful functions in Microsoft Excel. A VLOOKUP is a function that works off the first column in a list of data. When would you use a VLOOKUP? When you are trying to pull specific data from a list into another cell. For our example we will use an Invoice List:
For a VLOOKUP to work you must have a unique identifier and that unique identifier must be in the first column of your list. In this example our unique identifier is the Invoice Number. Once the VLOOKUP is executed if we put XL_SKIN2013 in a cell with the function it will return to us all the information we want. Please note the VLOOKUP function has no restrictions whether you want to pull information into the same spreadsheet, same workbook, or different workbooks.
To start, we put our Unique Identifier into a new cell. This will be the new list where we want to pull this information into.
The cell next to XL_SKIN2015 will be where we enter the VLOOKUP formula. Refer back to the Formula Builder Shortcut to open the Formula Builder.
Type VLOOKUP in the Search for a Function and double click VLOOKUP to start the function:
The formula builder will ask you for lookup_value, table_array, col_index_num, and range_lookup(optional). For these values insert:
Press Enter and the information should appear in the VLOOKUP cell!
If you would like to add more information to the new list simply drag the VLOOKUP down and keep adding Invoice Numbers. The information will automatically populate:
Today you’ll learn the shortcut that repeats the last Find action without opening up the Find Dialog Box. This shortcut saves you time and keeps you in your workbook. It’s especially useful when you are fine tuning and editing a bunch of data.
Example: You’ve just followed the “How to find things in a Microsoft Excel Spreadsheet” blog post and the last thing you searched for was your name. The search comes up empty and you continue with your work. At the end of your work session you want to search one more time for your name, just to ensure it is nowhere to be found in your workbook. Instead of opening up the Find Dialog Box all you have to do is execute a one-modifier key shortcut.
The Find Next shortcut:
As you keep executing the shortcut Excel keeps cycling through the workbook to find all instances of the last searched text. If there are no instances of the last searched text this dialog box will pop up telling you so.
And there you have it. The simple Find Next shortcut that can save you time by not opening the Find dialog box, and by not having to type what you are searching for over again.
Microsoft Excel for Mac doesn’t automatically underline incorrect spelling. This is because formulas are usually abbreviations of words, which would cause almost every cell with a formula to be underlined. There are times that spell check is very necessary though. Note: Excel will not correct grammar like Word, but it will find those misspelt words.
When your Excel spreadsheet is going to be in a presentation or handed into a professor it’s best to do a spell check on the entire workbook just incase.
The shortcut to spellcheck is a simple one modifier key shortcut.
With any cell selected press fn + F7:
This will open the spell check dialog box. From here you can review all spelling mistakes in the worksheet and correct them if needed.
If the correction is not needed click Ignore to move on to the next incorrect word.
Click on one of the Suggestions and then Change to change the selected word to the Suggestion
Click Add to add the word Excel marked as misspelt to the dictionary so it does not select the word as incorrect in the future.
Ignore All will skip over every instance of the misspelt word in your worksheet. For example, if I clicked Ignore All on the dialog box above all cells with asdf would be skipped.
Change All will change every instance of the misspelt word to the word chosen in the Suggestions box.
Has your Standard Toolbar ever disappeared in Microsoft Excel for Mac? Have no fear, there’s a shortcut to get it back!
Toggling the Standard Toolbar on the top of the Microsoft Excel window is a simple one modifier key shortcut. Below is a snapshot of the Standard Toolbar including some other tools I added myself. When we execute the shortcut the upper most portion of this screen will toggle to disappear.
And poof! It’s gone.
Execute the shortcut again and it reappears:
And that’s it! If you ever find yourself missing the Standard Toolbar simply execute this shortcut and it will reappear.X
This week we are sharing a useful resource from our friends at GraduateDegreeProgram.net. They put together an "Excel Cheat Sheet" that goes over everything from the Basics to VLOOKUPs. It's definitely worth checking out!