Basic skills Excel 2nd year students IHMS

• Informatiekunde
• MBO
• Studiejaar 2

Excel

Workshop basic skills Excel (Office 365) 2nd year

What are you going to learn?:

• What can you use Excel for?
• How to put information in Excel and how to adapt this info?
• How to work with formulas in Excel? 
• Create a graph/chart
• Filtering and sorting

What can you use excel for?

Excel

Excel is a calculation application that you can use to:

• calculate formulas
• create overviews of numbers (like amounts of money) 
• filter and sort data
• convert tables to charts

At this school, you use Excel in subjects like Finance and RDM.

To open Excel: go to "Startprogramma voor Apps" at the homepage IHMS.

These are the "nine dots" on the leftside of Mondriaan Portaal.

                                                                Choose Excel, and then

                                                       Nieuwe lege werkmap (New empty                                                                                                                          work-

                                                                                                                  book)

Title bar

Change the filename of your Excel file to: Workshop Excel - 'Name - class' and make sure it will be saved in your OneDrive map 'Mijn Bestanden' (via Bestand (File) - Opslaan als (Save as) -  Naam wijzigen (Change name))

Columns and rows

• The columns have letters, the rows have numbers
• Click in column A, row 1 (cell A1) and type the number 1
• After this, type the numbers 2 to 5 in the rows 2 to 5 like you see it in the image on the left.

Formula bar

• On het left in the formula bar you can see which cell you selected. 
• The name of the cell is the combination of  the letter of the column and the number of the row (A5 in the example on the left)
• In the bar you can see the content of the cell (in this case the number 5)

Fill handle

• With the fill handle you can quickly and easily copy data to other cells. You can also expand data with the fill handle.
• Use the fill handle by floating with the cursor on the edge in the lower right corner in the selected cell, you will see a black +

Fill handle

• Use the fill handle at cell A1, press and hold down the left mouse button, then drag the fill handle to  the right till column E
• Do the same with cell A2, but next to the left mouse button, also press and hold down the CTRL button.

Fill handle

• Do you see the same as in this image? Then you succeeded. 
•  This does not only work with numbers, but also with days of the week and dates

Select

• You can select whole rows and columns to edit them. You can do this by floating with the cursor next to the row or above the column you want to select and click on it (left mouse button) 

Select

• Select rows 3 to 5, click your right mouse button and choose 'Rijen verwijderen' (delete Rows)
• Select column A, click your right mouse button and choose 'Kolommen invoegen' (Insert columns)
• Your image now looks like you see next to this.

Edit

• Write the following words in column A, cell 4 to 7: Sum, Subtract, Multiply and Divide
• Select column A and make it bold (ctrl+b or click      )
• Make column A just wide enough for these words by doubleclicking with the cursor on top on the border between A and B

Formulas

As mentioned before, you can use Excel to calculate formulas. To 'show' Excel you will put a formula (calculation) in a cell, you always start with a "="

Other signs you will probably use often are + (summarize), - (subtract), * (multiply) and / (divide). If you make long formulas, you can put some parts of the formula between brackets.

Formulas

• We're going to sum the content of cells B1 and B2
• Click on cell B4 and type "="
• Then click cell B1, then you type "+" and then you click cell B2
• Click Enter

Which number is now shown in cell B4?

Formulas

• Now use the fill handle (dia 11) to copy the formula to cells C4 to F4
  
• In cell B5 we now put the formula for cell B1 minus cell B2
• Multiply cell B1 with cell B2 in cell B6
• Divide cell B1 by cell B2 in cell B7

Formulas

• Copy the formulas of cells B5 to B7 horizontally to the rest of the cells in row 5 to 7 (to and including column F) with the filling handle
• If this went well, it now looks like the image on the left!

Cell properties

Sometimes you want your numbers to be shown with a maximum of one decimal, or you want to show date and time in a certain way. Another time you maybe want the content of your cell to be seen as text instead of numbers. You can change this. 

Cell properties, change number of decimals

