Getting Started
There are so many functions and capabilities using excel that we can not cover all of them in the limited time together. Over the next week we will explore a few of the functions of excel.
Using the file attached to the button "Hockey Players" we are going to explore how to navigate excel and some simple functions and formatting. You can follow the steps below
|
Conditional Formatting
In this section we are going to learn how to use the conditional formatting features to colour our cells based on specific criteria. This can be used to highlight a specific piece of information so it can easily be identifiable.
Getting Started with Formulas
Using formulas in excel is one of the main features. We are going to explore some of the most used formulas over the next few lessons. Scroll through the images below to read about some of the formulas we talked about in class. To insert any formula into a cell you need to start with the equals sign (=)
Working with Large Databases
Let us start thinking about working with larger sets of data! There are many examples of large data sets that we use on a regular basis and we are not even aware of them.
Amazon's inventory is a database organised similarly to the ones we are using. Google images is also a database of images with lots of metadata attached to each image. Let us look at a database of an image website and the information included inside it. The Data set we are working with has 25,000 rows of information so instead of looking at all the information individually we are going to use formulas to analyse or data. |
What We Need To DO
Find
- UsCopying Data
- Copy the data in column heading "Photo Location Country" to a new sheet, Name the new sheet "Country"
- Copy the Camera Make Column to a new sheet, Name the new sheet appropriately
- Copy the Date Submitted Column to a new sheet and name it appropriately
Using the Sort Function
- Sort Main sheet By Photographer First Name
- Sort the sheet named "Country" alphabetically
- Sort the Camera Model Sheet alphabetically
- Sort the Date submitted sheet from oldest to newest
Using the Conditional Highlighting
- Conditional Highlight the main sheet on a Scale for Number of times downloaded
- Conditional Highlight those that are located in your home country on the Country sheet you created
Using Functions
- Using the "=year" function pull just the year data from the date submitted column
- Using the "=countif" function, count the number of photos taken in your home country
- Using the "=lookup" function find the first picture id from a photographer with the same name
- Using the "=countif" function, count the number of photos each year
- Using the "=countif" function, count the number of each camera model name
Multi-Step Challenges
Finding the Most Frequent Country
- Step One: Navigate to your country Sheet
- Step Two: Copy the country names from Row A. Special Paste the Values only in Row B
- Step Three: Under the data tab, select "Remove Duplicates"
- Step Four: Use the Countif function. (If followed exactly above formula should look like this, =countif(A:A,B1) )
Finding the Most Frequent Camera
- Step One: Navigate to your Camera Model Sheet
- Step Two: In Column A where you have the Camera Make, follow steps above to copy data to new column and remove duplicates to find Most Frequent
Using the Graphs/Insert
- Graph the year submitted column in a bar graph
- Graph the camera models
- Using the photo id found with the photographer with the same first name, find the image on the website and insert it in excel