Excel tips

Use a range name in a formula with =sum( and F3

Suppose you want to use a range name in a formula. For example, you want to sum the Sales range. Enter… =sum( …and then press F3. When you do so, Excel launches the Paste Name dialog. Just choose “Sales” from the list, press the OK button in the dialog, then enter the SUM function’s closing “)” to complete the formula.

Use range names with Ctrol+G or F5 key

If you use range names (which we strongly recommend to do) and you want to choose the range with a specific name references, press either Ctrl+G or the F5 key, which launches the GoTo dialog. If the name is simple, you can click on it in a list in that dialog. But if it’s at all unusual, Excel won’t list it; so you will need to type in the name. Then press OK.

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.

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:

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.

See formula results with F9

If you want to check formula results within multiple formulas, highlight the formula and select F9 to see formula result. Don’t forget to undo before exiting the formula.

Data validation for drop-down menus in cells

Using the drop-down list is a great way to impress your co-workers and boss with your Excel skills. At the same time, it’s a very user-friendly way to make sure custom Excel sheets function properly. This tool is used to enter data in a spreadsheet from a predefined list of criteria. The main purpose of using drop-down lists in Excel is to limit the number of choices available for the user. Apart from that, a dropdown prevents spelling mistakes and makes data input faster. This also gives you the possibility of controlling exactly what can be entered into a cell. This makes it perfect at validating inputs. First Select Data on the Ribbon and the Data Validation. Next, select your parameters. Below we used the months of the year to populate OrderMonth. Once you click Ok, click on the drop-down arrow next to the cell and select from the list. Note: now that one cell is set up, it can be copy and pasted in the remaining cells below.

Click once to Select All

Many of us know that we can select the entire excel sheet by using the Ctrl + A shortcut but I guess few of us know that simply clicking once on the corner button will select the entire data in less than a second.

Create a New Shortcut Menu

Using shortcuts makes the work easy. In Excel we are having three shortcuts in the top menu – Save, Undo Typing and Repeat Typing. But if you want to utilize more shortcuts like Copy and Cut, you can set them up: Here know how: Go to File > Options > Quick Access Toolbar > Add Cut and Copy from the left column to the right and save it. Now you can see two more shortcuts added to the top menu and make the work easy for you. This is really very helpful to organize the Excel crucial data easily and become an Excel expert. Know More MS Excel TRICKS.

Bring Selection into View

This Excel tips will help you to easily recognize the selection area. Suppose if have selected some areas of cells and scrolled away and now you are unable to see it … then, in this case, hit Ctrl + Backspace key this will brings that selection into view. And if you hit Shift + Backspace it will bring the selection into view but reduces the selection to the active cell.