Intermediate Excel
Dover Public Library
Peggy Thrasher
603-516-6050
Here is a table with a bunch of numbers
When you start typing a function, it tells you what the function will do.
When you put the first parenthesis in it shows the arguments that the function expects. The bold argument is the one it is expecting now. You can give it a number (like “4”) or you can refer to a cell (click on B2). The next argument is in square brackets. That means that it is optional. You could just find the average of one cell, but that isn’t very useful. So add a comma
Now [number2] is bold. Since you put in a comma, Excel is expecting a second argument. So click on B3 and type another comma. So it is now expecting the third number.
If you choose a range instead of a single cell, Excel treats that as the first argument. You could choose another group of cells if you want. Just type a comma and put your new cell or range of cells in.
But we’re done, so we close the parenthesis. The hints go away because you can’t add any more arguments after the closing parenthesis. The enter key tells Excel that you are ready to move on.
Let’s see how we can get the minimum for that same column. Guess what the function name is.
All you have to type is “=m” and it is on the list. Start the arguments with a parenthesis. Put in the arguments. Close the function with an end parenthesis, and then the Enter key.
If you can’t figure out the name of a function, go to the Formulas tab. All of them are categorized and listed.
If you click on one of the functions it helps you fill it in. (it is hard to find the Min function – it is hiding in More Functions / Statistical )
You can move this box around if it is sitting on the cells you want to select. When you select the column, it even shows you what the values are. And tells you the average. When you are done click OK.
Try Roundup.
Our Legislators. Let’s make a new column with the last name followed by the first name. You can use the =Concatenate function or just use the & operator.
We want to put a comma and a space between the names, too. Then we can fill down. If we wanted to create a column with first name followed by last name we would want to put a space in between the names.
Aside: Take a look at the Birthday column. Excel stores dates as the number of days since Jan 1, 1900. So Ralph was born 19983 days after Jan 1, 1990. You can reformat the date to something you want to read. A short date looks like “9/16/1954”, a long date looks like “Thursday, September 16, 1954”.
The IF function is very useful. The first argument is a logical test – something that will evaluate to True or False. We’ll test to see if the fiction number is greater than 40. Notice that it is showing us that this value is true (45 IS greater than 40)
Then we need to say what we want in the cell that we are in if the test evaluates to true. Let’s just put in the word True.
And last it wants to know what to put in the cell if the test evaluates to false. We’ll put in the word false.
So that evaluated to TRUE. If we fill down the column we can see that some are true and some are false.
We can put something into the cell that means a little more to humans. The function wants text to be in quotation marks. If you fill in the word without quotation marks, Excel will put them in. If you are typing this formula directly into a cell, you’ll need to put the quotation marks in or you’ll get an error.
More readable for humans.
You can have complicated logical tests, and you can put any formula/function for the value.
You can use a nested If forumula, too. Start with one of the choices and set the value for when it is true.
If the first condition is not true, then test for the second value. You don’t need the equal sign because Excel know that it is a function because you didn’t put it in quotes like you would for text. And if it isn’t english and it isn’t spanish then it is bilingual so put in a B.
Now we need to close all of those parenthesis. The first one will finish the spanish if statement, the second will finish the english if statement. Notice that the color of the closing parenthesis matches the color of the opening one so you can tell what you are closing.
And then fill down.
To count all the women in congress, use Countif and select the gender column for the range. The criteria uses weird syntax. Excel wants quotes around the criteria, but it will put them in if you don’t. It also tells you the result.
To find out how many women senators there are use Countifs. This lets you set more than one criteria. First the gender needs to be “F”, AND the type needs to be “sen”.
If we tried to use two countif criteria we get a different result.
208 is the number of females (108) plus the number of senators (100).
Sumif lets you test one column but then add another. Here we are looking for females, and the adding their district numbers. Answer: 1085
Formula Errors screen shots are from http://www.excel-easy.com/functions/formula-errors.html
Sometimes a formula will give you an error even when nothing is wrong. You can use ISERROR to clean up your results.
Test to see if the result will be an error. If it is, then put in a different result. If there is no error then you can use the calculated result.
And now other formulas that depend on that value will work as well.
In the Data tab you can choose to filter your columns. This will add a little dropdown for each column.
You can sort the document by this column, create a filter, or choose which values you want to see. This is a great way to see exactly what values there are in a column.
When you filter a column it changes the triangle to a filter icon.
Caution: if you use a function while the sheet is filtered the function includes all the rows – not just those that are visible.
To make a chart, first select the data you want to graph. Then choose one of the chart types in the Insert Tab
This is the first chart type in the column group, called a clustered column. Each value in the table is represented. The values in the fiction column are blue, while those in the non-fiction column are red. The months are listed on the X-axis (horizontal) and the Y-axis has numbers to show what the magnitudes of the values are.
We are now in Chart-land. 2 new tabs have been added at the top. The dark green area on the top indicates which tabs are chart tabs. As long as you have the chart selected the chart tabs will be there.
Let’s change the chart type. Click on Change Chart Type in the Design tab. Most chart come in three flavors. Vanilla, stacked and 100% stacked.
This is the middle one, called stacked column. With this format the bottom of the bar is the fiction, and then the value for non-fiction is added to the fiction value. So the bar is now representing both together.
Choosing 100% stacked column shows what percent of the total each choice represents. So now the Y-axis is percents. The length of the blue line shows what percent of the total for that month is fiction, the red shows what percent is non-fiction. Most of the other charts are variations of The columns have different shapes, and some are 3-D, or they are just points. Some of the choices near the bottom are a little weird.
You can choose new colors in the Change Colors dropdown.
The Quick Layout section gives you some choices for basic layouts. You can choose to have a Chart Title, or to have the values written on top of the bars or move the legend etc.
Choose Select Data in the toolbar, and then select just through August. This changes the data that will be represented in the chart.
The data goes just through August now.
The Plus sign next to the chart lets you choose which chart elements you would like to see, and even lets you choose options for those elements.
The paint brush lets you choose chart styles – just like at the top in the tool bar.
The filter icon lets you choose individual data points to include or filter out. You need to click Apply to see those changes.
Click on Switch Row/Column and now you see each month for fiction and each month for non-fiction.
Right click on just the December bar. Then choose Format Data Series. Let’s change the fill. Chose The paint bucket in the right Format Data and then pattern fill on the right. Choose wide diagonal red stripes .
Now December has candy cane columns.
You can make the chart bigger by dragging one of the corners.
There is a Move Chart Location icon at the far right of the design tab. You can choose to put the chart in a different worksheet, or create a new sheet just for the chart.
If you change the data in the table, your charts will all change.
Open a Microsoft Word document. Copy a chart in your Excel document and paste it with one of two paste options (they have little links on the clipboard pictures): “Use destination theme and link data” or “Keep source formattin g and link data”. If you keep the two files where Microsoft can find them (i.e. on the same computer) then when you change the underlying data in the Excel sheet your Microsoft Word document will change, too.
Pie charts can’t handle as many dimensions as the other types. If you choose the months and one of the values you can see the distribution for that year.
Choosing just one row doesn’t work well.
But if you switch the rows and columns it works fine.