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

 in Excel -4- Export graph/data / MS Excel 2010
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

 in Excel -4- Export graph/data / MS Excel 2010
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

 in Excel -3- Figures and graphs / MS Excel 2010
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

 in Excel -3- Figures and graphs / MS Excel 2010
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

 in Excel -3- Figures and graphs / MS Excel 2010
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

 in Excel -2- Descriptive statistics / MS Excel 2010
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

 in Excel -2- Descriptive statistics / MS Excel 2010
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

 in Excel -2- Descriptive statistics / MS Excel 2010
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

 in Excel -2- Descriptive statistics / MS Excel 2010
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

 in Excel -1- Get Started / 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

 in Excel -1- Get Started / MS Excel 2010
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

 in Excel -1- Get Started / MS Excel 2010
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

 in Excel -1- Get Started / MS Excel 2010
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

 in Excel -1- Get Started / MS Excel 2010
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

 in Excel -1- Get Started / MS Excel 2010