Master the way of the spreadsheet with these Excel tips and tricks

Excel tips and tricks person using computer
Nonwarit Pruetisirirot/123RF
Who doesn’t love spreadsheets? Probably a lot of people, actually, but even if the thought of filling rows and columns with various data points doesn’t get your pulse rising, there are a number of industries where spreadsheets are a necessity. However, though there are plenty of third-party alternatives to Microsoft’s iconic office suite, Excel remains the premier application for making and managing spreadsheets. If you’re new to Excel — or an old hand who wants to plumb Excel’s depths — our Excel tips and tricks are sure to help you increase your productivity. Read on for all the details.

If you’re looking for additional Excel pointers, we’ve put together  guides on how to password protect an Excel document and how to make a graph in Excel.

Select All

microsoft excel tips and tricks how to select all

If you want to highlight a specific grid of cells, you can simply click and drag, but what if you want to select all of them? There are two ways. You can use the keyboard shortcut — Ctrl + “A” in Windows 10, Command + “A” in MacOS High Sierra and earlier iteration of MacOS — or go to the smaller cell in the upper-left corner (marked by a white arrow) and click it.

How to shade every other row

Spreadsheets can be awfully drab, and if you’ve got a lot of data to look at, the reader’s eyes might start to drift aimlessly over the page. Adding a splash of color, however, can make a spreadsheet more interesting and easier to read, so try shading every other row.

To start, highlight the area you want to apply the effect to. If you want to add color to the entire spreadsheet, just select all. While viewing the Home tab, click the Conditional Formatting button. Then, select New Rule from the resulting drop-down menu.

microsoft excel tips and tricks how to shade alt rows step 1

In the Style drop-down menu, choose excel tips and tricks how to shade alt rows step 2

Afterward, select Use a formula to determine which cells to format.

microsoft excel tips and tricks how to shade alt rows step 3

The formula to enter is “=MOD(ROW(),2).”

microsoft excel tips and tricks how to shade alt rows step 4

Choose which fill color you want, then click OK. Now the rows should be shaded in alternating color.

microsoft excel tips and tricks how to shade alt rows step 5

Hide/unhide rows

Sometimes, you may want to hide some rows or columns of data. This can be handy if you want to print copies for a presentation, for instance, but the audience only needs to see the essentials. Thankfully, it’s easy to hide a row or column in Excel. Just right-click it, then select Hide.

microsoft excel tips and tricks how to hide unhide rows step 1

The column or row will be noted by a thick border between the adjacent columns or rows.

microsoft excel tips and tricks how to hide unhide rows step 2

If you want to unhide a specific row or column, you will need to highlight the columns or rows on either side of it. Once done, right-click and select Unhide from the resulting list of options.

microsoft excel tips and tricks how to hide unhide rows tep 3

If you have hidden multiple rows and/or columns, you can quickly unhide them by selecting the rows or columns in question, then right-clicking and selecting Unhide. Note: If you’re trying to unhide both rows and columns, you will need to unhide one axis at a time.

microsoft excel tips and tricks how to hide unhide rows step 4

How to use vlookup

Sometimes, you may want to ability to retrieve information from a particular cell. Say, for example, you have an inventory for a store, and want to check the price of a particular item. Luckily, you can do so using Excel’s vlookup function.

microsoft excel tips and tricks how to vlookup 1

In this example, we have an inventory where every item has an ID number and a price. We want to create a function where users can punch in the ID and get the price automatically. The vlookup function does this, letting you specify a range of columns containing relevant data, a specific column to pull the output from, and a cell to deliver the output to.

We’re going to write the vlookup function in cell I4; this is where the data will be shown. We use cell I3 as the place to input the data, and tell the function that the relevant table runs from B2 to D11, and that the answers will be in the third column (vlookup reads from left to right).

microsoft excel tips and tricks how to vlookup 2

So, our function will be formatted like so: “=vlookup (input cell, range of relevant cells, column to pull answers from),” or “=vlookup(I3,B2:D11,3).”

How to create a drop-down list

If you want to restrict the range of options a user can put into a cell, a drop-down menu is a good solution. Thankfully, you can easily create one that offers users a list of options to choose from. To start, choose a cell, then go to the Data tab and select Validate.

microsoft excel tips and tricks drop down 1

Under Settings, find the drop-down menu labeled Allow. Then, select List.

microsoft excel tips and tricks drop down 2

Now, highlight the the cells you want users to choose from and click OK.

microsoft excel tips and tricks drop down step 3

Now, when users click on the cell, they can choose options from a drop-down menu.

microsoft excel tips and tricks drop down 4

Keep your zeroes visible

Sometimes, you may want to enter strings of numbers beginning with one or more zeroes. Unfortunately, Excel may not show these zeroes, by default. To fix this, add a single quotation mark before the zeroes, e.g. “‘00001” instead of “00001.”

microsoft excel tips and tricks preface your zeroes

How to concatenate

Sometimes, you may want to reorganize data and combine info from different cells into one entry. You can do this using the concatenate function.

