- Contact Us
If you are a Manager you may be (very!) familiar with Excel spreadsheets. Whether you are keeping track of employee hours or calculating spend on development or production, you’ve dabbled in some of what the program has to offer. We know it can be a daunting task, keeping track of all things business-related in Excel and we want to make it a little easier for you. We know the application can be intimidating and we don’t want you spending any more time in Excel than you have to, so we’ve compiled a quick starter’s guide. Whether this is a refresher course on Formulas and Sorting or new information on quick hacks and Shortcuts, this guide will have you in and out of Excel and back to what you do best- working with your team towards your bottom line!
You can use Excel to simply make a list and create graphs and charts or you can use complex formulas to create budgets, reporting and more. Here are some basic Excel functions- some you may already be using and others that can assist in cutting time and boosting efficiency.
When most people think of Excel, they think of intricate formulas and math equations. There are an infinite amount of formulas for Excel and the most used are actually the most simple- SUM, COUNT and AVERAGE. SUM allows you to sum any number of columns or rows by selecting them or typing them in, for example, =SUM(A1:A8) would sum all values in between A1 and A8 and so on. COUNT counts the number of cells that have a number value in them. This would be useful for maybe determining if someone has paid, or in other database situations. AVERAGE does exactly what it sounds like and take the average of the numbers you input. Formulas do all the math for you, giving you several data points without all the long-hand and eliminating human error.
A game changer in terms of saving you time, Autofill helps you populate several cells with replicated data, like numbers and formulas. Simply select a cell, find the fill handle in the lower, right-hand corner of the cell- it looks like a + and then drag through other cells to import the value. This is great for groups of data, so you don’t have to tediously fill each, cell by cell.
These functions are the lifeblood of Excel. Being able to quickly and efficiently filter information and sort data is one, if not the main reason people use Excel. When you look at large sets of data, you don't usually need to look at every single row at the same time. Sometimes, you only want to look at data that meets certain criteria. This is what filters are for.
In Excel, a filter can be added to each column in your data. From there, you can then choose which cells you want to view at once. Add a filter by clicking the Data tab and selecting "Filter." Click the arrow next to the column headers and you'll be able to choose whether you want your data to be organized in ascending or descending order, as well as which rows you want to show.
Sort gives you the opportunity to categorize your data alphabetically by column or row. This helps you organize random or unorganized data into a clean, understandable list. Click on the data in the column you want to sort. Then click on the "Data" tab in your toolbar and look for the "Sort" option on the left. If the "A" is on top of the "Z," you can just click on that button once. If the "Z" is on top of the "A," click on the button twice.
The basics wouldn’t be complete without Shortcuts or quick hack. These are a great way to cut down on time and keep you organized when creating spreadsheets. Below is a list of the most common and widely-used shortcuts in Excel:
CTRL — Shift & Outline border to the selected cells.
CTRL Shift # — Change Date format with day, month and year.
CTRL Shift @ — Change Time format with hour and minute, AM or PM.
CTRL Shift : – For the current time.
CTRL ; — For current date.
CTRL 9 — Hide selected rows.
CTRL 0 — Hide selected columns.
CTRL ` — Display Formulas/Alternate in the worksheet.
CTRL+K — To insert Hyperlink.
CTRL+H — Opens up the Find and Replace dialog box.
CTRL+7 — Hides or displays the standard toolbar.
ALT+F1 — This creates a chart out of the data in the current range (selected cells). This is the most well-known feature of Excel, and now you know a new way to use it!
ALT+SHIFT+F1 — Inserts a new worksheet into a workbook.
F6 — Switches between the worksheet, Ribbon, task pane, and Zoom controls. This is probably the most useful shortcut of all. This cool shortcut is also the most beloved to all the anti-mouse users out there.
SHIFT+F6 – The reverse of the F6 shortcut.
CTRL+F6 – Switches to the next workbook window when multiple workbook windows are open.
CTRL+SHIFT+$ – Applies the currency format to the selected cells.
CTRL+`– Alternates between showing the value of a cell and the formula in the worksheet. Great for complex worksheets.
CTRL+D – Fill down command on the selected cells. Fill down copies the content and format of the topmost cell into the cells below.
CTRL+R – Fill right command on the selected cells. Fill right copies the content and format of the leftmost cell to the cells to the right.
As mentioned, Excel is a robust application. However, you don’t need all the fancy bells and whistles to track, calculate or report on your data. As your business grows, you may find that you need to add columns and rows (or hundreds) to your most-used spreadsheets. You can insert by highlighting the same number of pre-existing rows or columns that you want to add. Then, right click and select "Insert."
As shown above, you can add an additional three rows by highlighting three rows and then clicking insert, you are able to add an additional three blank rows into the spreadsheet quickly and easily.
Perhaps you are compiling an inventory or revising your vendor information and have duplicate data. You can remove duplicate information from columns or rows by highlighting the row or column that you want to remove duplicates from. Then, go to the Data tab, and select "Remove Duplicates" (under Tools). A pop-up will appear to confirm which data you want to work with. Select "Remove Duplicates," and that’s it!
Aside from the basic functionalities you can create and perform in Excel, it’s also a common export tool. Most Customer Relationship and Human Capital Management systems export Excel-compatible files so you can filter, sort and report data in a more user-friendly format.
Now that you’ve got the basics, don’t be afraid to explore Excel further. You can find local advanced Excel courses, YouTube videos on specific functionalities or check out this course offered by LinkedIn. And stay tuned for our Advanced Excel User’s Guide to learn about pivot tables, transposing, paste special and more!
*Excel GIFs provided by Hubspot