Microsoft Excel is an incredibly capable and complex spreadsheet program. If you’re just getting your feet wet, these tips will help you get started on making a spreadsheet and writing a formula. Once you learn the vocabulary, the rest gets easier.
1. 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.
2. Save an Excel file
Click the File tab, select Save from the side menu, browse to the location where you want to store this file, name it, then click Save. After the first time you save a file, all you have to do to save it again and again is press the Ctrl+S keyboard shortcut. Check out this past story for more handy Excel keyboard shortcuts.
3. Maximize Printing Options when Printing Large Workbooks
Spreadsheets work great on large widescreen monitors, but sometimes you might need to print out your workbook. If you are not careful, you can end up wasting a lot of paper on something mostly unreadable. Excel takes care of this using the Backstage printing options, which let you adjust the page size and orientation. Spreadsheets are best printed on legal size paper using landscape orientation.
If you need to adjust margins to fit additional information on a single sheet when printing, click the Show Margins button in the right-hand corner of the backstage print tab.
You can then use the margins to adjust the columns to fit any data that might spill over to another page.
If you can’t get all the data on one page, use the Page Setup dialog to make further adjustments. The scaling menu can help you reduce the size of the text to help it fit better. Try not to scale too much, since you want to keep text legible.
You can also use the same dialog to dress up your spreadsheet with a header and footer if desired.
4. Group/ungroup columns to hide detail data
Spreadsheets with a bunch of complicated and detailed information can be hard to read and analyze. Luckily, Excel provides an easy way to collapse and expand the messy details to create a more compact and legible view.
Grouping in Excel works best for structured worksheets that have column headings, no blank rows or columns, and data is sorted by at least one column.
Select all the data you want to summarize in the group. Then, go to the Data tab > Subtotal. This will bring up a pop-up in which you can select how the data should be grouped and summarized. In the example below, we grouped by the change in OrderYear and summed on Total. This will show us total sales for each year and in total.
Here is the result:
Now with just a few clicks, you can go from meaningful summaries to fine detail on the same spreadsheet.
5. Reveal Formulas
If you want to validate the calculations in your workbook, revealing your formulas is the way to do it.
Select the Formulas tab, then click Show Formulas located in the Formula Auditing group.
Now you can easily check through formulas used in your sheet and also print them. It’s a great way to find errors or to simply understand where the numbers come from.
6. Trace precedent/dependent formulas
Have you ever used a worksheet that someone else set up? If you need to update the formulas and functions but do not know what other calculations will be affected you can spend a lot of time clicking around in the workbook with nothing to show for. Or you’re looking for a miscalculation and need to see where the data is flowing from.
Excel offers a simple way to review which cells are dependent upon others with Trace Precedents, and which cells contribute to others using Trace Dependents.
Both functions only work on the active cell selected so, unfortunately, it’s one cell at a time. Click the Trace Precedents or Trace Dependents button in the Formula Auditing group of the Formulas tab to generate the blue arrows. Data flow follows the direction of the arrow with the blue dot being the precedent and the arrow being the dependent.
Below, Trace Dependents for cell E2 shows it only flows to cell G2.
And Trace Precedents for cell I4 shows cells E2 and F2 are the only cells flowing to it.
These functions work across tabs of the same workbook and different workbooks with one exception. Trace Dependents won’t work for external links for workbooks unless they are open.
7. Create and Manipulate Charts
A hallmark feature of Microsoft Excel, creating charts allows you to visually present your well-formed data. Excel makes the process very easy; highlight a range of data in your sheet, select the Insert tab, then click the See all charts button.
Click the All charts tab, then browse the through the list of chart styles.
You can also hover over a sample to see a preview of what the chart will look like. Once satisfied, click OK to insert the chart into the spreadsheet. If you would prefer to keep it in a separate sheet, select the chart, click Move Chart, select New Sheet then click OK.
8. 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.
9. Five Essential Excel Functions You Should Know – Sum, Average, Max, Min, Count
Excel’s vast true power lies in its functions and formulas. Basic functions let you do quick math operations, while advanced functions let you crunch some serious numbers and perform complex analysis. Just like everyone should know the formatting ropes in Word, you should also know the most popular functions in Excel.
Sum – calculates the total of a range of cells.
Average – calculates the average of a range of cells.
Max – calculates the maximum value in a range of cells.
Min – calculates the minimum value of a range of cells.
Count – calculates the number of values in a range of cells, avoiding empty or cells without numeric data.
Here is how you use a function. Enter the labels for the numbers you would like to produce the calculation for. Select the Function tab, then choose the category of function you would like to apply. Click Insert Function button within the Function Library group or press Shift + F3 on your keyboard. Select the function you need or use the Search for function feature then click OK.
Once you’ve found the function, select it then click OK.
Make any appropriate modifications to the range you are calculating then click OK to apply the function.
10. 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.
11. Apply Special Formatting to Numbers and Currency in Cells
If you need to apply a specific currency value or determine the decimal place for numbers in your spreadsheet, you can use the Numbers tab within the Formal Cells dialog to do so. Select the numbers you would like to format, right click the selection then select the Numbers tab. Select Currency in the Category list, then choose the number of decimal places and currency format.
12. Setting print areas on sheets
Now that you know how to automatically update what prints in the margin, another time-saving trick is set up print areas for your tabs to update what prints within the margins. If you like to keep all your work on one spreadsheet but only need to print out a certain portion, setting the print area is a great way to save time when you hit Ctrl + P.
To set your print area, highlight the cells you want printed. On the Page Layout ribbon, click the drop-down under Print Area and select Set Print Area.
In the same drop-down menu, there’s the option to clear the print area. This is useful if you’ve changed your spreadsheet and want to add more to printable area.
13. Use Cell Protection to Prevent Editing an Area of the Spreadsheet
If you share a workbook with other users, it’s important to prevent accidental edits. There are multiple ways you can protect a sheet, but if you just want to protect a group of cells, here is how you do it. First, you need to turn on Protect Sheet. Click the Format menu then click Protect Sheet. Choose the type of modifications you want to prevent other users from making. Enter your password, click OK then click OK to confirm.
Make a selection of the rows or columns you want to prevent other users from editing.
Click the Format menu, then click Lock Cell.
Anytime a user tries to make edits; they will receive the following error message.
To protect an entire spreadsheet, check out our article for instructions about applying encryption and passwords to your Excel spreadsheets and Office files.
14. Adding dynamic header/footers
While it seems everything is going paperless, sometimes there’s still a need to print. One of the best ways to keep track of what’s printed out of Excel is by adding page numbers, timestamps and file paths to the header or footer. To make sure you don’t have to update these values every time you need to print a spreadsheet, you can add formulas that update automatically.
First, change your view of Excel so you can see the header and footer.
Next, add the following text to your header footer:
| File name | &[File] |
| Sheet name | &[Tab] |
| Page number | &[Page] |
| Date | &[Date] |
Before:
After:
 |
 |
