10 easy, time saving tricks to use on a daily basis. (For Windows)
They are useful, make you look smarter, save you hours of tedious work & allow you to teach your boss a thing or two excluded. I have never been much of a writer, but I will try and share my knowledge in the best way possible.
Ok let’s get started…
10. Filter Function: (Alt + D + F + F) This is a shortcut to enable your columns to filter. It is one of the most useful shortcuts I use daily. Is your mind blown yet?
9. Find & Replace: (CTRL + H) Opens the Find and Replace function, with the Replace tab selected. This is fantastic for making bulk changes to a document. (You can also switch to the find tab easily if you need it to find all the SK000347583675490s in a 200K row spreadsheet. Be prepared to feel like a superhero!
8. Fill Down: (CTRL + D) This is FANTASTIC when you have a cell that contains a number and you need to copy it down to the range of cells below. CTRL + D takes the contents and format of the topmost cell of your selected range and identically copies it into the cells below.
7. Number Formatting: (CTRL + SHIFT + !) When creating performance reports it is important that everything is formatted in the best way possible to read the information. This shortcut applies the ‘comma style’ format to your selected cells, with two decimal places, thousands separator, and minus sign (-) for negative values when needed. Amazed?
6. Concatenate: (=Concatenate(cellx, “ “,celly) or =Concatenate(cellx,celly). This allows you to join character strings from two separate cells together. By using the ” ” you are able to put a space between them, or you don’t have two. When trying to add tracking to a URL, this can save hours of tedious time.
5. LEN: (=LEN(cellx)) Counts the number of characters (including spaces) in a given cell. Helps with writing ads, or anything with character limits and saves time by not manually counting!
4. TRIM: (=TRIM (cellx)) Eliminates any spaces in a cell, except for single spaces between words – so anything before or after your text. When trying to match data using something such as a Vlookup it will save hours of hair pulling to figure out why NOTHING WILL MATCH UP.
3. PivotTable: (ALT + D + P or Insert > PivotTable)PivotTables are basically excels version of spark notes. They help you to summarize your data in different ways as well as let you sum, count, average, and conduct other calculations depending on your specified reference points. Do I have your attention yet?
2. Index Match: (=Index(Reference, Match(lookup_value, lookup_array, match_type)) This is virtually a more advanced version of a Vlookup, and it is fabulous. However I tend to use vlookups more which this is why it is only at #9, but this formula will make your life better. Unlike a Vlookup an index match has the ability to match data from any column in a document, you are not restricted to only the columns to the right of your array. Because of this you are able to pull multiple different points of data into a clear and easy to understand matrix.
1. Vlookup: (=VLOOKUP(lookup_value, table_array, col_index_num, range_lookup) This formula takes tedious 5 hour tasks and turns them into 30 second ones. A Vlookup will take data from two separate tabs and find matches based on the column specified compared to the leftmost column on the second sheet. It will then identify matches based on the column you specified as the data you are looking for. So to simplify do the following: =vlookup(Column you are using to match/compare on the following tab,<now click to the second tab> Starting with the column that has similar data as you chose on the first tab – highlight a minimum of 2 columns up to as many as you like, then type the column number the contains the data you are looking to find <make sure you count 1 as the first column you have highlighted>,TRUE or FALSE < If you put TRUE it will give you the closest match. If you put FALSE it will only give you an exact match) **DO NOT FORGET THE COMMAS! And BOOM there you have it!