3 spreadsheet tips to make beginners feel like pros

Let's be honest: Spreadsheets can be daunting. Of course, it's easy to add borders and paint cells with different colors, maybe you even know how to use basic functions like SUM and AVG. marvelous.

But the reality is that you have to really understand what you are doing to unleash the real power of platforms like Microsoft Excel or Google Sheets. It's a long road and continuous upgrades will require you to stay informed of new tools and features.

Still, everyone needs to start somewhere, so here are three basic spreadsheet tips for beginners, whether you prefer Microsoft's suite or Google's collaborative nature.

Should I use Excel or Google Sheets?

If you're lost in the depths of TikTok, you may have seen Miss Excel (real name Kat Norton) dancing in front of an Excel spreadsheet, teaching users how to freeze columns while playing Vanilla Ice's "Ice Ice Baby". Even if you have never cared about TikTok, know that her spreadsheet skills are very powerful.

"One of the biggest problems I see is people feeling overwhelmed," Norton said as he taught the audience how to use the full functionality of a spreadsheet. "This is where I'm trying to teach people that Excel is a tool to help you."

[Related: The budget is boring. These tips make it easier. ]

If you are a beginner and cannot access Microsoft Office, Google Sheets is a fully competent alternative. Both are similar platforms, Norton says the choice is primarily determined by preference and availability. However, if you work with large amounts of data, Miss Excel has an unsurprising preference: “Excel is much more powerful in data analysis.”

How to use VLOOKUP

Norton said VLOOKUP is one of the most common and useful functions you have in your spreadsheet program. It helps you find something in a column using references—think of it as an enhanced lookup shortcut (Ctrl/Cmd F). For example, if you have a table with a name, phone number, address, postal code, city, and email (in separate columns), you can use this function to quickly find specific information about someone by using the name as a reference .

To use it, enter the function =VLOOKUP in the cell where you want to display the result. It looks like this:

=VLOOKUP(search_key, range, index, [is_sorted])

In Excel and Google Sheets, you will see the parameters or arguments required by the platform to get the results. In this case, there are four:

Search_key

This is your reference. Here you can use the value of the cell (the number in it), the text string inside the cell (you always need to enclose it in quotes in the function), or the cell number (refer to the letters of the column you are in, followed by the row number ). In this example, it will be the name of the individual whose information you are looking for - "jane smith".

Range

This is the cell group that VLOOKUP will search for. It must contain the column (name column) where the search _key is located and any columns that may contain the result. You can add this parameter using the mouse or trackpad highlight columns, or use a colon to indicate the number of the upper left and lower right cells of the selected one in the middle. If your range includes the first 20 rows of the first three columns, your range will be A1:C20.

Index

This is the number of the column where the result is located. In Excel and Google Sheets, the column is identified by a letter, but in this case the letter is converted to a number, where A is 1, B is 2, and so on.

(optional) is_sorted

This is the easiest parameter, because you only have two options: TRUE or FALSE. In Google Sheets, the latter is the default, which means the platform will search for the exact match of the search_key you entered. If you use TRUE, the platform also provides results from search_key. This is useful when you have an address book where multiple entries may belong to the same person and some of them may be spelled differently, such as "Jayne Smith" or "Jane Smit".

• Pro tip: All parameters in square brackets are optional. This means you can leave these fields blank and the function will still display the result.
• Note: A major warning of this tool is that you cannot find anything to the left of search_key. This means that if your search_key is in column D (or 4), your index cannot be 2 or 3.

After filling in all parameters, close the brackets and press Enter to display the results. Look! You just used the =VLOOKUP function.

How to copy and paste selected columns

If you have a large spreadsheet with many columns, you may want to select only a portion of the columns and paste them into a table for easier reading. You can do it slowly: select and copy each column, go to a new spreadsheet, paste the copied data, and repeat the process several times until you have all the information you need. You can do this quickly, too.

First hide the columns you don't want to paste. In Google Sheets, you can right-click on each column and select Hide column . With Excel, you can do the same thing, or select the columns you want to hide by holding down the Control key on your PC or Command key on your Mac while clicking them and pressing the shortcut key Ctrl 0 .

[Related: Use scripts and macros to increase your productivity on Google Docs and Sheets]

After that, you will see the data you want to paste. If you try to copy it, what happens depends on the platform you are using. In Google Sheets, you can copy and paste the table into a new spreadsheet and it will be transferred as is – including hidden columns, but still hidden. But in Excel you see that the program will copy and display hidden columns - because they still exist even if you can't see them.

To prevent this, select the table, navigate to Excel's Start menu, go to Find and select , and then select to go to special . In the newly-appeared pop-up window, select to see only the circle next to cell and click OK . Now you can copy the table that excludes hidden columns and paste it anywhere you want.

How to divide a phrase into single word cells

If you ever had to organize your contact form, you might have already struggled with your name. Putting your first and last name in different columns makes it easier to sort your contacts alphabetically. However, if you're working on a file that already has many cells containing people's full names, you're likely to think copying and pasting each one is the only option. This is not the case.

In Excel, insert a column to the right of the column containing the full name - the last name will be located here. Then, select the column with the full name, and in the Data menu, click Text to Column . In the pop-up window, select delimited and click Next . Select the separator that you will use as the word you want to split: space, comma, or semicolon. Just select the box next to one you want. If the character you want to use is not listed, select the circle next to Other and copy the character into the field next to it. Click to finish and enjoy your neat contact list.

It's easier to use Google Sheets. Select the column to which you want to split the text, then go to Data and select to split the text into column . A small pop-up will appear asking what the platform should use as a separator - you can use Google's computing power by selecting Automatically detect , or you can choose from the menu like in Excel Delimiter or enter a custom delimiter. Once the delimiter is selected, the spreadsheet will automatically adapt to the new columns and your text will be split into different columns.

The above is the detailed content of 3 spreadsheet tips to make beginners feel like pros. For more information, please follow other related articles on the PHP Chinese website!