Top 24 Excel Tips and Tricks – Become a Spreadsheet Master!

Excel, Microsoft’s spreadsheet program, is the business world’s industry standard and contains a treasure trove of handy hacks that boost efficiency, productivity, and the overall potential of your documents. Despite being the world’s premiere spreadsheet application for decades, these Excel tips and tricks are not immediately obvious.

There are plenty of resources like this YouTube tutorial, but scouring the Internet for Excel tips and tricks could be an unending task. To save you the trouble, we’ve rounded up our top Excel spreadsheet tips in one place to get you on your way to being an Excel wizard in no time.

Image by Esa Riutta from Pixabay

What Can Excel Be Used for?

There are about as many uses for Excel as there are capabilities in the program. At its core, Excel is used to create grids of text and numbers, and layer in formulas that allow for the automatic data calculation and analysis.

People use Excel for all kinds of business and personal needs — from tracking finances and planning budgets, to presenting fiscal results, to simply serving as a contact database. With Excel’s powerful programming potential, it can even be used to digest information from external sources and process the data for real-time analysis. Or, used to its full potential, it can serve as a customer-relationship management system. As a student or professional, you’re likely to use Excel at one point or another.

24 Top Excel Tips And Tricks

Now that we’ve got your mind running with the possibilities, here is our list of Excel tips and tricks to make your use of Excel more efficient and productive, no matter what it may be for:

1. One Click To Select All

You’ll often find it handy to be able to select all of the data in your spreadsheet, and clicking each cell one-by-one just won’t cut it when moving and formatting large quantities of data. Instead, either hit Ctrl+A or click the arrow button at the top corner of the spreadsheet to select all in seconds.

2. Add More Than One New Row Or Column

As you build and add to your spreadsheet, it’s essential to be able to add multiple rows or columns at once rather than going the manual route. To start, pick the area where you want to add a new batch of rows or columns, then click and drag the highlight over the number of lines you want to add (e.g., highlight seven rows to add seven). Right-click and choose “Insert” from the drop down menu to insert rows above the row or to the left of the column you selected.

3. Line Breaks With Text

Despite Excel’s many capabilities, the simple task of typing in and formatting spreadsheet cells can be frustrating since text defaults to continuing in one line. Luckily that is easily fixed by holding down Alt+Enter to create a new line as you type. You can also click “Wrap text” in the top menu to have the text automatically wrap at the edge of the cell you’re in, and adjust as you resize the column or row.

4. Ctrl+Shift

Add the Ctrl+Shift function to your arsenal for another way to quickly select large data sets. First click the first cell you want to highlight, then hold down Ctrl+Shift. From there, hit the down arrow on the keyboard to get highlight the data below the cell, the up arrow to highlight above, or the left and right arrows to highlight the rows on either side.

For even larger data selection, hold down Ctrl+Shift+End to make the cursor jump to the lowest right-hand cell with data while selecting everything in between, or hit “Ctrl+Shift+* to highlight the whole data set no matter which cell you clicked first.

5. Auto Fill

Excel has built in features to help users quickly fill in the data itself. The first of the two we’ll cover here is auto fill, which helps you to avoid spending an afternoon typing a repetitive series like a list of dates or numbers into a spreadsheet.

To activate auto fill, begin your series just enough to establish a pattern, then move the cursor to the lower right corner of the last filled out cell. The cursor should automatically turn into a plus sign. Once it does, drag down to select all the cells you need to fill and Excel will do it for you following the pattern you started. This also works if you need to fill up a column or left to right rows.

It’s possible to use auto fill without a pattern. Pick a cell or group of cells, move the cursor to the plus sign position, click, drag, and you’ll be provided a menu of options that allow you to dictate the data that’s auto-filled. The more data you’ve entered, the better tailored the options in the menu.

6. Flash Fill

Similarly, the flash fill feature populates columns based on a pattern of data it sees in the first row. By the second row, Excel should begin to recognize the pattern and will provide a suggestion of data and format to fill out further cells and you only need to hit enter to use it. If it doesn’t give you an accurate range, keep entering data until Excel catches on. Finally, go to the “Data” tab at the top of the spreadsheet and hit “Flash fill” to populate the rest of the sheet.