15. Change Text Alignment in Cells
If you need to create a register or labels, you can use the Format Cells dialog to adjust the alignment of text within cells. Select the cells where you would like to apply the formatting, right-click on the selection then click Format Cells….
Click the Alignment tab, then use the mouse to change the orientation of the text or enter a value. When satisfied, click OK.
Text within the cells will now appear slanted.
16. Adding frequent actions to quick access toolbar
Looking around any version of Excel you’ll notice there’s an endless array of tools at your fingertips. But most beginners use just a few of these repetitively. Instead of switching between the different tabs of the ribbon every time, you can add your personal favorites to what’s known as the Quick Access Toolbar.
Microsoft offers several ways to make this happen but the easiest way is to right-click on what you want to add and select “Add to Quick Access Toolbar”. One of my personal favorites in my QAT is the Sort & Filter function as I usually work with a lot of data.
Before:
After:
Once you have your favorites added, you can also rearrange your QAT shortcuts by right-clicking on it and then clicking “Customize Quick Access Toolbar…” in the menu. With your customized QAT you’ll be blazing through your next spreadsheet with ease.
17. Freeze Panes
If you want to scroll through a spreadsheet without losing focus on a particular part of the sheet or data, the Freeze Panes function is the perfect way to do it. Select the row or column where the data begins in the sheet.
Select the View tab, click the Freeze Panes menu then click Freeze Panes.
When you scroll, your headings or columns will remain visible.
18. Paste Special settings
There are so many different ways to accomplish tasks in Excel. Consider the simple copy and paste functions. As if Ctrl + C and Ctrl + V didn’t make copying easy enough, Microsoft built Paste Special.
Here’s how it works. Say you want to copy and paste something but not everything like a just a number or format. Instead of copying everything and removing what you don’t want, you can use Paste Special. To do this, copy your information as usual, but instead of using Ctrl + V, right click and select Paste Special from the menu.
Some of the more popular Paste Special options include:
- Values – use this option if you only want to paste the text you see in the cells. It will leave all formatting unchanged.
- Formulas – Need to keep that formula but don’t need the formatting? Formulas allow you to do just that.
- Formats – Use this option to duplicate formats while leaving existing values and formulas.
- Column Widths – For when all your columns need to be a uniform width, this option saves a ton of time instead of adjusting them manually.
19. Resize Columns and Rows
The Excel default cell height and width is hardly one size fits all. Chances are, you’ll need to adjust the column width and row height to accommodate your data. To do that, click the column or row, select the Home tab, then click the Format button within the Cells group. Choose whether you want to adjust the height or width.
Enter the amount then click OK. The column or row should be adjusted to the exact measurement.
You can also manually resize columns and rows using the mouse. Place the mouse pointer between the column or row, click the left mouse button, observe the floating balloon then drag and expand until the desired size is achieved.
And here’s a handy tip: simply double-click the right border of a column to auto-size the width to the data.
20. Filtering data
Speaking of a lot of data, Microsoft has amazing tools to handle it all, which probably explains why it became the most important workplace computer program around the world. Excel’s basic function for handling all this information is the Sort & Filter function. This is so useful because it allows you to rearrange and summarize data in a way that’s actually useful.
To do this, highlight all the data you want included in the filter (a quick way to do this is Ctrl + A). Then, click the funnel icon in the top left of the Home ribbon.
Now there will be a drop-down arrow at the top of each column in the filter. Here, you can select different values in the table and sort them. For example, let’s say you wanted to see how many orders were made in a certain time frame. Just filter on the date column and select your desired time frame.
From our example, you can quickly see there were only two orders in September 2014.
21. Protect sheets and workbook
When it comes time to send your Excel spreadsheet, it’s important to protect the data that you’re sharing. You might want to share your data, but that doesn’t mean it should be changed by someone else. Luckily, Excel has built-in features to protect your spreadsheets.
To protect a sheet, click on the Review tab in the ribbon then Protect Sheet. This will bring up a pop-up box where you’ll add the unlock password and what functions users can still perform while the sheet is protected.
Once you click Ok, you are prompted to confirm the password and save the workbook. Now, if anyone tries to alter the information they will need that password. If you have a bunch of sheets you want protected, click on Protect Workbook follow the same procedure.
