Three spreadsheet tips to make beginners feel like pros

Asia's Tech News Daily

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.

© cottonbro / Pexels Now you can rightly add “proficient in Excel” in your resume.

But the reality is that you have to really know what you’re doing to unlock the true power of platforms like Microsoft Excel or Google Sheets. The road is long, and constant upgrades will require you to stay up to date with new tools and functions. 

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. 

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.”

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: 

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 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. 

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. 

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. 

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. 

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.  

Internet Explorer Channel Network
Asia's Tech News Daily
News Related

OTHER NEWS

Microsoft and Samsung may team up for a new product

Microsoft and Samsung have a rather close association. The South Korean tech giant’s smartphones offer generous support to Microsoft’s apps. There’s better integration between Windows PCs and Samsung’s mobile phones. ... Read more »

Working with tables in Google Docs become easier with these updates

Google is rolling out new updates to Docs that makes it easier to create and edit inline tables. As reported by 9to5Google, the tech giant has added “several new ways ... Read more »

8 aluminum alloy makers fined W21b for price fixing

The exterior of the Korea Fair Trade Commission in Sejong (Yonhap)South Korea’s antitrust regulator said Wednesday it has decided to impose a combined 20.7 billion won ($17.6 million) in fines ... Read more »

SK Gas invests in US hydrogen startup in push for clean energy transition

Officials from SK Gas Co. and US hydrogen startup C-Zero hold a virtual ceremony on their partnership in this photo provided by the South Korean company on Wednesday. (Yonhap)SK Gas ... Read more »

Tesla replacing faulty autopilot cameras in some cars: Report

Elon Musk-owned electric car maker Tesla is reportedly replacing repeater cameras in the front fenders of at least several hundred Model S, X and 3 vehicles made in Fremont, California, ... Read more »

Global report questions Byju's meteoric rise in India

With more than six million paying users, and a 85 per cent renewal rate, edtech unicorn BYJU‘s has had a glorious run in India, particularly since the Covid-19 pandemic began. ... Read more »

Drone avionics, home solution for blood cell counting tech win Swadeshi Microprocessor Challenge

An integrated avionics system for drones, home solution for blood cell counting, and FM RDS Utilities are some of the most innovative technologies produced by Indian start-ups and students this ... Read more »

Whoops and grunts: ‘bizarre' fish songs raise hopes for coral reef recovery

From whoops to purrs, snaps to grunts, and foghorns to laughs, a cacophony of bizarre fish songs have shown that a coral reef in Indonesia has returned rapidly to health. ... Read more »

Volvo Cars' is fast tracking its goal of climate neutral manufacturing

When it comes to climate neutral manufacturing, Volvo Cars appears to have had a head start. As early as 2008, the brand ensured that all of its European plants ran ... Read more »

Korea H2 Alliance attracts $1 billion investment for hydrogen business

Kim Min, center, the chairman of Korea H2 Alliance and Kim Si-eun, fifth from left, the CEO of Korea H2 Alliance, pose during a virtual investment agreement ceremony with officials ... Read more »

Three weeks with VALORANT's new agent Chamber

Three weeks in, is Chamber any good? (Composite: Yahoo Gaming SEA, Image: Riot Games) The French agent Chamber is the latest addition to the VALORANT roster, released on 16 November ... Read more »

The best hacks for de-icing your car, fast

Have we been de-icing our cars all wrong? (Getty Images) Yahoo Life’s editors are committed to independently selecting wonderful products at great prices for you. We may receive a share ... Read more »

Instagram users can now customise text and colour of Link Stickers

In October this year, Instagram announced that it is expanding the ability to add links in Stories to all accounts. The feature was previously available for verified accounts and accounts ... Read more »

Amazon app quiz December 8, 2021: Get answers to these five questions to win Rs 25,000 in Amazon Pay balance

Win Rs 25,000 in your Amazon Pay balance today! You just have to answer 5 questions and a bit of luck. Amazon is back with its daily dose of app ... Read more »

Garena Free Fire Redeem Codes for December 8: How to redeem daily codes

Garena Free Fire recently gained popularity in the country as a multiplayer battle royale game. This game is developed by 111 Dots Studio where 50 players play 10 minute rounds. ... Read more »

8 aluminum alloy makers fined 21 bln won for price fixing

SEOUL, Dec. 8 (Yonhap) — South Korea’s antitrust regulator said Wednesday it has decided to impose a combined 20.7 billion won (US$17.6 million) in fines on eight makers of aluminum ... Read more »

Hawaii recoups from big storm amid lingering flood threats

The Associated PressPeople hold umbrellas as it begins to rain on an otherwise empty beach in Honolulu on Monday, Dec. 6, 2021. A strong storm packing high winds and extremely ... Read more »

Snap announces new features of Lenses, Spectacles and new monetisation opportunities for creators

Snap has announced new features for Lenses, monetisation tools for AR creators and new features for Spectacles during the ongoing Lens Fest. The company has launched new features for Lenses, ... Read more »

2022 economic policy centers on support for merchants, economic recovery: minister

