Oakland Middle School

Skip to main content
Main Menu Toggle
Melinda Reed » Excel - Intermediate for Visual Representation and Data Analysis

Excel - Intermediate for Visual Representation and Data Analysis

Click on the practice Excel notebook to download a copy for this session.
Exploring "Tell me what you want to do".
 
Excel, as well as other Microsoft products, has a very good search engine for you to use when you can't find the specific formatting tool that you need.  This feature is called "Tell me what you want to do".  For example, if you are asked to go to Find and Replace in the toolbar, you do not need to waste time trying to figure out where that feature is located.  Just go to the Search Box and type in Find and Replace.
 
 
Experiment with Find and Replace
 
  • Look at the tabs at the bottom of your Excel Workbook.  Find and click on the worksheet entitled Find and Replace.
  • Click on any cell in the workbook on the Find and Replace Worksheet.
  • Go to the search box and tyhpe in Find and Select
  • Click on Find
  • Type in 50 in the box and then click on the replace tab.
  • Type 100 in the replace box and choose replace.  Notice all the 50's turn into 100's.
 
Understanding Concatenate
 
The goal of Concatenate is to combine the data in two different columns into one column.  For example, you may need to take a First Name column and a Last Name column and combine them into a Full Name column.
 
  • Now locate and click on the Concatenate tab in our Excel Workbook.
  • Click into cell D2.
  • In the search box, search for Concatenate.  
  • Click Text Functions and choose CONCAT.
  • In Text 1 box, choose the first name Jeannie (A2).  You can click on the A2 cell and the info will populate in Text 1 box.
  • In the Text 2 box, type two sets of quotation marks with a space in between them...ex. " ".
  • In Text 3 box, choose the last name Williams (B2).
  • Click Ok.
  • Once this is complete, you can autofill the formula for the rest of the names.  This is done by simply clicking in cell D2 and hover the mouse in the bottom right of the cell until a plus sign appears.  Drag this down and it will copy the formula down the entire column.
 
Text to Columns
 
  • Click on Column C labeled CFA 1 and insert 4 columns.  (Columns always insert to the right of the column selected.)
  • Highlight the student names in Column A from Hall, Bill W. all the way down to Wayne-Stephens, Laura K.
  • Go to the Data tab in the menu and click on Text to Columns in the middle of the formatting toolbar.
  • Follow the prompts from the Data Wizard.
  • Select the Destination Cells where the new data will go and click on Finish.
 
Applying Conditional Formatting
 
Conditional Formatting applies colors to individual cells based on criteria selected within Excel.  For example, we can color code CFA grades in a worksheet so we can easily see who is struggling or who is showing mastery.
 
  • Click on the Conditional Formatting worksheet tab at the bottom of the workbook.
  • In the search box, type Conditional Formatting.
  • Choose Color Scales and then choose the Green-Yellow-Red Color Scale.
  • Highlight all CFA Data Fields.
  • Now type into the search box, Conditional Formatting again and choose New Rule.
  • In the Select a Rule Type window, select Format all cells based on their values.
  • In the Edit the Rule Description: window, select 3-color Scale for the Format style since we chose 3 colors above.
  • In the Mid Point box, type 70 for the value.
  • Click Ok.
Practicing with Basic Functions
 
For this task, we will continue in the Conditional Formatting tab.
 
  • Click in Cell D23.  Select the function (fx) symbol in the toolbar.
  • A function box will appear.  Choose the category Most Recently used.
  • Select Average, then Ok.  (This can also be accomplished using Autofill.)
  • Now, Autofill the averages for the other CFA's.
  • Can you Autofill individual student averages?  Do it now!
Protecting Sheets and Workbooks
 
Brainstorm some reasons why you  might want to protect a worksheet or an entire workbook.
 
  • Click on the File tab in an open Workbook.
  • Click on Protect Workbook.
  • Take a moment and go over the options for protecting and be prepared to discuss with the group.
  • Click on the arrow in the top left of the screen to go back to the workbook.
 
Creating Data Forms
 
We will create a new worksheet in our workbook for this task.  Creating a Data Form is an easier way to enter data for each student.  Data will be entered by a small form versus typing into the spreadsheet.
 
  • Right click on the Charts Worksheet tab and choose Insert.
  • Click on new Worksheet.
  • Create heading in this new worksheet for:name, ID, CFA 1, CFA 2, CFA 3.
  • Click on File, and then Options.
  • In the Excel Options box that appears, click on Customize Ribbon in the left vertical menu.
  • In the top drop down menu under Choose commands from...select All Tabs.
  • Next, click on the word Home under the Main Tabs column.
  • At the bottom, choose New Group.
  • Now switch from All Tabs in the Choose commands from box to All Commands.
  • Scroll down and select Form.
  • Click Add, then Ok.
 
Now highlight the headers you created.  In the ribbon under the Home tab, Click on Form.  Now you have a Form to assist you in entering data more quickly.
 
 
Exploring Charts
 
  • Click on this link and watch the video on charts in Excel.
  • Experiment with the different charts and options.  Try the Alt + F1 keystrokes for a simple chart.
 
Click on the Exit Ticket to complete the PD evaluation survey.