formatting exercise excel

Find the “Who Uses Social Media Section”
Click on Data to see the data for Age analysis of social media usage

Copy the data from the web site into your spreadsheet:

Copy the rows of information on this page to excel.

Start at the "18-29" heading and copy down through the last number on this table. (highlighting 18-29 and then scrolling down to hold shift while clicking the last number is a good way to get the whole table.)
While hovering on the highlighted numbers, right click and choose copy.

Then, start / program / microsoft office / excel and then paste.

Make it all fit

The date will probably be in too small a column. While you could just make A larger, instead highlight the entire spreadsheet by clicking between A and 1 (see the red circle below) and then double click the bar between A and B. It will make the columns wider.
You may also need to move the heading columns over 1 so it looks like this:

Add “Survey Date” as the heading in A1.

If you do not end up with a spreadsheet that looks like the one above: just download this starter spreadsheet instead:

Right click on starter sheet to see the context menu. (Control click works as right click on a mac)
choose "save target as"
open Excel
File / open and navigate to downloads and click on startersheet.xls

Step 2: Basic formulas : Watch Video Instructions

Add a column to show just the year: Click on the B and right click to see the context menu and choose to insert.

Write the label “Year” at the top of the new column in B1.
Insert a formula in B2:

Choose the formula ribbon and insert function (In older versions, insert function or formula instead)
Type “Year” and press Go or just look through all functions and select Year
In the Serial_Number or Date box type A2.
Press enter to accept the formula

It may show a strange number because it is formatting the 2005 year as a date. No Worries – we will fix it.

Format the year as a number: right click on the year and choose format cell, then the number tab and choose general

Copy the formula down the entire spreadsheet.

Change the date to show as a 4 digit year and a 2 digit month such as 2005-03.

Highlight column A, right click and choose format cell, then number, then custom and then type yyyy-mm.

Calculate the maximum percentage in each column:

Place your cursor under the last percentage in the 18-29 year old column. (to get to the last column, you can use control / end on windows and just the end key on the mac.)
Insert a formula in that cell:

Choose formula / insert function
Type “Max” and press Go
Hit the spreadsheet box in the parameter it gives you
Highlight all of the percentages under 18-29
Press Enter to accept the formula

Copy that max formula to the other colums:

Right click on the max cell and choose copy. Then highlight the 3 cells next to it and hit paste.
Notice: All the formulas reflect the columns above them, even though you didn’t type the new cell references. See that Excel guessed what you wanted when you copied the formula over.

Add a column to show the difference between the prior survey’s percentage and the next one.

Label it “Diff from Last Survey” in the empty column next to 65+
Write a dash in the first row because there is no prior value.
Type in the formula:

In the next column, type = to show excel you are starting a formula
Click on the 16% to pull in C3
Type the minus sign
Click on the 7% to pull in C2
Press Enter to accept the formula

If it is not formatted as a percentage, format the cell as a percentage
Copy that formula down the sheet
Notice: All the formulas reflect the rows you would expect See that Excel guessed again what you wanted when you copied the formula down.

Step 3: Concatenate Text: This was done on the end of step 2’s video above.

Add a column to explain the difference.

Some help:

Use a & instead of + to string together text.
All text must be enclosed in quotes
Spaces you want in your sentence must be in quotes

Label the new column “Sentence explaining diff’
Enter the formula

In the third row, type = to show excel you are starting a formula
Click on the 9% to bring in G2
Type the & (it is shift 7)
Type in this text: " more 18-29 year olds used social media than they did in the prior survey"
Press Enter to accept the formula
The 9% shows as .09 – but don’t worry about that. Formatting that properly would take a bunch of formulas.

The column is too small, make it larger but not the full size you need. Wrap it by format cell and then choose the alignment tab and choose to wrap it.
You might need to make row 3 smaller:

Either grab the line between 3 and 4 and make it smaller or highlight the entire spreadsheet and choose home / format / auto fit row height

Paste it down the spreadsheet

Step 4: Calculating using absolute reference: Watch Video Instructions

List the percentage difference from the max for every percentage.

Label the first new column “18-29 diff from max”
Start with just the difference between the highest percentage of 18-29 and the first one, 7%.
In the second row, enter the formula for the max minus that row’s 18-29:

= C31 – C2

If 31 is not your max row, change to the max row. It will keep growing each year.

Try copying that formula down. See that it is not correct. Look at one of the copied formulas and see the problem – it keeps moving the first term.
To tell Excel you don’t want the row of the first cell to move, put a $ before the row in the original formula (C$31 – C2) And then copy it. It will now be correct.
Then copy that formula to the next 3 columns and see that it remains correct.