Flash fill works with values like numbers, names, and dates, and is most efficient when the data is organized by a top header row.

7. Text To Columns

If you’re importing data from another program or re-formatting a spreadsheet, you may encounter a situation where you have a list of data in one column (i.e., first and last names) and need to break it out into two. To do this, select the data, then click “Text to columns” in the “Data” tab. This allows you to swiftly separate the data by delimiters like commas, or fixed width like a number of spaces or a period.

8. Multiple Cells, Same Data

Another handy Excel tip when working with a large set of information allows you to de-duplicate data and ensure you’re left with a unique set of values without repetition. First highlight your list, then choose “Remove duplicates” from the “Data” tab. From the columns you’ve selected, the menu will prompt you to select which columns that you want to remove duplicates from.

9. Paste Special

When you find yourself needing to turn a bunch of rows into columns or vice versa, the paste special function is your best friend. Rather than moving data cell by cell, highlight and copy the data, right click and select “Paste special,” check the “Transpose” box, then click “OK” to paste the data into your desired orientation.

10. Save Charts As Templates

Excel has tons of chart formats to choose from, but sometimes you need an extremely customized one to suit your business or presentation needs. Thankfully, Excel has an equally impressive ability to allow you to customize charts and graphs. To avoid the painful task of recreating your very specific template, right-click on your tweaked and tuned chart and select “Save as template.” Be sure to save a file with a CTRX extension in your default Excel templates folder.

To access your template for later use, select the data you want to chart, go to the “Insert” tab and click “Recommended charts.” Then under the “All charts” tab, locate the “Template” folder, and in the “My Templates” box, pick the one you’d like to apply, then click OK.

Be sure to note that some elements, like the actual text in legends and titles, won’t carry over unless they’re part of the data set, but font and color selections, embedded graphics, and series options will carry over.

11. Work With Cells Across Sheets

When you have multiple sheets in a workbook that all have the same essential layout (i.e., financial statements or invoices), you can use the 3D Sum to work with corresponding cells across different sheets.

Create a new worksheet within your workbook, then pick a cell to type a formula such as = SUM(‘W1:W8’!A3), which will total up cell A3 from sheets labeled between W1 and W8. This helps when you want to create a master spreadsheet that tracks the data in your workbook as it evolves.

12. Pivot!

PivotTables are an extremely helpful Excel feature that provides summaries of large collections of data. To create one for your data, check all of the columns and rows you’d like to include, then select “PivotTable” from the “Insert” tab. You can also use the “Recommended PivotTable” option to let Excel pick the right one for you based on your data, or add a PivotChart to your table that includes a chart to make the analysis easier to understand.

13. Conditional Format

Because Excel is commonly used to store and analyze data, conditional formatting is one of our top Excel tricks as it allows you to quickly identify highlights from a large amount of data. Whether you’re identifying top give values or data highs and lows, conditional formatting can put a border around the highlights you’re seeking or even color code the entire grid.

To enable, click the “Conditional formatting” drop down menu at the top of the document. You can use the Highlighted Cells Rules sub-menu to create more rules to look for more specific identifiers, such as a string of words, recurring dates, or repeating values.

14. Data Validation

When working on a spreadsheet that others will use, data validation is a way to keep things organized with a drop-down menu of selection to use so your colleagues can’t throw off the data set with an incorrect entry. To do so, highlight the cell where you’d like to place the drop-down, go to the “Data” tab and click “Data validation.” Tick “List” where it says “Allow:” and type the options you’d like to include, separated by commas, in the “Source:” field. To keep things extra clear, you can even create an error message other users will see if they try to enter data outside the desired range.

15. Hide A Sheet

It’s common to wind up with a really complex workbook with tons of worksheets lined up in the tab at the bottom of the document. Excel allows you to hide sheets to keep the data and formulas available for other sheets, while keeping the overall document visually streamlined. Simply right-click the tab of the sheet at the bottom of the document, and select “Hide.” To bring it back, visit the “View” tab at the top of the document, select “Unhide,” then pick the name of the hidden tab from the list that pops up.

16. Shift Between Workbooks

