Computer Literacy 2 / BMEEPAGG201

Excel bases

Table of contents:

  1. Timetable: simple data input, formatting
  2. Plaster cubes: basic formulas, absolute references, units
  3. Scale conversion: basic formulas, mixed references, units

Introduction

Familiarize yourself briefly with the following concepts: cell, column/row, header, menu bar, name box, formula bar, data types, formatting options.

1. Practice the selection modes:

2. Practice copying of data by dragging down for the following data types:

3. Practice the options for navigating in the document:

4. Practice the keyboard shortcuts:

5. Try out the formatting options, the Format Painter and the Merge & Center.

When solving the excercises, try to use as many of the above methods as possible!

1. Timetable - data input, formatting

Inspiration: Department timetable

Excercise: Create your own timetable!

Similar to the example, create the headers of the timetable by auto-filling. Fill in the data, merge cells, and format to your liking.

Export your solution in PDF format to fit on 1 A4 page in landscape orientation.

2. Designing plaster cubes

Excercise: We would like to produce 10 plaster cubes with a side length of 1 cm ... 10 cm. Calculate how much plaster we will need!

To get the solution, we will need to calculate the volume of the cubes and know the density of the plaster.

The mass can be calculated from the density and volume of the plaster: mass = volume*density.

NOTE: If you now copy the contents of cell D5 by dragging down, the cell references will move down. Since all cells have the same density, we need to prevent the C2 reference from moving down as well. We can also prevent separately the column reference (letter) and row reference (number) from changing by using the $ sign.

HINT: The $ signs are easily inserted by clicking on the reference in the formula and pressing F4 By pressing the button repeatedly, you can switch between fixing only the column, only the row, both or neither.

Use the SUM formula to sum up the masses. When using formulas, the cell contents always start with "=". The input data must be placed in parentheses after the formula name.

The contents of cells can be displayed in different ways. Units of measure can be displayed next to the numbers by setting a unique number format. This can be done by selecting Custom from the Home / General drop down menu / More number formats menu item. For the format code, 0 represents the number, and then we need to write the unit of measure between quotation marks. It is also advisable to insert a space before the unit of measurement. Some examples:
    0" m"     -     we get the final result rounded to the nearest whole number e.g. 5 m
    0,00" m"     -     we get the final result rounded to two decimals e.g. 5,00 m

HINT: Never write the units of measurement manually next to numbers, as the program will then treat those cells as text and cannot perform the operations.

HINT: How can we specify superscript units (e.g. cm³)? When entering the number format, there is no formatting option, so we can only insert "³" as a special character. The characters can be copied from anywhere, e.g. you can paste them as a symbol into a Word document and copy them from there. The fastest way, however, is to search the Internet for "superscript 2" or "superscript 3" and copy the character from one of the first pages.

You can compare your solution with the solution key.

3. Scale conversion - format, formulas, absolute references

Excercise: when drawing by hand (or making a model), we create a scaled-down drawing (or model) instead of the actual size. The scale shows how many units 1 unit of length on the drawing (or model) corresponds to in reality. Make a cheat sheet for the conversion that gives you how many cm 1...20 m corresponds to in 1:200, 1:100, 1:50, 1:10!

First we fill in the table with the data, but we do not yet care about the units of measurement. Let's create the headers:

Then think about how we can switch between the two numbers. Consider a scale of 1:100. By definition, 1:100 means that what is 1 unit on paper is 100 units in reality, i.e. 100 times as big. But here we have the opposite question: how many units is on paper something that is 1 unit in reality?

on paper       in reality
1 unit             100 units
x = ? unit             1 unit
The answer: x=1/100 units, i.e. the size on paper is 1/100 times as big. Since we want to give the final result in centimetres, not metres, we need to multiply this by 100. This means that for each row in the table we need to do the following operation: (header of the given row)/(header of the given column)*100.

If you formulate the formula correctly, you only need to enter it once, then you can copy it to the other cells. The copy function works in such a way that if you copy the formula horizontally/vertically, the reference also moves horizontally/vertically. For the row header, the reference should not move to the right when copying to the right, and for the column header, vertical movement should be disabled. Let's copy the formula down and to the right, click into the cells and see what's wrong with the references!

The unit of measurement is set as a number format:

In the same way display the unit of measurement for the row headers too.