When it comes to using CRMs, or really any occasion where you’re working with large amounts of data, Microsoft Excel is one of the best tools for organizing and enhancing the information that you need for the workplace. Here, we’ve compiled some of our favorite tools and tricks in Excel to speed up your workday and maybe give you some new insight.
For a video tutorial, be sure to watch our webinar!
#1 Formatting Tricks
One key part of becoming fluent in Excel is being able to manipulate cells to look the way you want them to. Here are some of our favorite methods:
Series Creation through Dragging:
Excel has built-in pattern recognition logic that can be used to save some time. One way Excel uses it is by dragging out rows or columns. For example, if you start with 3 months in a row and you want to fill out the rest of the row with the corresponding months, click and drag the rightmost cell by as many cells and you need.
Sometimes your cell entries are longer than the set width of the column and can look something like this:
To get around this, first, you want to highlight the columns you want by clicking the leftmost cell and dragging right to the cell that you want.
Now double click the border (your cursor should be an arrowed cross) between any two of the highlighted cells, and all the columns highlighted will reformat themselves to fit the text inside.
Transposing data from horizontal to vertical
Our last regular formatting trick is transposing a series from horizontal to vertical. If you have a row of values that you would like to turn into a column, first highlight and copy the row that you want.
Next you’ll want to select the cell you want to begin your column on and click the arrow under Paste which can be found under the Home tab on the top left of your window. Choose the Paste Special option.
#2 Conditional Formatting
Our next tool we’d like to introduce is conditional formatting. When you’re looking at large sets of numbers, it can be hard to track what they all mean for your company. With a simple tool like conditional format, you can turn a bland table into something that looks like this:
The first thing you want to do is locate your Conditional Formatting ribbon. It can be found at the top of your screen under the Home tab.
To assign colors to different values in a chart, first highlight the table in question. Then select your conditional formatting ribbon, and click on Highlight Cell Rules. From there, you’ll be able to select the conditions you want.
#3 Removing Duplicates
Duplicate entries are a problem for most kinds of digital record keeping and Excel is no exception. Luckily, Excel has some tools that you can use to look out for and deal with duplicate entries. One way to do this is to revisit our friend, Conditional Formatting.
The first thing you want to do is highlight the column that you want to check for duplicates in. Next, click on the Conditional Formatting ribbon, and then click Highlight Cells Rules. This time, you’ll click on Duplicate Values.
Just like the last tip, you’ll be able to highlight duplicate entries with a color that you select. If you right-click on the column again and select Sort by Color, you’ll be able to organize all of your duplicate entries in the way that you want.
Another way to remove duplicate entries is through the Data tab, located on the top of your screen.
Select the column that you want to remove duplicate entries in, and then look for the Data Tools Ribbon, located at the top of the screen. Select the Remove Duplicates button.
A popup box will appear, asking either to expand the selection or continue. Click continue and Remove Duplicates, and Excel will delete any identical records!
#4 Combining Data in Excel
When working with different data columns in Excel, there may be occasions where you want to combine the data in two different columns such as first and last names. To do this, we can use a quick formula. First, create a new column and name it whatever you choose. Select the first blank field in the new column and in the formula bar, type in the cell of the first column, then type &” “& and then type the name of the cell in the second column.
Then hover your mouse over the bottom right corner of the new joint cell and drag down the column to match however many entries are in the first two columns. The end result looks like this:
#5 Forms and Drop-Down Lists
Excel doesn’t have to be about organizing data. Here we’ll show you how Excel can be used in the data entry stage by demonstrating step by step how to make order forms and drop-down lists, both things that can bring your Excel game to the next level.
Create your Work Space
Excel by default presents itself with gridlines, but in a form, we want to be able to work with a nice visually empty space. In order to do this, first, select a large chunk of cells and right-click. Then click format cells.
A popup for cell formatting will appear. Select the Fill tab on the top and then in the Pattern Style dropdown, select the blank pattern. Now your selected grid should appear empty. The actual cells still exist as they were, but they just aren’t distinguishable from each other without further interaction.
The next thing you’ll want to do is adjust how the page is presented to you. You only really need to be able to see the grid you’ve cleaned out. First select your grid, and then click page layout underneath the View tab at the top of your screen.
With this selected, when you go to export or print the form, Excel will print out only the gridded out area and not any of the external cells.
You may want to add your company logo or other images to your Excel form. In Excel, you can drag and drop images anywhere onto the sheet and it will stay there without formatting issues. Conversely, you can add images from your files through the Insert tab on the top of your screen. Under the illustrations or pictures ribbon, you can then choose the file location of the picture you want to upload.
Dates are an important part of many forms and Excel has a variety of formats to keep track of them. When typing dates make sure to change your Number setting Short Date or Long Date in the Numbers Ribbon under the Home Tab.
Another way to enter dates is through formulas. If you want to display the current date, simply type into a cell’s formula bar: =TODAY(). This will display your computer’s current date in that selected cell. This tool can also be used to set up deadlines. By adding + 6 to the original formula, the selected cell will now display the date six days after the current date. Be sure to have that cell formatted to a date!
Sometimes for aesthetic or organizational purposes, you may want to merge a row of grids into a uniform line. This can be easily done by highlighting the cells in the row that you want to merge, right-clicking and then selecting format cells. In the formatting popup, select the Alignment tab and check the Merge cells box under Text Control. Press OK and your cells will merge.
Another powerful organizational tool for digital forms is the dropdown list. To begin with, somewhere in your Excel sheet, create a list of items that you want to include in your dropdown list. The list can look like this one:
Now select the cell in which you want to build your dropdown list. Then select the Data Validation Button found under the Data tab at the op of your screen.
In the Data Validation popup box, make sure to select List in the Validation criteria under the Settings tab.
Once you’ve selected List, you should see another line asking for a source. Click on the arrow on the right and then highlight the list you previously made.
Press enter and OK and the original cell you selected should now display a dropdown list composed of the items you made before. Here is what the end result looks like:
#6 Charts and Graphs
For data visualization, nothing beats graphs and charts, and being fluent in making these in Excel is a must! To begin with, make sure you have your data in table form. For now, we’ll use a simple table like this:
To begin, highlight the data that you want and then select a chart from the Insert tab. In this case, we’ll choose a Line chart. Once you’ve selected the chart you want, Excel will generate that chart based on the highlighted data.
Now that you’ve made your chart, it’s time to customize it to show all the data you want. Double click on the chart’s border and chart formatting options will appear, giving you access to new chart styles as well as the ability to change various aesthetic components. You can add data elements to your chart by clicking the Add Chart Element button under the Chart Design Tab. Chart elements that can be added include axis titles, data point labels, and more. These individual elements are all also customizable so there’s a lot of room for you to design your charts as you see fit.
Another awesome part about Excel graphs is that you can continually adjust the data and the chart will change with it. In the picture above, we’ve now added another data set below the first. By dragging the highlighted section to include the new data set, the new points get added as an additional line chart to the graph. The end result looks like this:
#7 Decision Matrixes
For our last tool to introduce, we will be showing you a key Decision-Making Tool, the Decision Matrix. This tool incorporates many of our previous tips and is one of our more advanced tips! The core concepts of this framework are to decide potential options and also to assign criteria to weigh those options. It will look something like this:
The first thing you want to do is list all of your options on the left-most column, and the 3 – 5 most important criteria for them along the top row. You can assign additional weights to those criteria with a weighting factor on the row above them. Set up drop-down lists that allow you to pick scores from 1-5 in the area of the chart. Finally, you’ll want to set the formula in the score region on the right. To do this, simply set the score to equal the sum of the criteria scores in the same row. Make sure that you are multiplying each criterion score by its corresponding weighting factor. Here is what the formula looks like:
Notice how there are dollar signs between the X and Y coordinates of each cell? Those dollar signs act as anchors. This way, if the chart ever changes, the formulas within the cells will stay anchored to the original cell it was referencing and you won’t have to worry about your scores reflecting something that you didn’t intend them to. Once you’ve finished the first row, hover over the bottom right of each cell in the row and drag and drop for as many options as you have. This fills in the rows below, adjusting for the new coordinates but keeps the base formula the same. Now you have a decision matrix that you can adjust on the fly and use however you see fit!
That’s it for our tips and tricks on Excel. We hope you’ll find them useful!