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 365 office suite, Excel remains the premier application for making and managing spreadsheets. If you’re new to Microsoft 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.
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 Windows keyboard shortcut — Ctrl + A in or the MacOS keyboard shortcut Command + A — 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.
In the Style drop-down menu, choose Classic.
Afterward, select Use a formula to determine which cells to format.
The formula to enter is “=MOD(ROW(),2).”
Choose which fill color you want, then click OK. Now the rows should be shaded in alternating color.
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 hide a column in Excel. Just right-click it, then select Hide.
The column or row will be noted by a thick border between the adjacent columns or rows.
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.
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.
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.
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).
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).” Check our full guide on how to use VLOOKUP in Excel.
How to create a drop-down list
If you want to restrict the range of options a user can put into a cell, creating a drop-down list 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.
Under Settings, find the drop-down menu labeled Allow. Then, select List.
Now, highlight the the cells you want users to choose from and click OK.
Now, when users click on the cell, they can choose options from a drop-down menu.
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.”
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.
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).”
To apply this formula to all the rows we need, click the blue box in the corner of the cell and drag down.
Excel will automatically copy the formula and fill in appropriately. Using concatenate in Excel has more complex options if you need them.
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.
Under the Alignment tab, check the box beside Wrap text.
Adjust the width of the cell to your liking.
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.
Next, click the Ribbon button.
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.
Now you should see the Devolper ribbon at the top.
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.
