Microsoft Excel is a great tool for pretty much any line of business, but most employees don’t utilize it to its full potential. The most common usage is calculating average sales per cycle, payroll, or lists for customer relationship management software (CRMs). If you already know how to enter information into cells to create a pool of data, you’re off to a great start. A few tips and tricks can make those cells give you a sea of information. If you need more assistance with your spreadsheets, DCA Virtual team members excel in turning data into visual representation.
1.) Selecting data
You can easily select more than just a single cell, or just a row or column. To select multiple cells that aren’t connected, hold down the CTRL (control) key while clicking the cells with your cursor. To select the adjacent cell, hold down Shift while pressing the arrow key in the direction you want to move. To select all cells, hold down CTRL + A (control and the “A” key). Alternatively, click the box located in the upper left hand corner of your workbook located the right of the Column A and above the Row 1 labels to select all cells. This makes it easy to format all cells or set your print area quickly.
You can sort your data in more ways than just A to Z order. If you need a more advanced view of your data, you can customize the way Excel sorts your list. To make your own criteria to sort by, after opening the “Sort” dialogue, chose “Order” and then “Custom list”. From there you can create a list in the order you want Excel to sort by. You can make any kind of criteria, such as a list sorted by Producers, Managers, and Staff, or even text that is sorted by red, blue and yellow. This is especially helpful in CRMs where you can group your customers by sales representative or type of relationship.
3.) Combining Data Into One Cell
Sometimes you need to combine two or more cells into just one. The data can be combined by just adding the “&” symbol. For example, if you want to combine the data in A2, B2, and E2, type in fx=(A2&B2&E2) and all of the data will be combined. If you want to add a space between the combined data rather than having it all run together, you need to add ” ” (double quote space double quote), such as fx=(A2&” “&B2&” “&E2). You can also chose the formula fx=CONCATENATE( ) and type or click which cells you’d like to combine separated by a comma. (Use CONCAT for 2016 and newer versions.) The same double quote method adds a space, such as =CONCATENATE(A2&” “&B2&” “&E2). These methods are helpful when combining cells for address lists or adding titles after each employee’s from a table.
4.) Shortcut Keys for PC
There are many shortcut keys for a PC built into Excel. (Mac computers have a different set, so some may be identical, but most utilize the CMD key instead.) While this list is by no means all shortcut keys available, they are some of the more widely used and helpful ones. Print this table or bookmark this blog post (shortcut key CTRL + D) for easy access to common commands.
|CTRL + N = New||F1 = Help||Home = Go to beginning of the row|
|CTRL + O = Open||F2 = Edit a cell||Shift + F3 = Insert a function|
|CTRL + S = Save||F3 = Paste Name||Shift + F5 = Find|
|CTRL + X = Cut||F4 = Sets an absolute or relative reference||Shift + F9 = Calculate the active sheet|
|CTRL + C = Copy||F5 = Go To||Shift + F11 = New Worksheet|
|CTRL + V = Paste||F6 = Switch between split window panes||CTRL + Home = Go to cell A1|
|CTRL + D = Copy down, from the cell above||F7 = Spellcheck||CNTL + F1 = Format cells|
|CTRL + R = Copy right, from the cell to the left||F8 = Extend Mode||CNTL + F4 = Close open workbooks|
|CTRL + F = Find||F9 = Calculate all sheets||CNTL + Space Bar = select column|
|CTRL + H = Replace||F10 = Excel Ribbon||CTRL + ; = Enter current date|
|CTRL + Y = Redo||F11 = Add a chart||CTRL + : + Shift = Enter current time|
|CTRL + Z = Undo||F12 = Save as||CNTL + ` = Display all formulas on sheet|