Download
1 / 16
160 likes | 271 Vues
This guide covers essential Excel functions, including conditional, nested functions, VLOOKUP, and text manipulation techniques. Learn how to effectively manage range names and perform operations like CONCATENATE, SUBSTITUTE, and TRIM. Explore XML usage, data import methods, and XML syntax essentials. The section on charts introduces important terms and types, such as column, line, pie, and area charts, along with advanced types like bubble and radar charts. Get equipped with skills for creating, editing, and printing charts in Excel.
E N D
Day 6:Excel Chapters 3 & 4 RAHUL KAVIRahul.Kavi@mail.wvu.edu SEPTEMBER 05, 2013
Recap • CONDITIONAL FUNCTIONS • NESTED FUNCTIONS • PAYMENTS • RANGE NAMES • MANAGING RANGE NAMES • VLOOKUP/HLOOKUP
Text manipulation • Convert Text to Columns • Data->Text to Columns • Just like importing text files • CONCATENATE() • Combines text
Changing Case • PROPER() • Also known as title case • First letter of each word capitalized • UPPER() • LOWER()
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
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
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
xml • Wrong XML File • XML only carries data • No information on how to display it (like Word, Excel, etc.)
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 -->
XML Import • Data Ribbon->From Other Sources->From XML Data Import
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
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
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
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
Next Class • Creating Charts • Editing Charts • Moving Charts • Chart Layouts and Styles • Printing Charts • Sparklines • Trendlines