If you had a $ before the C as well, $C$31, it would keep looking at the 18-29 column even if you wanted 30-49. Leaving the $ off before the C let Excel move the column when you copied, even though it kept the row the same.
Experiment with putting $ in different parts of the formula and recopying

Change the formula for the label to one that can copy:

Change “18-29 diff from max “ to a formula =C1& " diff from max"
Copy that to the next 3 cells.

Step 5: If Watch Video Instructions

Add a column to indicate whether more than 50% of the18-29 group was using social media.

Label the column “18-29 more than 50%”
Formula / insert function
Choose "If" and see 3 boxes.

In the logical test, click on the percentage of 18-29 year olds for the row and then type "> .50 "
Next to value if true, type “more than 50%”
Next to value if false, type "less than or equal 50%".
Press enter to accept the formula (and maybe ok).

Copy that formula down.
Change one percentage to be less than 50% and see the change.

Play with filtering:

Highlight the entire sheet
Choose home ribbon / filter in the editing pane to turn on filtering
Down arrow on the column you just added.
Click more than 50% and see how you only see the rows that are less than 50%. That is one of the powerful uses of the if statement.
Click the filter button again to remove filtering

Step 6: Formatting: Watch Video Instructions

Format all text to be arial 11

Highlight the entire sheet
Right click to format cell and choose font than then choose font arial and size 11

Bold row 1
Put a light border around all cells with something in them

highight all cells with data only
Then right click to format cell / border / and click on a thin line and then click on outline and inside.

Wrap cells so you can see all the contents:

format cells / alignment / make the wrap checkmark solid and not gray

Center the difference from prior survey column data so it looks better.

Highlight column G
Right click to format cell / alignment / horizontal and choose center.

Add a header saying your name and a footer with page number

page layout / page setup pane / little dialog box next to words "page setup"
header / custom header and type "your name" and then press ok
and then down arrow on the footer and choose a page number.

Print preview and see how it prints.

file / print and see the print preview window

Change to show column headings on page and landscape when you print preview.

page layout / page setup pane / little dialog box next to words "page setup"
sheet tab
rows to repeat click on the spreadsheet box and then row 1
then click page tab
choose landscape

File / Print preview to see it now prints column headings. But see that you would have to tape together 2 pages to get all columns across.

Make it smush to one page wide without adjusting the column sizes

page layout / page setup pane / little dialog box next to words "page setup" / page
click the "fit to" button and set to 1 page wide by 99 tall

It will never stretch to 99 pages, but will squeeze to 1 wide.

Print preview to see you no longer have the extra pages with the small columns and also verify it is at least 2 pages. If it is only one page, you might have chosen 1 high. Close Preview.

Try Scrolling around the spreadsheet to see some problems:

Scroll down and see headings move.
Scroll across and lose player names.

Change to show headings on screen as you scroll down and across.

Click once on C2 and then view / window pane / freeze

It will freeze to the left and above the highlighted cell.

After you freeze, Scroll down and across and see the labels stay still
Notice

Freeze pane affects the screen and rows to repeat affect the print out, even though they are both giving you column headings
Everything that affects the printing of the sheet is in page setup.
Format cells never changes the contents of a cell, just how it is displayed

Step 7: Create a line chart: Watch Video Instructions

To create a line chart similar to the chart you saw on the internet:

· Create the basic chart:

o Highlight from 18-29 to the last number for 65+, but don’t include the maximum row. This is what will be in your chart, plus the label for each of the lines. Choose insert / chart and choose the line chart and then pick the first one.

· Get the x axis labels of the dates from column 1 :

o Right click on the area of the chart with the lines and choose “select data”

o Click “edit” on the horizontal category and then select all the dates and press OK 2 times:

Add titles to chart, horizontal axis and vertical axis:

· Click on the chart and then on chart design

· Then down arrow on “add chart element” icon

· Then choose axis title and choose both horizontal and vertical. If you did not have a chart title box, choose chart title

Now that you have boxes for each title Then change your titles:

· Horizontal: Survey dates

· Vertical : % of people using social media

· Chart title: American's use of Social Media Surveys by Pew Research (n varies)

Push the chart down below the list:

· Be sure to grab the full chart, not just the inside box

· If you move the wrong part, you can put it back with ctrl z

Step Extra: You can watch this video about using a pivot table to further analyze this data. You don’t have to do what it says, but having an introduction to pivot tables may help you in the graph analysis you will do later.