MS Excel 2010

Microsoft Excel (MS Excel) is one of the most popular spreadsheet applications and has become for many the “default” software to handle data, numbers, graphs and analysis. Other spreadsheet applications such as Calc (Open Office/Libre Office) are certainly as good as (or even better than) MS Excel. bioST@TS makes the choice to focus first on MS Excel 2010 because it is available to all employees and students at the University of Bergen and it is part of the default configuration on all IT-managed PCs (client PCs) on our campus.


chart tools
To understand how to export charts, let’s work on the following example: Right-click on the chart area to display the contextual menu and select Copy. Alternatively, if the chart is already selected, use the shortcut CTRL+C to copy the object to the clipboard. Go to MS Word or MS Powerpoint, […]

1. How to export a chart


paste options table
To understand how to export tables, let’s work on the following example: Most of what has been explained previously regarding charts is actually applicable to tables. Again you will find out that there is a possibility to import an MS Excel table into MS Word (or others) as a picture, as […]

2. How to export the table





Axis Labels
Since it is your first chart, we’ll take it easy and use a single dataset. Let’s use the previous example, concentrating on temperatures in Bergen between Sept. 19th and Sept. 27th 2015. The “easy way” consists in selecting the data which has to be contained in the graph: this means […]

3. Make your first line chart












pie chart
It is not so often that one gets to use a pie chart… and it is not so often that such a chart is used for the right purpose. Pie charts can be used to represent different categories in a population, election results, polls, data based on proportions and percentage. Let’s learn how […]

11. Make a pie chart


legend box
You may realize by quickly checking the pie chart that we just made previously that not all of the parties are represented in the legend. This is due to a default setting that MS Excel uses and which restricts the size/display of the legend box. To display all categories or […]

12. Resize the legend box








var.p
To calculate the variance of a series,  you may choose between using the =VAR.P(...) and =VAR.S(...) functions. Both functions provide the variance, but based on different assumptions and therefore on slightly different formulas.   Use =VAR.P(...) if the dataset that you analyse is a whole population.     Use =VAR.S(...) […]

3. Calculate the variance


standard deviation S
The standard deviation may be calculated using one of the following options: a) the standard deviation is actually the square root of the variance. In MS Excel, this can be written =SQRT(VAR(...)). In our case, type =SQRT(VAR(A1:A100)).   b) use the formula =STDEV.S(...) or =STDEV.P(...) to get the standard deviation of the […]

4. Calculate the standard deviation






cumulative
Often, the simplest way to visualize your dataset or sample will be through a histogram, also called frequency histogram. The histogram will eventually represent the distribution of the data and the shape of the chart will most certainly give you information on whether this distribution is symetrical, bimodal, skewed… This […]

8. How to draw a histogram



homemade boxplot
A boxplot (box plot, or whisker plot) is a compact, but efficient way to represent a dataset using descriptive stats. This “little diagram” combines informative, standard values such as the first and third quartiles (the bottom and top of the box, respectively), the median (the flat line inside the box) […]

9. How to draw a boxplot


Empty worksheet
When first starting MS Excel 2010, the program opens an empty workbook file named by default “Book1” (as indicated in the title bar on the top of the window). This workbook contains three empty worksheets (“Sheet1”, “Sheet2” and “Sheet3”) represented by the three tabs at the bottom of the window. […]

1. Starting up MS Excel 2010


Insert a new worksheet via contextual menu
Default workbooks always come with 3 empty worksheets. At any time, you may add a new worksheet to your workbook just by clicking on the icon next to the last sheet of your current workbook. The new sheet automatically appears to the right of the last one. Note that mousing […]

2. Opening a new worksheet







without parentheses
One of the first things that you may want to try when it comes to calculation is performing simple arithmetic operations “somewhere” in the worksheet. To do this, select any cell in the worksheet by clicking on it or by entering its address (for example E7) in the name box. […]

6. Simple arithmetic operations





Insert function
Many functions and arithmetic operations are pre-programmed in MS Excel. Providing that you know the name and role of a function, you may perform heavy calculations within a few clicks. At any time, click on the symbol fx next to the name box to open a dialog box which will […]

9. Using formulas and functions



Sorting data
You do not necessarily need to suffer from obsessive compulsive disorder (OCD) to organize and tidy a bit your data. It is actually a good practice to sort tables using parameters and variables. MS Excel has a useful tool that performs this type of tidying according to one or several variables of […]

11. Sorting your data




Save your work
When you are finally happy with the content of your worksheet and you consider that it would be a shame to see it disappear, simply use CTRL+S to save your work. If the workbook already has a name and location, then the data will be save automatically without any further request. Otherwise, […]

13. Saving your work