3 Tips from an Expert In Excel

1
3 Tips from an Expert In Excel

• Website
• http//www.p2w2.com/chaitanya_sagar/

2

• Six months ago, I had never used Excel before,
  and today, Im an international champion. 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.

3

• With so many features and capabilities, Excel can
  be intimidating at first, but once you dive in,
  youll find its a lot less complicated than it
  seems. Im happy to share three of my favorite
  Excel tips with readers because I think everyone
  can benefit from the technology its an
  important skill set for success in school and the
  workforce. Get started with the following tips to
  become an expert in excel

4
The Fill Handle

• The Fill Handle is one of my favorite features in
  Excel because its 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. Heres
  how to get started

5

• 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 Microsofts
  term for the automatic completion of a series
  such as dates, days of the week, days of the
  month, ID numbers, etc.
• 2. 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.

6
(No Transcript)
7

• 3. 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 users 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
  cells formatting.

8

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

9
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

10
(No Transcript)
11

• Highlight the cells containing data that needs to
  be separated.
• 2. 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.
• 3. Choose Delimited, and click next.
• 4. 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.
• 5. 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.
• 6. Click finish to apply the separation.

12
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

13

• 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 C4G2. 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

14

• 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 (G2). This tells Excel
  to always reference G2 when calculating the
  bonus.

15

• 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,
  youll 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.

16
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 years Microsoft
  Office World Champions! In the meantime, the
  Expert in Excel and Office.com are great
  resources if you ever find yourself stumped.