...
eCommerce WordPress Themes
Desk Name Plates
Phone Lock Boxes
Desk Pads & Blotters
Photo Printers
Laptop Backpacks

Need help?

Contact Us

0 My Cart
Menu Categories
Cart $0.00
Shop Now

best Excel tips and tricks, shortcuts, and hacks to boost productivity

Microsoft Excel is an integral business tool. Some people relish the capabilities of Excel, finding it to be a useful tool that allows them to easily manage, report on, and illustrate data. Whether you’re analyzing huge amounts of data or trouncing timesheet templates, Microsoft Excel management can help you get the job done.

One thing almost every Excel user has in common: not knowing enough. There are so many ways to slice and dice data that it’s impossible to discuss them all. Entire books are written on the topic. But it’s easy to master some of the more interesting and intricate tips that will make your time using the program easier and will make you look like a guru. Bone up on any or all of these tricks to excel at Excel.

Excel-shortcuts.jpg

Table of contents

1. Formulas for simple calculations
2. INDEX and MATCH formulas
3. IF formula to automate Excel functions
4. Test VLOOKUP to pull data across sheets
5. Have COUNTIF count words or numbers
6. Use named ranges for easier formulas
7. Create a new workbook
8. Select an entire row
9. Select an entire column
10. Select the rest of a column
11. Select the rest of a row
12. Add a hyperlink
13. Open the format cells window
14. Autosum selected cells
15. Add multiple rows or columns
16. Use conditional formatting for color coding

If you find Excel confusing or are looking to get better at it, read below to learn Excel tips and tricks to make your life easier and more productive. We’ll save you the Google search—read on to discover a list of smart and handy Excel shortcuts:

1.

Use formulas for simple calculations

Excel’s built-in formulas make it easy to perform all sorts of calculations, from basic arithmetic to complex functions. Whether you need to calculate employee hours or an average price, Excel’s formulas can save you time and ensure accurate results.

For example, if you want to sum the numbers in a range from A1 to A5, you can type “=SUM(A1:A5)” in a cell and press Enter. The sum of the numbers will appear in the cell.

2.

Use INDEX and MATCH formulas for horizontal data

When working with data arranged horizontally, the combination of the INDEX and MATCH functions can be more efficient than VLOOKUP. This approach allows you to look up values based on both row and column criteria, making it a powerful tool for complex data analysis. In this case, you’ll use the INDEX formula and include the MATCH formula within it.

3.

Try the IF formula to automate Excel functions

The IF formula is a powerful tool for automating certain Excel functions. With this formula, you can specify different values or actions depending on whether a particular condition is true or false. 

The formula is =IF(logical_test, value_if_true, value_if_false), where: 

  • The logical test is the condition that you want to evaluate.
  • The value_if_true is the result or action to be taken if the condition is met or true. 
  • The value_if_false is the result or action to be taken if the condition is not met or false. 

For example, you can check if a cell value is greater than a certain number.

4.

Test VLOOKUP to pull data across sheets

The VLOOKUP function helps you extract data from one part of your spreadsheet and display it elsewhere. This is especially useful when you have data spread across multiple sheets.

For example, say you enter the VLOOKUP formula: “=VLOOKUP(A2, Sheet2!A:B, 2, FALSE)” into your sheet. The formula will search for the value in cell A2 of your current sheet in Sheet2 and return the corresponding value from the second column.

5.

Have COUNTIF count words or numbers

The COUNTIF function, which is =COUNTIF(range, criteria), is a great way to count the occurrences of specific words, numbers, or other criteria within a range of cells. This can be helpful for tasks like inventory management and identifying trends.

6.

Use named ranges for easier formulas

Assigning meaningful names to cell ranges or cells can make your formulas more intuitive and easier to understand. Named ranges also let you reference cells by their descriptive names rather than cell addresses, simplifying formula-building. 

To use named ranges, start by selecting the cells you want to name, navigate to the Formulas tab, and select Define Name. 

7.

Create a new workbook

To quickly create a new Excel workbook, use the keyboard shortcut Ctrl + N for Windows or Cmd + N for Mac. This will open a new, blank Excel workbook ready for you.

8.

Select an entire row

Simply press the Shift + Spacebar on your keyboard. This will highlight the entire row. Selecting an entire row allows you to apply formatting, sort data, or delete rows quickly.

9.

Select an entire column

Press Ctrl + Spacebar on Windows or Cmd + Spacebar on Mac. Like select an entire row, it allows you to quickly make a selection to manipulate, format, or analyze data.

10.

Add a hyperlink

To quickly add a hyperlink to a cell in Excel, press Ctrl + K in Excel for Windows or Cmd + K for Mac. Adding a hyperlink is useful for quick access to external files, websites, or specific locations within the same workbook.

11.

Select the rest of a row

Press Ctrl + Spacebar on Windows or Cmd + Spacebar on Mac. Like select an entire row, it allows you to quickly make a selection to manipulate, format, or analyze data.

12.

Select the rest of a column

If you need to simply select the rest of a column versus the entire column, click on the cell you’d like to start and press Ctrl + Shift + Down Arrow on Windows or Cmd + Shift + Down Arrow on Mac.

13.

Open the format cells window

To open the Format Cells window in Excel, press Ctrl + 1 on Windows or Cmd + 1 on Mac. This window will allow you to modify cell formatting, including changes to fonts, borders, and number formats.

14.

Autosum selected cells

To quickly sum up a range of selected cells in Excel, press Alt + = for Windows or Opt + = for Mac. Autosum is a handy feature in Excel that allows you to quickly calculate the sum of selected cells.

15.

Use conditional formatting for color coding

Conditional formatting enables you to automatically apply different colors, icons, or data bars to cells based on their values or criteria you define. This can help you quickly identify patterns, trends, and outliers in your data.

In the “Home” tab of the Excel ribbon, click the “Conditional Formatting” button. Excel offers a wide range of options, such as highlighting cells that contain specific text or are greater than or less than a certain value.

16.

Add multiple rows or columns

To insert multiple rows or columns, select the number of rows or columns you want to add, right-click, and choose “Insert.”

Excel will automatically add the same number of rows or columns that were highlighted and shift the existing data down or to the right to accommodate the new additions. This method is especially useful when a large number of rows or columns need to be added.

Spend more time growing your business

Your journey toward using Excel and leveling up your management starts with that first column and will go on for years as you learn more advanced Excel functionalities.

You can also level up your spreadsheet skills by learning to use Excel for accounting and bookkeeping. Or skip the data entry and use accounting software like QuickBooks Online to streamline your finances. QuickBooks is automated and helps you run your business more efficiently and error-free.

Want more Tips and tricks like this?

Subscribe us and get latest news, best tips and updates to your inbox directly.

Excel tips and tricks FAQ

Visual Basic for Applications (VBA) tends to be the most difficult area of Excel. VBAs include writing macros and designing Excel apps.

Excel is easier than Python, as Python requires programming skills. You also need to know the Python language and its various libraries.

Excel is easier than SQL given its user-friendly interface. It requires little or no programming.