So you use Microsoft Excel everyday for work. Does it sometimes take you much longer than it should to complete tasks? Do you ever feel like you are still only using the basics? Could your spreadsheets be smarter or better organized? Here are some Excel tips that can make your tables easier to update and manipulate without all of the manual data entry issues.
EXCEL TIP 1 - No Clicks Totals
Is all of your data already in a table? Do you want to add a Totals row at the bottom? Here is a quick tip from Microsoft itself for a no clicks, no formulas way to total. Select the cell you want Then press Ctrl+Shift+T and Excel will do the rest. (For Mac users, this shortcut is Command+Shift+T).
EXCEL TIP 2 - Filters
You don't always have to see every cell in your spreadsheet. Do you ever find that it is difficult to organize a large data set down to just the information relevant to your specific report or analysis? When you only need to see specific data or categories of data, use Excel's Filters option.
Filters allow you to sort out data based on your column headings and have assigned to "filter" using the Filter button that looks like a funnel. This way you can tell Excel that you only want to see the rows that meet your specific filter options.
"Add a filter by clicking the Data tab and selecting 'Filter.' Clicking 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 specific rows you want to show." HubSpot
Video Credit: HubSpot
EXCEL TIP 3 - Keyboard Shortcut: Chart Your Data
You have all of this data in your spreadsheet and now you need to report on it. Aren't reports better with charts and graphs and visual components? Your boss, your team and your clients won't loath your reports if they can understand the data they are looking at.
Select any cell in your data range and press Alt+F1 for Windows users OR option+fn+F1 for Mac users. Your table of data is now a chart!
NOTE: If you are a Mac user and want fewer buttons to remember, you can enable your F1-F12 keys using these steps from Apple. This way you don't have to press the fn key:
- Choose System Preferences from the Apple menu.
- Click Keyboard.
- Click the Keyboard tab if it's not already highlighted.
- Select "Use all F1, F2, etc. keys as standard function keys"
EXCEL TIP 4- Conditional Formatting Formula
With Excel's Conditional Formatting tool, you can visually organize data with cell colors. Using the Excel formula and setting rules, you can change the color of a cell based on the cell's content. You can use this feature to highlight a specific group of information (like the highest numbers or lowest numbers). You can also use Conditional Formatting to color code duplicates, unique values or specific numbers and text.
"To get started, highlight the group of cells you want to use conditional formatting on. Then, choose "Conditional Formatting" from the Home menu and select your logic from the drop down. (You can also create your own rule if you want something different.) A window will pop up that prompts you to provide more information about your formatting rule. Select "OK" when you're done, and you should see your results automatically appear." HubSpot
Video Credit: HubSpot
EXCEL TIP 5- The Status Bar
Do you ever catch yourself trying to drum up some complicated formulas to get a quick answer? Maybe you're in a meeting and your boss needs a number right now. Maybe your client expects you to know more detailed analytics than you have off the top of your head. Use the Status Bar to see averages, counts, and sums without a single formula. Maybe you set up Conditional Formatting using Tip 3 and you want some quick stats on a certain data set.
Select the cells you need this data for then simply LOOK DOWN. The Status Bar is in the lower right corner of your Excel window. Viola! See your stats.
EXCEL TIP 6 - Combining Cells with &
Do you have names, addresses or other text data in your spreadsheets? Sometimes you break these into different columns to organize by zip code, last name or city. If you've already divided this information into separate columns - Great Job! But do you ever want to put that information back together in one cell? Well, with this tip you can combine the contents of two or more cells. Use the & symbol as part of your formula to create a phrase-like data set in a different cell. For example, use =A2&B2 to streamline or simplify the data as seen below.
BONUS: use " " to add a space between the content of two cells. The formula for this example would look like this: =A2&" "&B2
EXCEL TIP 7 - Make Projections
Microsoft Excel can do more than crunch numbers; it can predict them, too! The Forecast formula helps find trends and predict values based on the data in your spreadsheet. Sales teams, inventory managers and consumer report analyzers use this feature to help project trends in their field that is specific to their clients using their own data.
Use the formula =FORECAST(x, known_y’s, known_x’s)
EXCEL TIP 8 - Add Cells if They Meet the Right Conditions
SUMIFS calculates the sum of the cells in a range you define, IF they match the criteria you set. Say you only want to add cells that are on page 2 and 3. Maybe you want to see all of your sales last month from a specific salesman.
Use the formula =SUMIFS (sum_range, range1, criteria1, [range2], [criteria2], …)
EXCEL TIP 9 - IF Statement
Check out this great tip from, HubSpot:
"Sometimes, we don't want to count the number of times a value appears. Instead, we want to input different information into a cell if there is a corresponding cell with that information.
For example, in the situation below, I want to award ten points to everyone who belongs in the Gryffindor house. Instead of manually typing in 10's next to each Gryffindor student's name, I can use the IF THEN Excel formula to say that if the student is in Gryffindor, then they should get ten points.
The formula: IF(logical_test, value_if_true, value of false)
Example Shown Below: =IF(D2="Gryffindor","10","0")
In general terms, the formula would be IF(Logical Test, value of true, value of false). Let's dig into each of these variables.
- Logical_Test: The logical test is the "IF" part of the statement. In this case, the logic is D2="Gryffindor" because we want to make sure that the cell corresponding with the student says "Gryffindor." Make sure to put Gryffindor in quotation marks here.
- Value_if_True: This is what we want the cell to show if the value is true. In this case, we want the cell to show "10" to indicate that the student was awarded the 10 points. Only use quotation marks if you want the result to be text instead of a number.
- Value_if_False: This is what we want the cell to show if the value is false. In this case, for any student not in Gryffindor, we want the cell to show "0" to show 0 points. Only use quotation marks if you want the result to be text instead of a number.
Note: In the example above, I awarded 10 points to everyone in Gryffindor. If I later wanted to sum the total number of points, I wouldn't be able to because the 10's are in quotes, thus making them text and not a number that Excel can sum."
Contact our team for a personalized analysis of your software systems and workflows. We can create a custom solution that works to improve your existing systems, and simplify your data handling.