SEOUL, Dec. 8 (Yonhap) — South Korea’s top economic policymaker said Wednesday the 2022 economic policy will be focused on supporting pandemic-hit small merchants and helping the country’s economy pull ... Read more »

Hyundai Doosan Infracore wins preorders for 100 excavators from N. America

By Nam Kwang-sik SEOUL, Dec. 8 (Yonhap) — Construction machinery manufacturer Hyundai Doosan Infracore Co. said Wednesday that it has won preorders for 100 excavators from North America. Under the ... Read more »

The most searched memes of 2021 revealed - with Matt Hancock topping the list

Google has revealed the top 10 memes people were searching for this year. Read more »

Here's how burnout can put the security of your organization at risk

Perhaps the allure of working from home has worn off or employees are just finding themselves in a slump but across industries, workers are now reporting extraordinarily high burnout which ... Read more »

Hands on: Amazon Echo Show 15 review

The Amazon Echo Show lineup has always held a key spot in the house – each model is both the brains of Alexa and the nervous center of the smart ... Read more »

S. Korea seeks Russia's cooperation on stable supply of urea and grain amid export quota

SEOUL, Dec. 8 (Yonhap) — South Korea has asked for Russia’s cooperation to ensure the stable supply of urea, grain and other major items amid Russia’s export quotas, Seoul’s finance ... Read more »

Notepad gets a new design in Windows 11, including a dark mode

Notepad is one of the most basic apps in Windows, but that’s exactly why so many people still love it: it’s a barebones text editor free of distractions. Still, I’ve ... Read more »

UK to phase out 2G and 3G mobile services by 2033

The government says setting a date for the end of 2G and 3G will make it easier for new equipment makers to enter the market, because they will not have to support the older technologies. Read more »

Posco International sets up holding company in Singapore to bolster palm oil biz

This undated photo, provided by Posco International Corp. on Wednesday shows its palm oil plantation in Indonesia. (Posco)Posco International Corp., the trading arm of South Korean steel giant Posco, said ... Read more »

Omicron significantly reduces Covid antibodies generated by Pfizer vaccine, study finds

Health workers giving out Covid vaccines in Gaza on Tuesday – BLOOMBERG People who have previously had Covid or been vaccinated have far less protection against omicron than they do ... Read more »

Patients ‘more likely to discuss some health conditions with AI than a GP'

Patients are more likely to discuss stigmatising health conditions such as sexually transmitted infections (STIs) with an AI chatbot than a GP, new research suggests. A study by the University ... Read more »

UK to switch off 2G and 3G by 2033 to boost UK telecoms networks

2G and 3G mobile networks are to be phased out of use in the UK by 2033 as part of Government measures to increase the security of telecoms supply chains, ... Read more »

Pfizer vaccine ‘may be up to 40 times less effective' against omicron variant, early study suggests

(Independent) The neutralising antibody response generated by the Pfizer vaccine may be 41 times less effective against the omicron variant than the initial Covid virus type, an early study has ... Read more »

M&As by large companies jump this year

SEOUL, Dec. 8 (Yonhap) — Corporate mergers and acquisitions (M&As) by big South Korean firms rose sharply in the first nine months of this year as they revved up efforts ... Read more »

COVID-19: Antibodies from Pfizer vaccine may be up to 40 times less effective against Omicron, first lab tests suggest

The Pfizer vaccine may be up to 40 times less effective against Omicron than the original COVID strain, the first lab tests on the new variant in South Africa have ... Read more »

Neuralink and Tesla have an AI problem that Elon's money can't solve

Elon Musk’s problems are bigger and more important than yours. While most of us are consumed with our day-to-day activities, Musk has been anointed by a higher power to save ... Read more »

Nvidia says more graphics cards will make it to gamers in 2022, but should we believe it?

Nvidia believes that supply of its hard to find graphics cards will improve by the second half of 2022, yet another glimmer of hope in a year full of false ... Read more »

Omicron variant may be a 'live Covid vaccine', claims Vladimir Putin

Vladimir Putin – Mikhail Metzel, Sputnik, Kremlin Pool Photo via AP Vladimir Putin has compared omicron to a “live vaccine”, downplaying the potential risks posed by the variant which has ... Read more »

12-year-old dies by suicide after being told he'd go to hell for being gay, parents say

A 12-year-old boy has died by suicide after being bullied for being gay, his parents have said. Eli Fritchley, a seventh-grade student at Cascades Middle school in Shelbyville, south of ... Read more »

Vistaprint is offering up to 60% off almost everything—save on your holiday cards and more

— Recommendations are independently chosen by Reviewed’s editors. Purchases you make through our links may earn us a commission. The countdown to Christmas is upon us, folks, with T-minus three ... Read more »

Don't let scammers ruin your holiday: Here's how to stay safe while shopping online

Yahoo Life is committed to finding you the best products at the best prices. Some of the products written about here are offered in affiliation with Yahoo. We may receive ... Read more »

Internet interrupted: Amazon Web Services outage cripples airlines, banks, streaming services

According to reports, the Amazon outage has impacted Google, Spotify and multiple airlines and banks. Read more »
On free-english-test.com you will find lots of free English exam practice materials to help you improve your English skills: grammar, listening, reading, writing, ielts, toeic