Ever find yourself elbows deep in an Excel worksheet with seemingly no end in sight? You’re manually replicating columns and scribbling down long-form math on a scrap of paper, all while thinking to yourself, “There has to be a better way to do this.”
Truth be told, there probably is … you just don’t know it yet. Excel can be tricky that way. On one hand, it’s an exceptionally powerful tool for reporting and analyzing marketing data. On the other hand, without the proper training, it’s easy to feel like it’s working against you.
To help you use Excel more effectively (and save a ton of time), we’ve compiled a list of essential functions, keyboard shortcuts, and other small tricks you should know.
Download nine of our most popular Excel templates here.
12 Excel Formulas, Keyboard Shortcuts & Tricks That’ll Save You Lots of Time
1) Quickly select rows, columns, or the whole spreadsheet.
Crunched for time? (Who isn’t?)
No problem. You can select your entire spreadsheet in just one click. All you have to do is simply click the tab in the top-left corner of your sheet to highlight everything all at once.
Just want to select everything in a particular column of row? That’s equally as easy. Just use these shortcuts:
Mac
- Select Column = Command + Shift + Down/Up
- Select Row = Command + Shift + Right/Left
PC
- Select Column = Control + Shift + Down/Up
- Select Row = Control + Shift + Right/Left
This shortcut is super helpful when you’re working with larger data sets, but only need to select a specific piece of it.
2) Automatically fill columns or rows with data.
Tired of manually entering data that follows a pattern across a bunch of cells? Excel’s Auto Fill feature is designed to minimize the work required on your end by making it easy to repeat values you’ve already input.
To do so, click and hold the lower right corner of a cell, and then drag it down or across into adjacent cells. When you release, Excel will fill in the adjacent cells with the data from the cell you first selected.
3) Quickly open, close, or create a workbook.
Need to open, close, or create a workbook on the fly?
The following keyboard shortcuts will enable you to complete any of the above actions in less than a minute’s time.
For Mac:
- Open = Command + O
- Close = Command + W
- Create New = Command + N
For PC:
- Open = Control + O
- Close = Control + F4
- Create New = Control + N
4) Customize the color of your tabs.
Have a ton of different sheets going in one workbook? Make it easier to identify where you need to go by color-coding the tabs. For example, you might label last month’s marketing reports with red, and this month’s with orange.
To do so, simply right click a tab and select “Tab Color.” A popup will appear that allows you to choose a color from an existing theme, or customize one to meet your needs.
5) Format numbers into currency.
Have raw data that you want to turn into currency? Whether it be salary numbers, marketing budget, or ticket sales for an event, the solution is simple.
Just highlight the cells you wish to reformat and select Control + Shift + $.
The numbers will automatically translate into dollar amounts – complete with dollar signs, commas, and decimal points.
6) Add a comment to a cell.
Want to make a note or add a comment to a specific cell within a worksheet? Simply right-click the cell you want to comment on and then click Insert Comment. Type your comment in the text box and click outside the comment box to save it.
Cells that contain comments display a small, red triangle in the corner. To view the comment, hover over it.
7) Insert current date and time into a cell.
Whether you're logging social media posts or keeping tabs on tasks you’re checking off your to-do list, you may want to add a date and time stamp to your worksheet. To do so, you’ll want to start by selecting the cell into which you want to insert the time, date, or time and date.
Then, depending on what you want to insert, do one of the following:
- Insert current date = Control + ; (semi-colon)
- Insert current time = Control + Shift + ; (semi-colon)
- Insert current date and time = Control + ; (semi-colon), SPACE, and then Control + Shift + ; (semi-colon).
8) Copy and duplicate formatting.
If you’ve ever spent some time formatting a sheet to your liking, you know that it’s not necessarily the most enjoyable activity. In fact, it’s pretty tedious.
For that reason, it’s likely that you wouldn’t want to have to repeat the process next time … and you don’t have to. Thanks to Excel’s Format Painter, you can easily copy the formatting from one area of a worksheet to another.
To do so, simply select the thing you’d like to replicate, then select the Format Painter option (paintbrush icon) from the dashboard, as show below:
The pointer will then display a paintbrush, prompting users to select the cell, text, or entire worksheet they’d like to apply that formatting to.
9) Extract specific characters from a cell.
Let’s say you have code that you want to break down into a few different segments. Rather than manually retyping each piece of the code into its respective column, users can leverage a series of string functions to deconstruct the sequence as needed.
LEFT:
- Purpose: Used to extract the first X numbers or characters in a cell.
- Syntax: =LEFT(text, number_of_characters)
- Parameters:
- Text. The string that you wish to extract from.
- Number_of_characters. The number of characters that you wish to extract starting from the left-most character.
For the sake of this example, we entered =LEFT(A2,4) into cell B2, and copied it into B3:B6. This allowed us to extract the first 4 characters of the code.
MID:
- Purpose: Used to extract characters or numbers in the middle based on position.
- Syntax: =MID(text, start_position, number_of_characters)
- Parameters:
- Text. The string that you wish to extract from.
- Start_position. The position in the string that you want to begin extracting from. For example, the first position in the string is 1.
- Number_of_characters. The number of characters that you wish to extract.
For the sake of this example, we entered =MID(A2,5,2) into cell B2, and copied it into B3:B6. This allowed us to extract the two numbers starting in the fifth position of the code.
RIGHT:
- Purpose: Used to extract the last X numbers or characters in a cell.
- Syntax: =RIGHT(text, number_of_characters)
- Parameters:
- Text. The string that you wish to extract from.
- Number_of_characters. The number of characters that you want to extract starting from the right-most character.
For the sake of this example, we entered =RIGHT(A2,2) into cell B2, and copied it into B3:B6. This allowed us to extract the last two numbers of the code.
10) Identify duplicate values.
In many instances, duplicate values – like duplicate content when dealing with SEO – can be troublesome if gone uncorrected. However, in other cases, you simply need to be aware of them.
Whatever the situation may be, it’s easy to surface any existing duplicate values within your worksheet in just a few quick steps. To do so, click into the Conditional Formatting option, and select Highlight Cell Rules > Duplicate Values…
Using the popup, create the desired formatting rule to specify which type of duplicate content you wish to bring forward.
For the sake of this example, we were looking to identify any duplicate salaries within the selected range by formatting the duplicate cells in yellow.
11) Add up the sum of cells that meet a certain criteria.
Let’s say you want to determine the profit you generated from a list of leads who are associated with specific area codes or calculate the sum of group of employees’ salaries that fall above the a particular amount. Sounds a little time-consuming, doesn’t it?
With the SUMIF function, it doesn’t have to be.
SUMIFS:
- Purpose: Used to add up cells that meet a certain criteria.
- Syntax: =SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], …)
- Parameters:
- Sum_range. The range of cells you’re going to add up.
- Criteria_range1. The range that is being tested using Criteria1.
- Criteria1. The criteria that determine which cells in Criteria_range1 will be added together.
To see the function in action, check out this example:
In an effort to calculate the sum of the salaries that were greater than $70,000, we used the SUMIF function to add up the dollar amounts that exceeded that number in the cells C3 through C12. To achieve this, we entered =SUMIF(C3:C12,“>70,000”) into the Formula Bar.
12) Clean up irregular spacing.
Ever have one of your colleagues send you a worksheet with some funky spacing going on? Not only do these rogue spaces can make if difficult to search for data, but it also affects the results when you try to add up columns of numbers.
Rather than painstakingly removing and adding spaces as needed, you can clean up any unnecessary spacing using the TRIM function.
TRIM:
- Purpose: Used to remove extra spaces from data (except for single spaces between words).
- Syntax: =TRIM(“Text”)
- Parameters:
- Text. The text from which you want to remove spaces.
Here’s an example of how we used the TRIM function to remove extra spaces after a name on our list:
To do so, we entered =TRIM(“Steve Peterson”) into the Formula Bar.
What are your favorite Excel tips and tricks for saving time? Let us know in the comments section below.
from HubSpot Marketing Blog http://bit.ly/1ORPJj6
from Tumblr http://bit.ly/1W3TjqI
No comments:
Post a Comment