Microsoft Excel Tips & Tricks

Microsoft Excel is an amazing tool, especially when you’re dealing with a huge amount of data. Still, there are many who aren’t able to maximise the capabilities of this computer program. If you’re among those that need a bit of help in terms of navigating and manipulating Excel, here are some tips to make things easier for you. You can use these as springboards when you take Microsoft Excel classes, which should help you advance your skills further to become a spreadsheet master.

Simple Keyboard Shortcuts

Excel can be frustrating and time-consuming if you don’t know your way around. Thankfully, there are hundreds of keyboard shortcuts that can make things a lot easier. For example, you can press “Alt =” in PC or “Command Shift T” in Mac to perform the autosum function in all selected cells. Meanwhile, if you’re working on percentages, you can press “CTRL Shift %” in both PC and Mac to show all values as percentages. For easy viewing, you can hide or unhide selected rows by pressing “CTRL 9” or “CTRL Shift (” and hide or unhide selected columns by pressing “CTRL 0” or “CTRL Shift )”. These shortcuts work for both PC and Mac.

Data Filtering

If you’re already familiar with this function, congratulations. If not, then here’s how you can filter and sort your data to help with your analysis. First, highlight the data you want to filter and then click on the “Data” tab at the top. There, you will see an icon for “Filter” (it looks like a funnel). Click that and a dropdown menu will appear on each cell in the first row of your selection. Clicking on the dropdown menu will then show you various ways to filter the data. Once you’re done, click the “OK” button and you will now only see the filtered information. To remove the filtering, highlight the cells and click on the “Filter” button again.

Transpose Columns and Rows

Sometimes, you need to rearrange the data you have to make it more understandable. One such way of rearranging data is by moving them from columns to rows or vice versa. This might seem like a time-consuming task, but you can use the Paste Special function to make things simpler. First, select the data you want to transpose and copy it (CTRL C or Command C). Afterwards, select the destination cells where you want to place the data. Right click those cells and select “Paste Special…” from the dropdown menu. Select the “Transpose” option, and voila! The data has now been rearranged.

Auto-Fill Cells

This function is a helpful one but something that often gets forgotten. If you need to fill cells with repetitive items like dates, the first thing you have to do is begin the series. Three to five cells would do. After this, move the cursor to the lower right of the last cell in the series; the handle (the small square in the corner) should change into a plus sign. Click this and then drag down to all the cells you need to fill. Whatever the pattern you used in the first few cells would be applied to the succeeding cells. You can use this function for both columns and rows.

Simple Formulae

By now, you probably already know of the AUTOSUM and SUM functions in Excel. Still, there are many more formulae you can use for simple computations and tasks. Here are some of them.

  • AVERAGE. You can type “=AVERAGE(number1, [number2], …)” or “=AVERAGE(B2:B11)” for simple averages. The latter is useful for a wide range of values.
  • COUNT. This will count all cells that contain number numeric values. For example, “=COUNT(A:A) will count all numerical values in column A. “=COUNT(A1:E1)” will include rows and columns.
  • MAX and MIN. These will help you find the highest and lowest numbers in a range of values. For example, “=MIN(A1:E25)” will find the lowest number between column A to E from rows 1 to 25.

Basic Information

Do you need to know the sum of numbers but don’t need to put the value in the spreadsheet? Just highlight a group of figures and check the lower right corner of your screen. There, you’ll be able to see some basic information regarding the data in the  cells you highlighted. These include the sum of the numbers in the cells, the total number of cells highlighted, and even the average of the numbers.

View Using Two Windows

When you’re working with a lot of data and numbers, sometimes you need to compare them side-by-side. But what if the data you need to view are on different sheets in the same workbook? You don’t need to copy the other data set into another workbook! What you have to do is to click “Window” on Mac or “View” on PC. Then, click “New Window.” What this action does is open a copy of the same workbook in another window. You can then place the two windows side by side to compare data, instead of clicking back and forth between worksheets.

For many Excel pros, these “tricks” are old news. For the rest of the world, these simple tips are surprising breakthroughs. Still, these are just the beginning. There’s so much more you can do with Excel, such as creating macros that can automate routine tasks. Don’t be afraid of Microsoft Excel’s seemingly complicated facade. If you take time to learn, this spreadsheet program can make your business and work life so much easier.