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.”
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.”
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.
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”.
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.
This is the easiest parameter, as you only have two options: TRUE or FALSE. 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.
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.
Source : https://www.popsci.com/diy/excel-google-sheets-beginner-tricks/
