In the era of big data, the amount of information marketers need to analyze is overwhelming. In fact, according to a Hubspot report, marketers spend an average of 3.55 hours a week just collecting, organizing, and analyzing data.
Becoming more data-driven is a good thing; it means you are making smarter marketing decisions. However, if you’re one of these marketers, figuring out how to use Excel has probably become a key part of your job.
Be an even smarter marketer by learning how to use Excel more efficiently and effectively with these 23 easy tips and tricks.
How to use excel with 23 easy Excel tips
- Add dates quickly and easily
To add a string of dates quickly and easy, first type in the starting date. Hover your cursor over the bottom-right corner of the first cell until it turns into a solid black plus sign. Then, click and drag your cursor over the cells you want to fill for as many days as you want. Once you release, they will sequentially fill with the dates.
- Jump from the top to the bottom of your data set, and vice versa
If there are no blank cells in your dataset, you can use Ctrl+Down Arrow to jump to the last row of your data set, or the Ctrl+Up Arrow to jump to the first row.
- Add multiple rows or columns
To add multiple blank rows or columns, select as many rows and columns as you would like to create blank ones of. Then right click and select Insert from the pop-up menu. The blank rows or columns will appear before the ones you selected.
- Select your whole excel sheet at once
Select your whole excel sheet at once by clicking on the corner of the sheet.
- Select multiple cells across rows and columns
To select a large group of cells across rows and columns, select your first cell, press down on the Shift key, and then select your last cell. All of the cells in between should become selected.
- Round up decimals
To round up numbers, use the ROUND function. In the cell next to the number you would like to round, type in =ROUND(Cell you would like to round,Number of decimals). In the example below, this would be =ROUND(A1, 2) to round the number in cell A1 to two decimal places.
- How to use excel formulas on multiple cells
To apply formulas to multiple cells, select a cell where you have already used a formula and drag it over other cells to apply the same formula to those.
- Make text upper or lowercase
To change text casing in a cell, use the UPPER function to capitalize all letter, use the LOWER function to put letters in lowercase, and use the PROPER function to only capitalize the first letter of a word. To get the results in the second column in this example, you would type in =UPPER(A1), =LOWER(A2), and =PROPER(A3).
- Filter your data
To add filters to your data, click on the Data tab in the top navigation. Select the title cells for the columns or rows you would like to filter, then click on the Filter button with the funnel on it in the tools. You can then filter each row of data by ascending, descending, specific categories, and more.
- Freeze rows or columns
To keep a row or column in place while you scroll, select the rows or columns you want to keep in place. Then, click on the layout tab and select the Freeze Panes option.
- Create a diagonal line in cells
Select the cell where you’d like to place a diagonal line, then right click and select Format Cells. In the pop-up, select Border in the top navigation and then click on the icon with a diagonal line on it.
- Copy multiple cells easily
First, type in your formula in the first cell, then hit Ctrl+C to copy it. Hit Ctrl+Shift+Down Arrow to select and jump to the end of your data, then hit Ctrl+V to paste the formula.
- Change data displayed in status bar
To show or hide what’s displayed in the status bar (the bar at the bottom of your Excel window), right click and select what you want to see.
- Transpose data (switch chart axes)
Select the data set you would like to transpose and copy it. Go to a new area of your Excel sheet or to a new tab, right click on a cell and select Paste Special. Check the box for Transpose and then click OK.
- Find duplicates
Select the range of cells you want to test for duplicates. In the Home tab, select Conditional Formatting, then click on Highlighted Cells Rules. Select Duplicate Values and once the pop-up box appears, click OK.
- Dedupe data
Select the range of data from which you would like to remove duplicates. In the Data tab, under the Tools section, and click Remove Duplicates. In the pop-up box, select the columns you would like to dedupe and click Remove Duplicates.
- Get rid of blank cells
Select your range of data, then hit F5. In the pop-up Go To box, click Special, and select Blanks, then click OK. Right click on the selected cells and click Delete.
- Count number of words in cell
To find the number of words in a cell, use this formula: =IF(LEN(TRIM(A2))=0,0,LEN(TRIM(A2))-LEN(SUBSTITUTE(A2,” “,””))+1), shown in the example below.
- Count number of characters in cell
To find the number of characters in a cell, type =LEN(cell). In the example below, the formula =LEN(A2).
- Shade cells according to data
To automatically shade a group of cells based on their value, select the range of cells you’d like to change. Go to the home tab and click on Conditional Formatting, and under Color Scales, click on the scale you’d like to use.
- Combine text from different cells using &
To combine text from one or more cells, type in =(A2&” “&A3&” “&A4), putting whichever cells you would like to combine in place of A2, A3, and A4.
Know more tips for how to use excel? Let us know what they are in the comments!