Top 10 Excel tips

Excel Data To Word

Another easy but helpful Excel trick is copying cells or charts into Word. Though a straightforward copy-and-paste maneuver, be sure to note that this “link-and-embed” process — meaning that if you change the data in the spreadsheet — will also change the data in its Word counterpart. If you’d like to avoid this, instead paste the data as a graphic using Word’s “Paste special” feature. You can also select “Copy as picture” within Excel, and paste the static graphic into Word.

Find and replace values

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.”

Input Restriction

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.

Use conditional formatting

It can be used for various purposes such as color format or cell format of cells, rows or columns based on dependent cell values or formats.

Apply Some Conditional Formatting

In Excel, you can add Conditional formatting this can add some pop to the sheets and helps you pick out data easily. It is simple to use. Select the data that is needed to be formatted > click Conditional Formatting (in Home) > and build your rules accordingly from the drop-down options.

Quick Analysis

If you need some guidance on what kind of info you’d like to apply to your data, Excel’s “Quick analysis” can provide you with some options, you guessed it, quickly. First select your data and click the quick analysis icon that appears to the bottom right of your selection. You’ll be greeted with a menu that can apply conditional formatting, tally up totals, or even call up charts in one simple click.

Use two windows

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.

Text-to-column - splitting data in one cell to multiple cells

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.

Creating simple graphs

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:

Format whatever object fast with Ctrl+1

If you select any object in Excel – a cell, a chart, a chart axis, a drawing object – then press Ctrl+1, and you will get the Properties dialog for the certain object. This shortcut offers a very quick and easy way to format whatever object you’re working with.