Querying Using Excel

1. Querying Using Excel

2. Asking questions of the data • If you imagine the master table as a set, when you ask questions of the data you will filter out information into subsets eg all the servants, the women, the children, the male defendants found guilty of murder etc. • In each case go to Data then Filter • Next to each heading a box with a down arrow appears. This will form the basis for our queries.

4. Meeting exact conditions • For example all those presented at the Frankpledge court • Click on the down arrow next to the ROLE column. • Click on Presented. • To return to the whole table, click on the clear button • The bottom line tells you the number of records filtered from the total.

6. Text filters • Use the text filters to refine the search • For example using does not equal or does not contain • Or begins with/ends with • The custom autofilter allows wildcards. So b*r will select butcher, baker, builder etc. *smith will select blacksmith, gunsmith, coachsmith. Sm?th will select Smith and Smyth

8. Ranges • It is possible in numerical fields to select data within a certain range. • For example to select all the presentments between 1600 and 1605 use the Number Filter option and choose greater than 1600 and less than 1605. • The Top Ten… function allows you to select the ten most numerous values.

10. Pivot tables • To create frequency counts or cross tabulations use the Pivot Table option • The pivot table allows you to select fields to count or to summarise • You can count up all the butchers, bakers and candlestick makers • Or you can compare columns such as age and sex or role and place