Schema della sezione

  • Introduzione

    Minimizza tutto Espandi tutto

  • Textbook

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

  • Course program and outlines

  • Two introductory Powerpoint presentations on Excel

    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

    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.

    • Area and population data File
    • Formatting an Excel 2007 worksheet File
    • The following Excel file contains some examples on number formatting:
    • Examples on number formatting File

  • A new tool: Tables

    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. 

    • Excel 2007 Tables File

  • Introductory exercises

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

    • An introductory simple exercise File
    • Another introductory exercise File

  • Formulas

    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.

    • Enter formulas File
    • Data for the presentation on formulas File

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

    • Some Excel functions File
    • Many other functions are available in Excel. The following file contains a list of Excel functions and a brief description of their use.
    • List of Excel functions File

  • Naming ranges and referring to data in Tables

    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.

    • Naming ranges File
    • The Excel file which follows contains an example where some named ranges are used.
    • An example of named ranges File
    • 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.
    • Referring to data in Tables File

  • Exercise: calculating averages and percentages

    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.

    • Foreign students in Italy 1999 - 2000 File

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

    • Averages and percentages File
    • Percentages to total File
    • Summary File

  • Exercise: using functions requiring criteria

    Exercise: using functions requiring criteria
    • In the Excel file wich follows you can find data about the urban population in Italy in various ages.
    • Urban population in various ages File
    • 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.
    • Analysis n.1 of urban population File
    • Analysis n.2 of urban population File

  • Charts

    Charts
    • Watch the following 'official' Microsoft presentation on Excel charts.
    • Create a chart File

    • After watching the presentation, do the following exercise. You find the data in the Excel file below the exercise. 

    • A worksheet for the head of your office File
    • Data for the exercise File

  • An analysis of customers' arrivals using charts

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

    • Analysing arrivals of customers File

  • Plotting functions

    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

    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.
    • Dates and time in Excel File
    • Some examples on using dates and times in Excel File

    • For a complete list of date and time functions in Excel, see the Excel help or the following link:

    • Tutorial on Microsoft Excel on www.bettersolutions.com URL

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

    • An exercise on date formulas in Excel File

    • You will probably find the following resource useful:

    • Calculating an age between two dates using the DATEDIF function URL

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

    • It's your birthday today! File

  • Exercises on dates and time

    Exercises on dates and time 

  • Importing textual data

    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.

    • Import text into Excel File
    • 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.
    • Proportion of European population aged 15-24 (txt) File
    • Proportion of European population aged 15-24 (csv) File
    • Proportion of European population aged 15-24 (prn) File

  • Using Excel functions to look up values

    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.

    • Information about functions that look up values File
    • Some lookup examples File

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

    • Were the Beatles good students? File

  • The Goal Seek tool

    The Goal Seek tool
    • Do the exercises that follow.
    • Building a wall Pagina
    • How much can I spend? Pagina

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

    • The Goal Seek tool File

  • Something magic: Pivot Tables

    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. 
    • Get started with Pivot Tables File
    • Data for the Pivot Tables presentation File
    • After watching the presentation, do exercise n. 13 again, using Pivot Tables and Pivot Charts.

  • Some exercises

    Some exercises

  • Exam - 08/01/2009

    Exam - 08/01/2009

  • Exam - 22/01/2009

    Exam - 22/01/2009

  • Exam - 05/02/2009

    Exam - 05/02/2009

  • Exam - 03/06/2009

    Exam - 03/06/2009

  • Exam - 17/06/2009

    Exam - 17/06/2009

  • Exam - 30/06/2009

    Exam - 30/06/2009

  • Exam - 09/09/2009

    Exam - 09/09/2009