Sunesis Consulting

Microsoft Excel Basics: A Quick Reference Guide

Microsoft Excel

Once you get a hang of Microsoft Excel it will be the easiest tool to use. You can shorten the time you do tasks, create reports quickly and draw conclusions from data with ease. For faster learning of advanced Excel functions and formulas visit:

Training

Get started with this Microsoft Excel guide today:

  1. Sum

Here is how to add up numbers in Excel:

Fruit

Amount

Apples

50

Oranges

20

Bananas

60

Lemons

40

  
  • Select the cells with numbers you want to add under the amounts for fruit.
  • Type =SUM(D4:D7), and then press enter. When you’re done, you’ll see the result of 170.
  • Here’s another way to add, using a shortcut key. Select the yellow cell under the amounts for meat.
  • Press [Alt]+[=]. Then, press Enter.
  • Now add only the numbers over 50. Select the last yellow cell. Type =SUMIF(D11:D15,”>50″) and then press Enter. The result is 100.
  1. Fill

This:

Plus this:

Equals:

Plus this:

Equals:

50

50

100

75

175

50

60

 

75

 

50

70

 

75

 

50

80

 

75

 
  • Click the cell with the number 100.
  • Rest your cursor on the lower-right corner of the cell until
    it becomes a cross:
  • Click the cross and drag down three cells. Excel will automatically fill the cells with the totals: 110, 120, and 130. People call this “filling down.”

This:

Plus this:

Equals:

Plus this:

Equals:

50

50

100

75

175

50

60

110

75

185

50

70

120

75

195

50

80

130

75

205

200

    
  • Click the yellow cell with 200, and fill again but this time drag the fill handle to the right to fill the cells. This is known as “filling right.”
  1. Split

Email

First name

Last name

Nancy.Smith@contoso.com

 

Smith

Andy.North@fabrikam.com

  

Jan.Kotas@relecloud.com

  

Mariya.Jones@contoso.com

  

Yvonne.McKay@fabrikam.com

  
  • In the cells under First name, type the first names that are in the Email column: Nancy, Andy, and so on.
  • When you see the faded list of suggestions, press Enter right away. This list of suggestions is called Flash Fill. Flash Fill detects when you type a consistent pattern, and provides suggestions to fill the cells with. When you see the faded list, that’s your cue to press Enter.
  • Try another way to Flash Fill: Click the cell with Smith.
  • Click Home > Fill > Flash Fill. Now the last names are in their own column.
  1. Transpose

With transpose you can rotate and columns and rows

 

Item

Bread

Donuts

Cookies

Cakes

Pies

Amount

50

100

40

50

20

  • Click and drag to select the two rows of cells from Item, to 20.
  • Now you’ll copy the cells. Press [CTR]+[C]
  • Click any cell below the Item Amount.
  • On the Home tab, click the arrow under the Paste button.
  • Click Paste Special, and then at the bottom, click the checkbox for Transpose. Click OK.

 

  1. Sort and Filter

Department

Category

Oct

Nov

Dec

Meat

Beef

$90,000

$110,000

$120,000

Bakery

Desserts

$25,000

$80,000

$120,000

Produce

Fruit

$10,000

$30,000

$40,000

Produce

Veggies

$30,000

$80,000

$30,000

Deli

Salads

$90,000

$35,000

$25,000

Meat

Chicken

$75,000

$82,000

$2,000,000

Bakery

Breads

$30,000

$15,000

$20,000

Deli

Sandwiches

$80,000

$40,000

$20,000

  • Let’s say you want the departments in alphabetical order. Click in the Department column, and then click Home > Sort & Filter > Sort A to Z.
  • Sort December’s amounts from largest to smallest. Click any cell in the Dec column, and then click Home > Sort & Filter > Sort Largest to Smallest.
  • Now you’ll filter the data so that only the Bakery rows appear. Press CTRL+A to select all the cells, and then click Home > Sort & Filter > Filter.
  • Filter buttons appear on the top row. On the Department cell, click the filter button  and then click to clear the Select All checkbox. Then, click to select Bakery.
  • Click OK and only the Bakery rows appear. Now clear the filter by clicking the filter button   for Department and then click Clear filter…
  1. Tables

A table gives you special features and conveniences. Here’s how to create one:

Department

Category

Sales

Produce

Veggies

$1,000

Produce

Fruit

$2,000

Bakery

Breads

$3,000

Bakery

Desserts

$1,000

Deli

Sandwiches

$2,000

Deli

Salads

$3,000

Meat

Beef

$4,000

Meat

Chicken

$8,000

  • Click inside the data to the right, and then click Insert > Table > OK.
  • Now you have a table, which is a collection of cells that has special features. For starters: A table gives you banded rows for easier reading.
  • You can also create new rows easily. In the empty cell under Meat, type some text and then press Enter. A new row for the table appears.
  • You can also create columns easily: In the lower-right corner of the table, click the resize handle and drag it to the right 2 columns.
  • Notice how the two columns are created, formatted, and the text Jan and Feb are filled for you.
  1. Drop down in lists

Drop-down lists make data entry easier for people. Here’s how to do one:

Food

Department

Apples

 

Beef

 

Bananas

 

Lemons

 

Broccoli

 

Kale

 

Ham

 

Bread

 

Chicken

 

Cookies

 

Cakes

 

Pies

 
  • We want only three department names to be valid entries for each of the foods on the right. Those departments are Produce, Meat and Bakery.
  • Click and drag to select the yellow cells under Department.
  • On the Data tab, click Data Validation. Under Allow, click List.
  • In the Source box, type Produce, Meat, Bakery. Make sure to put commas in between them. Click OK when you’re done.
  • Now click the yellow cell next to Apples, and you’ll see a drop-down menu.
  1. Analyze with charts

You can always use the Insert tab and create a chart. But here is another way to make a chart, using the Quick Analysis button. This time though, we’ll use the keyboard shortcut:

Department

Category

Oct

Nov

Dec

Bakery

Breads

$30,000

$15,000

$20,000

Bakery

Desserts

$25,000

$80,000

$120,000

Deli

Sandwiches

$80,000

$40,000

$20,000

Deli

Salads

$90,000

$35,000

$25,000

Meat

Beef

$90,000

$110,000

$200,000

Meat

Chicken

$75,000

$82,000

$150,000

Produce

Veggies

$30,000

$80,000

$30,000

Produce

Fruit

$10,000

$30,000

$40,000

  • Click a cell inside the data to the right, and then press CTRL+Q
  • On the panel that appears, click Charts.
  • Click the first Clustered… button.
  • A new clustered column chart appears. Move it anywhere you’d like. Notice that each product has three columns, one for each month of sales.

Courses

We have made it easy for you to become an expert in data analysis using Excel. Find out more here: https://sunesiskenya.com/training/

Tags :
Share This :

Leave a Reply

Your email address will not be published.