Let’s all come together and face it: spreadsheets can be scary. Sure, adding borders and painting cells different colors is easy, and maybe you even know how to use basic functions like SUM and AVG. Amazing.
Still, everyone needs to start somewhere, so here are three basic spreadsheet tricks for beginners, whether you prefer Microsoft’s suite or Google’s collaborative nature.
Should I use Excel or Google Sheets?
If you’ve lost yourself in the depths of TikTok, you’ve probably seen Miss Excel—real name Kat Norton—dancing in front of an Excel spreadsheet, teaching users how to freeze columns while busting moves to Vanilla Ice’s “Ice Ice Baby.” Even if you’ve never cared about TikTok, know that her spreadsheet skills are formidable.
“One of the biggest things that I see is people just getting overwhelmed,” Norton says about teaching viewers how to use the full power of spreadsheets. “That’s where I try to come and teach people Excel is a tool to help you.”
[Related: Budgeting is tedious. These tricks make it easier.]
If you’re a beginner and you don’t have access to Microsoft Office, Google Sheets is a perfectly capable substitute. Both are similar platforms, and Norton says the choice mostly comes down to preference and availability. But if you’re handling a lot of data, Miss Excel has an unsurprising preference: “Excel is a lot stronger in terms of data analysis.”
How to use VLOOKUP
Norton says VLOOKUP is one of the most common and useful functions you’ll find in spreadsheet programs. It helps you find something within a column by using a reference—think about it like the Find shortcut (Ctrl/Cmd + F) on steroids. For example, if you have a sheet with names, phone numbers, addresses, zip codes, cities, and emails in separate columns, you can use this function to quickly locate specific information about a person by using their name as a reference.
To use it, enter the function =VLOOKUP into the cell where you want to display your result. It looks like this:
=VLOOKUP(search_key, range, index, [is_sorted])
In both Excel and Google Sheets, you’ll see the parameters or arguments the platform needs to get your result. In this case, there are four of them:
Search_key
This is your reference. Here you can use the value of the cell (the number in it), the string of text within that cell (which you’ll always need to put inside quotation marks in a function), or the cell number (the letter of the column your reference is in, followed by the row number). In this example, it’d be the name of the person whose information you’re looking for—”jane smith”.
Range
This is the group of cells VLOOKUP will search. It must include the column where your search_key is (the name column) and any columns where results might be. You can add this parameter by highlighting the columns with your mouse or trackpad, or indicating the number of the top-left cell and the bottom-right cell of that selection with a colon in between. If your range includes the first 20 rows of the first three columns, then your range would be A1:C20.
Index
This is the number of the column where your result is. In Excel and Google Sheets, columns are identified by letters, but in this case, that letter is translated into a number where A is 1, B is 2, and so on.
(Optional) is_sorted
This is the easiest parameter, as you only have two options: TRUE or FALSE. On Google Sheets, the latter is the default, and it means the platform will search for an exact match of the search_key you entered. If you use TRUE, the platform will also deliver results from similar search_keys. This comes in handy when you have an address book where there might be several entries per person and there’s a chance some of them may be spelled differently, say “Jayne Smith” or “Jane Smit”.
- Pro tip: All parameters in brackets are optional. This means that you can leave these fields blank and the function will still pull up results.
- Note: The one major caveat of this tool is that you cannot look for anything to the left of where the search_key is. This means that if your search_key is in column D (or 4), your index cannot be 2 or 3.
Once you’ve filled out all of your parameters, close the parentheses and hit return to pull up your result. Voilá! You’ve just used the =VLOOKUP function.
How to copy and paste selected columns
If you have a large spreadsheet with a lot of columns, you may only want to select a certain number of them and paste them into a sheet so it’s easier to read. You can go at it the slow way: select and copy each column, go to the new spreadsheet, paste the copied data, and repeat the process several times until you have all the information you need. You can also do this the speedy way.
Start by hiding the columns you don’t want to paste. On Google Sheets, you can right-click on each one and choose Hide column. With Excel, you can do the same, or select the columns you want to hide by clicking on them while pressing Control on a PC or Command on Mac, and then hitting the shortcut Ctrl + 0.
[Related: Boost your productivity on Google Docs and Sheets using scripts and macros]
After this, you’ll see the data as you want to paste it. What happens if you try to copy it will depend on the platform you’re on. On Google Sheets, you can copy and paste the table into a new spreadsheet, and it’ll transfer as is—hidden columns included, but still hidden. But on Excel, you’ll see the program will copy and display hidden columns—because even though you can’t see them, they’re still there.
To prevent this, select the table, navigate to Excel’s Home menu, go to Find & Select, and then choose Go To Special. In the emerging popup window, check the circle next to Visible cells only and click OK. Now you can copy the table excluding hidden columns and paste it wherever you want.
How to separate phrases into single-word cells
If you’ve ever had to organize a contact sheet, you’ve probably struggled with names. Having first and last names in different columns makes it easier to sort your contacts alphabetically. But if you’re dealing with a file that already has a bunch of cells with people’s full names, it’s likely you’ve thought copying and pasting every single one is the only option. It’s not.
On Excel, insert a column to the right of the one with full names—this is where the last names will be. Then, select the full names column, and in the Data menu, click on Text to columns. In the popup window, select Delimited and click Next. Choose what you will use as the separator for the words you want to split: a space, a comma, or a semicolon. Just select the box next to the one you want. If the character you want to use is not listed, select the circle next to Other and copy the character in the field next to it. Click Finish and enjoy your neatly organized contact list.
It’s even easier with Google Sheets. Select the column with the text you want to split, then go to Data, and choose Split text to columns. A tiny popup window will appear asking what the platform should use as a separator—you can use Google’s computing power by choosing Detect Automatically, or you can do as in Excel and choose a separator from the menu or type in a custom one. Once you select it, the spreadsheet will automatically adapt to the new columns and your text will be split up into different columns.
