Day 6: Excel Chapters 3 & 4

1. Day 6:Excel Chapters 3 & 4 RAHUL KAVIRahul.Kavi@mail.wvu.edu SEPTEMBER 05, 2013

2. Recap • CONDITIONAL FUNCTIONS • NESTED FUNCTIONS • PAYMENTS • RANGE NAMES • MANAGING RANGE NAMES • VLOOKUP/HLOOKUP

3. Text manipulation • Convert Text to Columns • Data->Text to Columns • Just like importing text files • CONCATENATE() • Combines text

4. Changing Case • PROPER() • Also known as title case • First letter of each word capitalized • UPPER() • LOWER()

5. SUBSTITUTE • SUBSTITUTE(text, old text, new text, n) • text: the text you want to make the substitution to • old text: the text you want to remove • new text: the text you want to replace old text with • n: which occurrence to change • If n is not specified, all text matching old text will be replaced with new text

6. Other text functions • TRIM() • Removes leading and trailing spaces • LEFT(text, n) • Returns the leftmost n characters of text • RIGHT(text, n) • Returns the rightmost n characters of text • MID(text, start, n) • Returns n characters of text, starting with the character in the position specified by start

7. xml • eXtensible Markup Language • Why use XML? • Each piece of data has a tag that specifies what it represents • A tag is like a label • HTML is a specific form of XML with limited tags (<h1>header</h1>, <b>bold</b>, etc.) • XML can have any tag

8. xml • Wrong XML File • XML only carries data • No information on how to display it (like Word, Excel, etc.)

9. XML syntax • Element • Start tag, end tag, and data • Tags • Tags use angled brackets <> • End tags must have the same name as the start tag, but are prefixed with a / • <example>data</example> • Tags are case sensitive so you can’t end an <example> with </Example> • Comments <!-- comment tags do not need an end tag -->

10. XML Import • Data Ribbon->From Other Sources->From XML Data Import

11. Custom XML imports • File->Open->Select XML File • Choose “Use the XML Source task pane” • Drag elements to the desired cells • Right click on the XML area, XML->Import and select the XML file again • Excel will import the data in the format you laid out

12. charts • Charts are visual representations of data. • Important Chart Terms • Chart Area: entire chart • Plot Area: area where data is displayed • Title: brief description of chart • X-axis: labels and scale or category • Y-axis: labels and scale or category • Legend: labels for colors used

13. Types of charts • Column/Bar Charts • Clustered • Stacked • 100% Stacked • Line Charts • Simple • Stacked • 100% Stacked • Pie Charts • Simple • Exploded Pie • Pie of Pie • Bar of Pie • Area Charts • Like line charts, but area below line is filled • Scatter Plot

14. More chart types • Stock Charts • High-Low-Close • Open-High-Low-Close (candlestick) • With or without volume (how many shares were traded) data • Surface Chart • 3D plot of two variables per category • Doughnut Chart • Like pie chart, but can show multiple data series • Bubble Chart • Like scatter chart, but shows three variables. • The 3rd variable controls the size of the bubble • Radar Chart

15. Next Class • Creating Charts • Editing Charts • Moving Charts • Chart Layouts and Styles • Printing Charts • Sparklines • Trendlines