When working across multiple workbooks, you’ll be very thankful for this Excel trick that allows you to hop between documents and avoid messing up an entire project by one mis-entry into the wrong workbook. Just hit Ctrl+Tab to quickly and efficiently shift between workbooks.

17. Add Diagonal Line

In some cases, you may need to add a diagonal line to the first cell of a section to separate different attributes of rows and columns. Click “Home,” then “Font,” then “Borders,” and you’ll be met with an array of border options to choose from. Click on through to “More Borders” for an additional menu of alternate options, click the diagonal line, then hit “Save.”

18. Copy And Paste Quickly

Though it seems like a swift action as it is, this Excel tip will help you to transport data around your document faster than ever. To move a whole column of data within a spreadsheet, choose the column, and move the cursor to its border. When the arrow turns into a crossed arrow icon, drag the column to move it wherever you’d like. To copy the data, press Ctrl before dragging to move and the new column will automatically copy all of the selected data.

19. Screenshot Insertion

It’s extremely easy to insert a screenshot from another program into your Excel spreadsheet. Go to the “Insert” tab, select “Screenshot,” and you’ll see a menu of thumbnail images from other open programs. Select the one you want, and resize or crop as you desire within the spreadsheet.

20. Excel Data To Word

Another easy but helpful Excel trick is copying cells or charts into Word. Though a straightforward copy-and-paste maneuver, be sure to note that this “link-and-embed” process — meaning that if you change the data in the spreadsheet — will also change the data in its Word counterpart. If you’d like to avoid this, instead paste the data as a graphic using Word’s “Paste special” feature. You can also select “Copy as picture” within Excel, and paste the static graphic into Word.

21. Input Restriction

Similar to the data validation feature, there’s another way to restrict input values and offer guidance to other users adding to your spreadsheet. To ensure data outside of a given range is barred from your sheet, go to “Data,” then “Data Validation,” then “Setting,” and input your desired conditions. You can also shift to “Input message” and provide prompts that steer users to the correct data range, which will show up when their cursor hovers over the cell and trigger a warning message if the value is unqualified.

22. Quick Analysis

If you need some guidance on what kind of info you’d like to apply to your data, Excel’s “Quick analysis” can provide you with some options, you guessed it, quickly. First select your data and click the quick analysis icon that appears to the bottom right of your selection. You’ll be greeted with a menu that can apply conditional formatting, tally up totals, or even call up charts in one simple click.

23. Hide Data

Whether you’d like to hide a row or column, or inconveniently placed data, but keep it available for reference or formulas, Excel makes it easy. Simply select the cells, row, or column, right click, and select “Format cells.” Then go to the “Number” tab at the top, then “Category,” and select “Custom.” In the “Type:” field, type three semicolons, and click “OK” to hide but keep your data close at-hand.

24. Shortcuts

Some of the best Excel tricks come from the software’s series of simple keyboard shortcuts that allow you to maneuver around the document even more quickly. Here are some of our favorites:

• Ctrl+; – Inserts the date.
• Ctrl+Shift+: – Inserts the current time.
• Ctrl+0 – Hides the selected column.
• Ctrl+9 – Hides the selected row.
• Ctrl+` – Reveals all of the formulas embedded in your spreadsheet.
• Ctrl+PageUp or PageDown – Toggles between sheets in the currently open workbook.
• F2 – Start editing the current selected cell vs. double clicking.
• Shift+F10 – Opens the right-click menu for the selected cell.

Photo by Samuel Sianipar on Unsplash

Where To Study Excel Courses

By now you can probably tell that we’ve only just scratched the surface of what Excel is capable of. If you’re interested in diving deeper, consider investing in a video tutorial on Excel from online resource hub Udemy, or even enrolling in an online degree program at the University of the People. From Computer Science to a Master’s in Business Administration (MBA), you’ll be sure to hone your Excel skills while earning your degree! Online learning allows you to get the skills you need from wherever and whenever you want, all you need is a willingness to learn and an Internet connection.

A working knowledge of Excel will serve you well no matter your field, and we welcome you to explore our tuition-free programs to ensure you’re equipped with these invaluable skills.