In this example, we have a basketball fantasy league roster, with fields for players’ last names, first names, and positions. If we want to combine all that information in one field, we will need a formula that reads “=concat(first cell, second cell, third cell),” or, in this case, “=concat(C6,B6,D6)” without the quotes.

microsoft excel tips and tricks concat 1

Unfortunately, concatenate doesn’t automatically put spaces between text from different cells. To make a space appear, you will need to add quotation marks with a space in between. Now the function should read “=concat(C6,” “,B6,” “,D6).”

microsoft excel tips and tricks concat 2

To apply this formula to all the rows we need, click the blue box in the corner of the cell and drag down.

microsoft excel tips and tricks concat 3

Excel will automatically copy the formula and fill in appropriately.

How to wrap text

Got a lot of text in a single cell? It will probably spill into other cells, which might not look as nice as you’d like. Thankfully, it is easy to make text wrap within a cell.

Start by selecting the cell with the excessive text in it. Right-click and select Format cells.

microsoft excel tips and tricks wrap text 1

Under the Alignment tab, check the box beside Wrap text.

microsoft excel tips and tricks wrap text 2

Adjust the width of the cell to your liking.

microsoft excel tips and tricks wrap text 3

How to see the developer tab

If you want to do more advanced work in Excel — such as create macros — you will need to access the Developer ribbon. Sadly, this tab is hidden by default. To view it, click Excel in the upper-left corner and select Preferences.

microsoft excel tips and tricks dev ribbon 1

Next, click the Ribbon button.

microsoft excel tips and tricks dev ribbon 2

You should see list of buttons, and checkboxes that determine whether you can see the various components. Here, scroll down to the Developer box and check it.

microsoft excel tips and tricks dev ribbon 3

Now you should see the Devolper ribbon at the top.

microsoft excel tips and tricks dev ribbon 4

Stop your PC's vow of silence with these tips on how to fix audio problems

Sound problems got you down? Don't worry, with a few tweaks and tricks we'll get your sound card functioning as it should, and you listening to your favorite tunes and in-game audio in no time.

How to easily record your laptop screen with apps you already have

Learning how to record your computer screen shouldn't be a challenge. Lucky for you, our comprehensive guide lays out how to do so using a host of methods, including both free and premium utilities, in both MacOS and Windows 10.

Changing file associations in Windows 10 is quick and easy with these steps

Learning how to change file associations can make editing certain file types much quicker than manually selecting your preferred application every time you open them. Just follow these short steps and you'll be on your way in no time.

Change your Outlook password quickly and easily by following these steps

Keeping your digital accounts locked up with strong, unique passwords is important, so learning how to change your Outlook password quickly and easily is one of the best ways to stay safe online.

Is your PC slow? Here's how to restore Windows 10 to factory settings

Computers rarely work as well after they accumulate files and misconfigure settings. Thankfully, with this guide, you'll be able to restore your PC to its original state by learning how to factory reset Windows.

The Titan RTX graphics card is nearly here. Here's what you need to know

The Nvidia Titan RTX is arguably the most powerful consumer graphics card ever made, even if it's not really aimed at consumers. It bridges the 2080 Ti and RTX Quadro cards with boat loads of power.

Leak reveals that Nvidia’s RTX 2060 gaming chipsets will be headed to laptops

The latest leaks of Nvidia's upcoming RTX 2060 have given performance benchmarks and further detail about the future chipset and its capabilities, while a RTX 2060 Max-Q variant has also been discovered for thin and light gaming machines.

Looking for an Apple MacBook below $900? Woot has you covered

If you're looking for a great deal on an Apple MacBook, then Amazon's Woot may just have what you have been seeking. It has Macbooks available for only $810 with Intel M3 CPUs, 8GB of RAM, and 256GB SSDs.

Want to save a webpage as a PDF? Just follow these steps

Need to quickly save and share a webpage? The best way is to learn how to save a webpage as a PDF file, as they're fully featured and can handle images and text with ease. Here's how.

New rumors say the Pixelbook 2 could show up at CES 2019

What will the Pixelbook 2 be like? Google hasn't announced it, but thanks to rumors and leaks, we think we have a pretty good idea of what the potential new flagship Chromebook will be like.

A dead pixel doesn't mean a dead display. Here's how to repair it

Dead pixel got you down? We don't blame you. Check out our guide on how to fix a dead pixel and save yourself that costly screen replacement or an unwanted trip to your local repair shop.

You could spend $1,000 on an iPhone, or buy one of these awesome laptops instead

Finding a decent laptop is easy, but finding one under $1,000 is a bit tricky. Luckily, we've taken some of the guesswork out of picking out a budget laptop. Here are some of our favorites, the best laptops under $1,000.

Don't know what to do with all your old DVDs? Here's how to convert them to MP4

Given today's rapid technological advancements, physical discs are quickly becoming a thing of the past. Check out our guide on how to convert a DVD to MP4, so you can ditch discs for digital files.

Here’s how to install Windows on a Chromebook

If you want to push the functionality of your new Chromebook to another level, and Linux isn't really your deal, you can try installing Windows on a Chromebook. Here's how to do so, just in case you're looking to nab some Windows-only…