Students Staff
University of Essex

March 20, 2018

Ten really useful Excel functions you should get to know…

Filed under: Uncategorized — sgswaine @ 10:55 am

Excel can do some really great things like SUM and AUTOSUM, SORT and FILTER, and some other clever functions that you may not be using like CONCATENATE or VLOOKUP? I’ve put together a list of 10 really useful functions that I think you should get to know…


CONCATENATE
(combining data in columns): Let’s say you’ve been given a spreadsheet with a list of names in it. The names are in two separate columns called Firstname and Lastname, but you want to combine these to make one column called Name.

The formula for the example below would be =CONCATENATE(A2,“ ”,B2). The space between the quotation marks gives you the space between the first name and last name in the results.

concatenate4

Want to see it in action? Watch the Joining data from multiple cells (4m 18s) video on Lynda.

 

TEXT TO COLUMNS (splitting data in columns): This time your spreadsheet has just one column called Name, but you want be able to sort by Lastname so you need to separate them out into Firstname and Lastname.

To do this select the column you wish to split, go to >Data >Text to Columns >Delimited >Next then tick >Space (for example) >Finish.

text-to-columns text-to-columns-step1

text-to-columns-finish text-to-columns-results

See how it works in the Splitting cell data into multiple cells (2m 22s) video in Lynda.

 

AUTOSUM: this function is probably the most widely used, but did you know you can use it horizontally at the same time as vertically? Just select the data you want to add as well as the column and row you want the results to appear in, then choose >AUTOSUM and it will add the results instantly.

The keyboard shortcut for AUTOSUM is Alt + equal sign (=).

auto-sum-whole-worksheet auto-sum-whole-worksheet-results

Watch the Adding a whole worksheet (1m 48s) and Adding numbers using Sum and AutoSum (6m 11s) videos in Lynda.

 

COUNT: want to know how many items you have in a column? To do this you use the COUNT formula, and if you delete a value from the column it will recalculate the number of items automatically.

The formula for the example below is =COUNT(B3:B8). Use AUTOFILL to extend the results across columns – hover over the corner of the cell until you see a plus sign (+) and then drag to the right.

count count-results

Want to see COUNT in action? Watch the Working with numbers in columns (4m 53s) video in Lynda.

 

AVERAGE: if you want to know the average value of a group of numbers, the formula for the example below
is: =AVERAGE(B3:B8). press Ctrl + enter to get results and stay in the same cell.

average average-results

Want to see AVERAGE in action? Watch the Working with numbers in columns (4m 53s) video in Lynda.

 

IF STATEMENTS: will output one result if the statement is true and output a different result if the statement is false. The general syntax for this is: =IF(statement, value_if_true, value_if_false).

The formula for the example below is: IF(B3>=500,$G$4,$G,$3). To get the ABSOLUTE REFERENCES (eg $G$4) use and to display the results as %, select the column then go to >Format Cells > Number >Percentage > no decimal places.

sumif-formula sumif-results

Need to see this step-by-step? Check out the Using IF (4m 49s) video in Lynda. Want more? Have a look at Using SUMIF and AVERAGEIF (4m 15s) in Lynda.

 

CONDITIONAL FORMATTING: lets you format numbers and dates according to the value. Often this is used to highlight high or low values but it can also be used to highlight values within a range.

To use the automatic conditional formatting options, select the range of numbers you want to format > Conditional Formatting (from the ribbon) >choose your selection from the dropdown menu eg Colour Scales or Data Bars.

cf-colour cf-data-bars

To learn more, check out the Using Conditional Formatting (4m 6s) and Using Custom Conditional Formatting (5m 49s) videos in Lynda.

 

DATA SORTING & FILTERING: one of the most useful Database functions in Excel. If you haven’t got to grips with Sort & Filter now’s the time to learn – they’re some of the most powerful functions in the Excel arsenal.

To sort a column, select the Column header >Sort & Filter > then either Sort A to Z or Sort Z to A. In the example below, I sorted highest to lowest ie Sort Z to A.

sort

If you choose >Sort & Filter >Filter you’ll notice Excel adds drop-down menus to each of your column headers – this allows you to perform multiple sorts on more than one column.

filtering
To see Sort & Filter in action, check out the Basic and multi-field sorting (6m 30s) video in Lynda.

 

PIVOT TABLES: are really useful when you have a lot of data to analyse and it’s not obvious how they relate to each other. For the example below I selected my data then chose >Insert >Tables >Pivot Tables >New Worksheet (Sheet3) >OK.

pivot

Excel will create the shell of your Pivot Table, you then need to drag and drop the Fields into the appropriate areas. In the example below I put Month into Filters; Region into Rows; Size into Columns; and Sum of Quantity into Values.

pivot-results

The Month is automatically set at All, but I can filter by a different month quickly and easily to change my results.

pivot-results-month

Want to know more? Have a look at the Creating a basic Pivot Table (2m 46s) video in Lynda.

 

LOOKUP FUNCTIONS: when you have a lot of data in a worksheet, VLOOKUP (vertical) which is for data arrange down columns and HLOOKUP (horizontal) which is for data arranged across rows, can help you extract specific data.

The general syntax for this is:

vlookup-syntax
Lookup value = which row do we want?
Data range = the entire data area (all of the columns)
Column # = the column to match
True = approximate match
False = exact match

I want to look up the Product Code and have it return the exact Description. The formula for this is: =VLOOKUP(A7,A6:F14,FALSE)

vlookup-syntax-false vlookup-syntax-false-results

To see how VLOOKUP works, watch the Creating look up tables (3m 6s) video in Lynda.

All of these functions are more can be viewed in the Excel 2010 Essential Training (6h 21m) video in Lynda. Good luck and happy spreadsheeting!



March 19, 2018

Digital Storytelling and Curriculum Confidence with Jisc

Filed under: Uncategorized — Alex ONeill @ 11:26 am

There are two interesting courses coming up delivered by Jisc, who provide digital solutions for UK education and research. See below for how to book on if either is of interest (and please do let us know if you go!)

Digital storytelling *NEW*
17 and 24 April, 1 and 8 May (one course over four sessions)

This online workshop is aimed at anyone with an interest in developing the use of stories within their personal or professional practice. It will help you develop your understanding of the use of narrative and storytelling in education, research or student support; learn how to write stories that are memorable, entertaining and engaging for your audience; use digital tools to create and share stories; and facilitate your own collaborative storytelling projects.

https://www.jisc.ac.uk/training/digital-storytelling

Curriculum confidence
1 May, Bristol
18 June, Belfast
10th October, Manchester

This one day workshop will build confidence to design and deliver a digital curriculum that will prepare students to learn successfully in digital settings and to thrive in a digital world. It will include a series of activities which will support participants with designing in opportunities for students to develop relevant digital capabilities into their course, module or unit of learning.

https://www.jisc.ac.uk/training/curriculum-confidence

If you have any queries, contact Jisc on 01235 822242 or training@jisc.ac.uk. The full schedule of courses is available at www.jisc.ac.uk/training.