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

Two Time Saving Excel Keyboard Shortcuts

1. Excel Keyboard Shortcut for 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.

For Windows users, press Ctrl + Shift + T

For iOS/Mac users, press Command + Shift + T

2. Excel Keyboard Shortcut to Chart 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!

For Windows users, press Option + F1

For iOS/Mac users, press Option + fn + F1

NOTE: If you are a Mac user and want fewer buttons to remember, you can enable your F1-F12 keys using these 4 steps from Apple.
This way you don’t have to press the fn key:

1. Choose System Preferences from the Apple menu.
2. Click Keyboard.
3. Click the Keyboard tab if it’s not already highlighted.
4. Select “Use all F1, F2, etc. keys as standard function keys.

Three Great Excel Tips and Tricks

3. Using Excel 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.”

4. Excel Conditional Formatting Formulas

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.”

5. Using the Excel 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 4 and you want some quick stats on a certain data set.

Select the cells you need this data for then click the Status Bar in the lower right corner of your Excel window. This will allow you to see your stats!

Four Useful Excel Formulas and Expressions

6. Combining Excel 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 will learn how to 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

7. Use Excel FORECAST to 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 Excel formula =FORECAST(x, known_y’s, known_x’s)

8. Add Excel Cells if They Meet the Right Conditions

Calculate the sum of the cells in a range you define with SUMIFS only 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 Excel expression =SUMIFS (sum_range, range1, criteria1, [range2], [criteria2], …)

9. Using the Excel 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.