Coverage
3 Tips from an Excel Expert
By Nina Joseph
www.hackcollege.com
Six months ago, I had never used Excel before, and today, I'm an international champion. This month I won third place in the 2013 Microsoft Office Specialist World Championship, where I represented the United States with five other students in the ultimate productivity competition. I competed against 100 finalists, who outmatched more than 344,000 total participants in qualifying rounds across 90 countries. My story is a testament to how easy it is to learn (and master) Excel.
With so many features and capabilities, Excel can be intimidating at first, but once you dive in, you'll find it's a lot less complicated than it seems. I'm happy to share three of my favorite Excel tips with HackCollege readers because I think everyone can benefit from the technology — it's an important skillset for success in school and the workforce. Get started with the following tips, and if you have questions, just leave a comment below or tweet me @Nina_Joseph2, using the hashtag #ExcelExpert.
The Fill Handle
The Fill Handle is one of my favorite features in Excel because it's a great time-saver. Located in the bottom right-hand corner of an active cell, the Fill Handle is a small, black square that can be used to copy the contents of a cell, or range of cells, into adjacent rows or columns. Here's how to get started:
- Click on the cell you would like to fill or copy. This should highlight the cell with a thick black border, making it active. "Fill" is Microsoft's term for the automatic completion of a series such as dates, days of the week, days of the month, ID numbers, etc.
- When you move your mouse over the Fill Handle, a small black cross appears. Click and drag the cross down a column or across a row to complete the series.
View full size
- Below the bottom right-hand corner of the filled series, an icon appears. When selected, a menu expands from the icon which provides options that are specific to the user's series. Within this menu, choose an option that best fits your desired effect.
- Copy Cells: This copies the selected cell(s) and the formatting into the adjacent cells. In my example, Copy Cells would result in all seven cells containing the text "Monday" as well as the formatting of the cell.
- Fill Series: This option will complete the series set by the selected cell(s). In my example, this would fill the rest of the days of the week.
- Fill Formatting Only: This copies only the formatting of the selected cell(s) into the adjacent cells. In my example, this would fill the black, size 11 Calibri font into the other cells.
- Fill Without Formatting: This option is the opposite of "Fill Formatting Only." It will copy the text from the selected cell(s) but not the format. In my example, this option would result in the text "Monday" being copied without the cell's formatting.
The Fill Handle can be useful when a user needs to fill a long series. It saves time by preventing the user from having to type out each element of a series. This command has definitely helped me in the past by allowing me to quickly fill in data, formulas, or formatting in worksheets of all sizes, which ultimately increases efficiency.
Text to Column
A great tip for organizing your data by different components in Excel is the Text to Column command, which makes it easy to separate data within a cell. Follow these quick steps to use Text to Column:
- Highlight the cells containing data that needs to be separated.
- Click on the Data Ribbon. In the Data Tools group of the Data Ribbon, select the Text to Column command. This causes the Text Wizard to launch.
- Choose "Delimited," and click next.
- The next step allows the user to select how the data is separated. In my example, the first and last names are being separated by spaces, so in the "Delimiters" box, I would check "Space." Once the delimiter has been selected, click next.
- The next step allows the user to format each new column with a different data format and choose where the data should be placed. This step is not necessary in my example; however, this step could be useful if a user wanted to separate a date such as "January 10, 2009" into month, day, and year columns because "January" could be formatted as text and "10" and "2009" could be formatted as numbers.
- Click "finish" to apply the separation.
Absolute References
This is one of the easiest but little known tricks in Excel. Absolute References are used in formulas to prevent an included cell reference from shifting as formulas are filled to adjacent cells. Here is an example of a situation when an absolute reference comes in handy:
In the example above, each employee will receive a bonus equivalent to 5% of their salary. Because each employee has a different salary, each bonus will be a different amount. In order to calculate the bonus, the wage must be multiplied by 5%. So, in cell D4, the formula should be "=C4*G2." We can then fill this formula to the end of the column; however, if G2 is not made into an absolute reference before it is filled, the cell reference will shift downward with the fill. To create an absolute reference:
- 1. Double-click on the cell that contains the formula; this places the cursor in the cell. Move the cursor so that it is within the cell reference that must be absolute. In the above example, that would be cell G2.
- 2. Press the F4 key once. This inserts dollar signs into the cell name ($G$2). This tells Excel to always reference G2 when calculating the bonus.
Understanding absolute references and how to create them is imperative when using formulas in Excel. They allow formulas to copy correctly within a workbook, and in the example above, you'll see how important it is to reference cell G2 rather than typing 5% into the formula because it allows the bonus percentage to change while keeping the bonus amounts accurate.
What are You Waiting For?
Becoming familiar with Excel can save you a lot of time, and the best way to become familiar is to experiment. Who knows ─ with enough practice you could even be one of next year's Microsoft Office World Champions! In the meantime, the Excel Blog and Office.com are great resources if you ever find yourself stumped.