Schema della sezione

    • Timetable
      Recommended Textbook
      Tues. 11.15 - 12.45 Pc Lab
      Microsoft Office Excel 2007 Bible
      Fri.      9.15 - 10.45 Pc Lab
      John Walkenbach

      Wiley, 2007

    • Please, take note that from January 2010 the exam will be in oral/practical form.
  • Two introductory Powerpoint presentations on Excel

    Here you find two 'official' Microsoft presentations on Excel.
    After opening Excel, watch them and try to repeat the actions which are illustrated inside step by step.
    Data to reproduce the actions contained in the second lesson of the second presentation are contained in the Excel file below.
    In this way, you will learn some basic Excel functionalities and  become familiar with some fundamental Excel menus and commands.

  • An introductory exercise on formatting
    • Here below you find an Excel workbook containing some unformatted data and a Powerpoint presentation on some basic formatting Excel commands.

      Follow the instructions contained in the presentation to format the data in the workbook.

    • The following Excel file contains some examples on number formatting:
  • A new tool: Tables
    • Excel 2007 introduces a new tool to help the user to create and to manipulate tables of data, called 'Tables'.

      The following pdf document explains the main functionalities of this tool. Read it (the first six pages are enough) and do the previous exercise again, using Tables. 

  • Introductory exercises
    • Now, look at the following pdf files and try to reproduce their contents by means of Excel workbooks, using the notions that you learned before. (You can choose your own colours and numbers, if you like.)

  • Formulas
    • Here you find an 'official' Microsoft presentation on Excel formulas.

      Remember that formulas are what make a spreadsheet program really useful. Formulas let the user calculate results from data stored in the spreadsheet. They usually refer to cells and changing data in these cells make formulas change dynamically.

      After opening Excel, watch the presentation and repeat the actions which are illustrated inside step by step. For this, feel free to use the Excel workbook provided below.

    • The following Excel file presents some examples where some important Excel functions are employed. Examine the use of these functions very carefully.

    • Many other functions are available in Excel. The following file contains a list of Excel functions and a brief description of their use.
  • Naming ranges and referring to data in Tables
    • A range is the term employed by Excel to define a group of cells.

      For instance, B1:D5 represents a group of 15 cells (B1, B2, B3, B4, B5, C1, C2, C3, C4, C5, D1, D2, D3, D4, D5).

      Many functions accept ranges as argument. For instance: SUM(B1:D5) returns the sum of the values contained in the 15 cells above.

      Ranges can be named. To discover the way to accomplish that, read the following pdf file.

    • The Excel file which follows contains an example where some named ranges are used.
    • Data in a table (i.e. data formatted as Table) can be referred to by using the table name and columns headers. An example is contained in the file which follows.
  • Exercise: calculating averages and percentages

    • The Excel workbook below contains the number of foreign students enrolled in Italian universities during the academic year 1999-2000.

    • Modify the workbook according to the pdf files which follow (use for this three worksheets in the same workbook).

  • Exercise: using functions requiring criteria
    • In the Excel file wich follows you can find data about the urban population in Italy in various ages.
    • Try to reproduce the following worksheets by using the data contained in the previous file. A good knowledge of the syntax of functions like COUNTIF, SUMIF, COUNTIFS, SUMIFS, ..., is required.
  • Charts
    • Watch the following 'official' Microsoft presentation on Excel charts.
    • After watching the presentation, do the following exercise. You find the data in the Excel file below the exercise. 

  • An analysis of customers' arrivals using charts
    • Experiment different (but reasonable) kinds of charts doing the exercise which follows. (Hint: use the SUMIF function to build new tables with the data requested.)

  • Plotting functions
    • Plot the graph of the function f(x)=x2+1 in the interval [-2,2]. (Hint: build a convenient table of values between -2 and 2...)
  • Dates and time
    • Excel employs serial numbers to represent dates and time. You find a brief description of this representation and some examples in the following documents.
    • For a complete list of date and time functions in Excel, see the Excel help or the following link:

    • Now try to create a worksheet as suggested by the following pdf file, by using convenient Excel functions. Try to discover them! (Some hints are given.)

      Notice that data contained in orange cells are typed by the user, whilst those in yellow cells are calculated by means of formulas.

    • You will probably find the following resource useful:

    • Notice also what happens in case of user's birthday. (Hint: explore the use of logical functions (for instance IF and AND) in Excel.)

  • Exercises on dates and time 
  • Importing textual data
    • Data are often available as text. Excel is able to import such kind of data, but some specific rules apply in order to convert them into a readable form. Read the following pdf file to understand the basic principles on this topic.

    • To try the Excel's functionalities in importing data, you can use the files which follow. They contain the proportion of population aged 15-24 years in the European countries, in plain text, CSV (Comma-Separated Values) and 'text separated by spaces' format respectively. Use the preview window of the Excel's importing procedure to verify that the data are correctly imported.
  • Using Excel functions to look up values
    • Excel supplies us with many functions to look up values in tables of data, for instance: VLOOKUP, HLOOKUP, LOOKUP, MATCH, INDEX, and others.

      A brief explanation on these functions, together with examples, can be found in the files which follow. You can also read the Excel help system.

    • Using the right Excel functions, try to reproduce the Excel worksheet that is represented in the pdf document which follows.

  • The Goal Seek tool
    • Do the exercises that follow.
    • After doing the exercises, read the following pdf document, which explains the Excel Goal Seek tool , and try to do the second exercise again, by using that tool.

  • Something magic: Pivot Tables
    • A pivot table is a dynamic and interactive summary report which is generated from data. With only a few mouse clicks, data can be presented and summarised in many different ways. Watch the following 'official' presentation on this powerful tool. Data to try are provided as well. 
    • After watching the presentation, do exercise n. 13 again, using Pivot Tables and Pivot Charts.
  • Some exercises
  • Exam - 08/01/2009
  • Exam - 22/01/2009
  • Exam - 05/02/2009
  • Exam - 03/06/2009
  • Exam - 17/06/2009
  • Exam - 30/06/2009
  • Exam - 09/09/2009