Top 10 Excel tips

Maybe you noticed an error in your spreadsheet. Or, perhaps you want to update some terminology. There’s no need to scroll through your entire data set to find each individual occurrence of that term or value. Using the “Find and Replace” feature can help you update everything at once. Highlight the cells you want to search and hit Ctrl + F. You’ll be met with a popup where you can enter which term you want to find in the spreadsheet, as well as what you’d like to replace it with. For example, if I wanted to replace the appearance of “Stout” with “Vanilla Stout,” I could use “Find and Replace” to do that in a few short steps. Note: It’s important to be aware that this feature will replace every appearance of the combination of letters that you enter. So, if you had a list of states and wanted to replace CO with AZ, it would replace anywhere that “Co” appears—meaning you could end up with something that says “Azmpany” instead of “Company.”

Similar to the data validation feature, there’s another way to restrict input values and offer guidance to other users adding to your spreadsheet. To ensure data outside of a given range is barred from your sheet, go to “Data,” then “Data Validation,” then “Setting,” and input your desired conditions. You can also shift to “Input message” and provide prompts that steer users to the correct data range, which will show up when their cursor hovers over the cell and trigger a warning message if the value is unqualified.

Do you have worksheets within the same workbook, but would like to view them side by side—rather than needing to click back and forth between the two? Of course, there’s a way you can easily do this. If you’re on a Mac, click “Window” within the main Excel menu and then select “New Window.” If you’re on PC, go to “View” in the Excel ribbon and then select “New Window.” Doing so will open your existing workbook in an entirely new window—so you can position them side by side and avoid a bunch of clicking. The best part? Any changes you make will be applied to both windows—so you don’t need to make changes twice.

Have you seen data in excel that you need but there’s other information in those cells that won’t work with your formulas? While there are some complex formulas that will help you split your text into new columns, it can consume a lot of valuable time. The speedy way to split this up is Text to Columns which splits all the selected cells at exactly the same point and puts the results in separate columns. You can use Text to Columns in two different modes: fixed-width and delimited. Fixed-width is useful if can separate your data using a straight line through all the rows while delimited splits the text based upon the text such as every comma, tab, or space. For example, let’s look at a delimited Text to Columns to remove cents from our total column. Like most functions on the Data Ribbon, first, highlight your data and then click Text to Columns. Once here you’ll choose between fixed-width or delimited. On the next screen, set your splitting criteria. In our example, we used the period. On the final screen, you have the option to exclude sections and change the formatting. This will save you crucial steps later. Then click Finish. And finally the results! All of the cents were placed in the column just to the right.

One of the best ways to improve your Excel skills is being able to communicate your results to others. Graphics, images, and charts are great ways to visualize and represent your data to end users, and Excel does a great job of automating this process. Let’s take a look at how to set up a simple chart using our sample sales data. A simple graph starts with two sets of data, your independent (date) and dependent (results) variables. Below we have selected OrderDate and Total. Clicking on Insert in the Ribbon you’ll see all the graphing options. For this example, a simple line graph will show us the total sales for each date. Excel makes graphing so simple the results even include the title! So the next time you’re called into a last minute meeting you can create a simple graph on the fly in the meeting. Bonus: Check out this lesson on how to create column charts in Excel: