Excel 2016

[Pages:26]Excel 2016

DATA MANAGEMENT

Elaine Williamson, Catherine McGowan& Baljit Sangha

LONDON SCHOOL OF HYGIENE & TROPICAL MEDICINE | MARCH 2017

2

CONTENTS

Format as a Table........................................................................................................................................................4 Transpose ..................................................................................................................................................................... 5 Copying data ranges and charts to Word ............................................................................................................. 5 Sorting and Filtering.................................................................................................................................................. 6

Basic Sorts ............................................................................................................................................................... 6 Custom Sorts .......................................................................................................................................................... 6 Autofilter ................................................................................................................................................................. 6 Advanced Filter...................................................................................................................................................... 7 Automatic Subtotals .................................................................................................................................................. 9 Adjusting Views with Subtotals ...................................................................................................................... 10 Removing Subtotals ............................................................................................................................................ 10 Conditional Formatting ........................................................................................................................................... 11 IF Function .................................................................................................................................................................12 VLOOKUP ..................................................................................................................................................................13 Data Validation ..........................................................................................................................................................15 Input from List ......................................................................................................................................................16 Input Message .......................................................................................................................................................17 Error Message........................................................................................................................................................17 Protecting Workbooks and Locking Cells ..........................................................................................................18 Pivot Tables................................................................................................................................................................20 Statistical Functions ................................................................................................................................................ 23 Data Analysis Tool.................................................................................................................................................... 25 Converting your Excel spreadsheet to STATA, SPSS and SAS formats......................................................26

3

Format as a Table

You can also apply a pre-defined format to any table you have created in Excel. When you apply a table style to a table you will see additional features on your table that will allow you to sort the contents of your table easily. You will also see a Table Tools Design tab at the top of the screen when you click inside your table. To convert a range to a formatted table, select the range and click on FORMAT AS TABLE on the HOME tab. Then select your preferred style. If your table has a header row make sure you tick `my table has headers' when prompted. To add a row of totals for your table click once in your converted table. In the TABLE TOOLS DESIGN TAB tick TOTAL ROW. Then click on the drop down menu in your total row to select the type of total you would like.

To convert a converted table back to a `range' (i.e. an unformatted table), click on your converted table and then select CONVERT TO RANGE in the TABLE TOOLS DESIGN TAB.

4

Transpose

Sometimes data are entered with an unsuitable layout. Tables can be turned around to make row headings into column headings, thus allowing them to be more readable and functional as databases. To turn the data around select the table and select COPY. Click on another area of the sheet (or another sheet) right-click and select PASTE SPECIAL. Check the TRANSPOSE box and click OK

Copying data ranges and charts to Word

There are various options for copying from Excel into Word which are given below.

Command

Options

Included in Word

Count?

Copy Paste

Y

Copy Paste special, Paste or Paste Link Microsoft Excel Worksheet

N

Object

Formatted Text

Y

Unformatted text

Y

Bitmap

N

Picture

N

HTML Format

Y

Unformatted Unicode Text

Y

Copy Paste special, Paste Link

Word Hyperlink

Y

Paste Link ? changes in the source file will automatically be reflected in your document. Microsoft Excel Worksheet Object ? provides Excel functionality such as Excel ribbons and automatic recalculation of formulae. Picture or Bitmap ? inserted as an image.

5

Sorting and Filtering

Basic Sorts

To execute a basic descending or ascending sort based on one column, select a single cell in the column to be sorted by and then click the SORT & FILTER button on the HOME tab. Click the SORT A to Z (ascending) or SORT Z to A (descending) button

Custom Sorts

To sort on the basis of more than one column: Click the SORT & FILTER button on the HOME tab and choose CUSTOM SORT. Choose the column you want to sort by first and the sort order. Then click ADD LEVEL and choose the next column you want to sort by and click OK.

Autofilter

To filter a range click inside the column or columns that contain the data you wish to filter. On the HOME tab, click on SORT & FILTER then click the FILTER button.

Use the DOWN ARROWS that appear at the top of each column to apply one or several filters To clear the filters click on SORT & FILTER and click CLEAR

6

Advanced Filter The advanced filter command in Excel allows you to filter by more complex criteria which may not be possible using the Autofilter command e.g. filter by one criterion in column A or a second criteria in column B. Advanced filter allows you to place your filtered results in a new location, leaving the original range in view. To create an advanced filter: Ensure your data range has meaningful column headings Copy the column headings to another area of the worksheet, leaving enough blank rows underneath the copied headings to enter your filter criteria

Add the search criteria under the copied column headings (see examples below).

Searches for males who are 50 or over

Searches for females who are 40 and over from either Durham or Newcastle

Searches for males or anyone who is 50 and over 7

After adding your search criteria under the copied column headings, on the Data tab select Advanced filter

You can either filter the list in the current location or copy the results to a new location The list range is your table of data including the column headings The criteria range is your copied column headings and the criteria underneath (see below) An example of the Criteria range is shown below: Criteria range: $A$1:$I$2 Click OK

8

 ................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download