• Click on cell B7 and while you press and hold down the left mouse button, move to the right to and including cell F7
• Click the right mouse button on the grey area and then click 'Getalnotatie'
• Choose 'Getal' (Number) and change "aantal decimalen" to 1

Which number is now shown
in cell F7?

Fx

Excel contains a few ready-made formulas, so you need to type less; if you have a lot of numbers in your Excelsheet, you can calculate with this very quickly.

 You can find these formulas in the formule bar by clicking on 'Fx'.

Fx

• Type the word "Sum" in cell A9 and the word "Average" in cell A10
• Click on cell B9 and then on "Fx"
• Select 'Som' and click on OK

Fx

• Select cells B4, B5, B6 and B7 by moving over these cells while you press and hold down the left mouse button
• If the cells are selected, press Enter

Which number is now shown
in cell B9?

Fx

• Now in the same way: insert in cell B10 the formula to calculate the average (gemiddelde) of cells B4 to B7
• Copy the formulas to the rest of the cells with the fill handle
• Ensure that all numbers only have 1 decimal, by using "Getalnotatie"

Which number is now shown
in cell D10?

Worksheet

• In an Excel file you can have more than one worksheets; the first worksheet is automatically named 'Blad 1' (in the status bar)
• Click the right mouse button on 'Blad 1'  and then click 'Naam wijzigen' ('Change name')
• Change the name to 'Formulas' 

You have now made your (probably) first table in Excel and used formulas, well done!

 

Open the Excel-file

"Excel workshop - exercise" (click in Status bar on worksheet Weekly turnover)

Fill cells B3 to H3 with all days of the week (in Dutch) by using the earlier learned Fill handle. 

(start in B3 with 'maandag')

Now select cell B9 and calculate the total turnover for 'maandag' by clicking the AutoSom-button:

You can do this the same way for the other days of the week; or you can use the Fill handle from B9 to cell H9.

Now select cell L9 and calculate with

the total weekly turnover. 

How much is the total turnover for the week (cel L9) ?

Selecteer het blok cellen van A1 t/m L9 en  

Select the block of cells from A1 to L9 and go to Getalnotatie.

Choose "Valuta" and then OK. All numbers are now displayed as amounts 

in Euros.

Create a graph/chart

• To create a graph, you first need to select from the table which information you want to see in the graph 
• Select the block of cells from A1 to H9
• In the Ribbon, click on 'Invoegen' (Insert) and then on the left icon of the  bar graph.

Modify the Graph

Excel has now automatically created a graph. When you click the right mouse button on the graph, you can change different things. Via "Opmaak", click on "Grafiektitel" (title) and change this to "Turnover per day per type of turnover".

For now, let's leave it at that.

Now click on Worksheet "Filtering en sorting" in the Status bar 

We're going to practice Filtering and Sorting. When Sorting, data is displayed in ascending or descending order. If you use Filter, some data is omitted (filtered), which makes other data more clearly displayed.

Select row 1.

Then click this button 

(in Ribbon Startpagina is selected)

Then choose Filteren.

A Filter is now shown above all columns.

Click on the down arrow on the right, next to "Name guest" (cell B1).

Choose "Sorteren van A naar Z"

Which guest is now shown
on top of the list?

Now we want to know how many people have paid in advance.

Click on the down arrow on the right next to "Prepaid" and choose "Sorteren van Z naar A" (we want the "yesses" at the top). Then click on  Filter and make sure only "yes" is checked.

Click OK.

How many guests have paid in advance?

How many people chose for menu 1?

Remove the Filter for "Prepaid". Do this by selecting column D, then clicking on              and then choose Wissen (Clear/Erase). We now want to know how many guests chose for menu 1.

Click on the down arrow to the right of "Order", choose Filter and only check "Menu 1". Click OK.

How many guests chose menu 1?

Next to making a table and working with formulas, you now have learned how to create a graph and you practiced a little with Sorting and Filtering.

By working with Excel, you will get to learn more and more about this handy application.

Good luck in your 2nd year at the International Hotel and Management School!