WIXLIB

03/18/2013Advanced Excel 201058th Annual Conference & ExhibitsTracy S. Leed, MSc, PRSBOMarch 19 ‐ 22, 2013Payroll & Staff Accountant/System AnalystDavid L. Lawrence Convention Center,Pittsburgh, PennsylvaniaChester County Intermediate Unit #24Downingtown, PAAdvanced Excel 2010What’s new in Excel 2010 Intro Customizable ribbon Office Backstage View Workbook management tools Sparkline’s Slicers Paste with Live Preview New Equation editing tools Many other enhancementsTIP See these resources:What's New in Excel 2010; px?CTT 5&origin HA101842942# Toc274394080Guide to locate favorite menu and toolbar commands in 2010 �products‐HA101794130.aspx# Toc268688375Advanced Excel 2010 Power user Tips and Tricks Master the Data It’s all about the Data Data tools to manage the Data Transform the Data Automate - adopt Developer skills1

03/18/2013Advanced Excel 2010Power user Tips & Tricks Quick Access Tool bar and Ribbon ‐customizeThe ribbon new to Excel 2007 allowedusers to Customize quick access toolbarNew to Excel 2010 customize RibbonCreate custom tabs and groups andrename or change the order of the built‐intabs and groups.TIP You can also get to the Customize the Ribbon window, by right‐clicking anytab on the ribbon, and then clicking Customize the Ribbon.Advanced Excel 2010Power user Tips & TricksExcel 2010 customize Ribbon File tab\Help\Options\Click Customize RibbonCustomizeoptionsNew TabNew GroupRenameImport/ExportRearrange withup and downarrowsTIP See this resourceVideo: How to customize the 52.aspx?CTT 5&origin HA010355697Advanced Excel 2010Power user Tips & TricksTemplates ‐ save time and add consistency to your excel documentsFile Tab\Newtemplates grouped by categoriesA powerful dashboard template is one of the many options available2

03/18/2013Advanced Excel 2010Power user Tips & TricksStatus Bar ‐ get more informationCustomize the status bar ‐ right‐click status barFreeze and unfreeze worksheet titles ‐ view titles while scrollingthrough large data setsView tab\ Freeze Panes (Unfreeze Panes)Excel displays dark lines to indicate frozen elements.Split and Un‐split Worksheet windows a toggle command,allows users to scroll and view sections of large data sets.View tab\Split – toggle on and off.Advanced Excel 2010Power user Tips and TricksView Side by SideLine up Multiple Sheets in the same Worksheet in same windowView tab\New Window\View Side by SideBy default, Synchronous Scrolling option is turned on, so that you can scrollthrough both pages by dragging the slider bar in one of them.Turn off Synchronous Scrolling if you prefer to scroll through each sheetseparately.Magnification ‐ Zoom slider allows user to view more data on the screen. Click Zoomslider bar to desired magnificationTo focus on specific group of cells or data, select data set, thanslide Zoom SliderZoom dialog box; allows user to define zoom rationAdvanced Excel 2010Power user Tips and TricksKeyboard shortcuts – adding a few of these can be a huge time saverCTRL key shortcutsALT key shortcuts – and Hot Keys for editing commandsPress ALT key ‐ KeyTips displayed over each feature that is available in the current view.My new favorite ALT SUM() functionKeyTipsCNTL‐[: ‐ CTNL than keys – [ at the same time‐highlights the Formulas that ‐Reference the Current Cell Excel 2010 Keyboard shortcutsFunction keys‐ F1‐F12F1 help; F7 spelling; F9 calculate; F10 KeyTips; F11 create chart current cell range; F12Save AsTIP See these resources:Excel 2010 Keyboard Shortcuts including PDF Quick Reference Cards; 0‐HP010342494.aspx#BM3Excel 2010 keyboard shortcuts II: ALT key Course & Quick Reference Card; y‐shortcuts‐RZ102733786.aspxExcel 2010 ALT hot keys for editing s.html3

03/18/2013Advanced Excel 2010Power user Tips and TricksNew Backstage view & File Tab ‐ Where you do things to the file which you don’t do in the fileReplaces File menu or Office button in earlier versionsContains common tasks related to filesThe Office Backstage view iswhere you manage your filesand the data about them —creating, saving, inspecting forhidden metadata or personalinformation, and settingoptions.Backstage view – simplifiedprinting (Ctrl P)‐ preview,change settings, and print allwith a live previewAdvanced Excel 2010Power user Tips and TricksExcel 2010 Security – Protect your filesProtected view and more!Security Message Bars provide the opportunity to considerthe potential security risks that may be in your file, and then theability to open or read the file while reducing the risks that canoccur.Use the Trust Center to view security settings or to changethem. File Tab/Help‐ Options/Trust centerUnderstand the reasons for Protected View and how toenable editing when it appears.TIP See this resourceQuick Reference Card: Office 2010 Security Protecting your ction 10&mode printAdvanced Excel 2010Master the dataFormat and edit ‐ Home TabTranspose – rearrange data in rows and columnsHome tab\Clipboard\Copy\Paste\TransposeConditional formatting – visual clues to dataHome tab\Styles\Conditional FormattingFormat as Table – quickly manage group of related data independently fromdata in other rows and columns.Home tab\Styles\Format as TableCell Styles‐ quick and easy style changes to cells – more than 40 colorfulpredefined styles availableHome tab\Styles\Cell Styles4

03/18/2013Advanced Excel 2010Master the dataVisually denote the data‐ Insert TabIn Excel 2010 Chart wizard retired!Charts Insert tab direct access to all major types of chartsPivot table and Table option – use to quickly arrange, summarize,manage, and analyze the dataSlicer – filter large data sets in more detailGrouping – outline and summarize data up to eight levels.Advanced Excel 2010Master the data Visually denote the data‐ Insert TabCharts – display data visuallyUnderstand the parts of an excel chart to harness the powerof the visualization.The guts: Chart area, data marker, data series, axis, tick mark, plotarea, gridlines, chart text, and legend.TIP See this resource:Get to know the Parts of andExcel 2010 2010‐chart.seriesId‐223717.htmlAdvanced Excel 2010Master the dataSparkline’s – visualize data trends with one clickHow to use Sparkline’s to show data trendsTIPHow to use Sparkline’s to show data ‐HA010354892.aspxHyperlinks – embed links to documents, email addresses, picture, orwebsite directly on worksheet.Screenshot tool ‐ instantly insert picture of any program that isrunning and is not minimized. Use screen clipping to insert a part ofthe screen picture.5

03/18/2013Advanced Excel 2010It’s all about the DataData tab – get connectedGet external data – eliminate time consuming copy and pasteImport data from a variety of sources with a few simple clicksAfter connection you can auto refresh data from its original source,manage and change connections and their properties.TIPConnect to external Data Excel 0342297.aspxAdvanced Excel 2010It’s all about the DataConnect data (import data) from text filesAdvanced Excel 2010It’s all about the Data – get connectedExistingConnectionsData refreshedwith savedconnectionoptionsMSN MoneydefaultsUse dataconnections fordata files,databases, andother datasourcesincluding theWeb.6

03/18/2013Advanced Excel 2010Data tools to manage the dataData Tab – empower yourselfSort & Filter ‐ Specify the data you want to see with‐ you are the master overthe dataData validation ‐ control type of data entered into cells; Data tab\datavalidation.Advanced Excel 2010Data tools to manage the dataData Tab – empower yourself over the dataConsolidate ‐ summarize and report values from multiple worksheets or workbooks intoone NEW range/worksheet.Two main techniques: by position; by categoryData Tab/ConsolidateVariousfunctionsavailableLet’s try!TIPS Consolidate data from multiple worksheets in a single worksheetVideo: Consolidate Excel Data from multiple workbooksAdvanced Excel 2010Data tools to manage the dataData Tab – empower yourselfWhat –If Analysis: ‐test values for the formulas in the sheetScenario Manager‐ Create and save variousscenarios and switch between themGoal Seek‐ find a desired value for a formula‐Data tables ‐see results of varied possible inputs.TIPSIntro to What‐ if analysis; 164.aspxVideo: What‐if analysis example; http://www.youtube.com/watch?v ZyehZAbj9IwCompare Budgets with os/Video: What‐If / Data Tables; http://www.youtube.com/watch?v eSRr3g6ptYYLet’s Try it!7

03/18/2013Advanced Excel 2010Data tools to manage the dataData Tab – empower yourselfGroup and Subtotal ‐ Outline the data up to 8 levelsGroup or relate a range of cells so they can becollapsed and expanded.Subtotal total rows of related data together byautomatically adding subtotals and totals for cells selected.Use an outline to quickly display summary rows or columns,or to reveal the detail data for each groupTIPHow to outline (group) data in a worksheet 0342744.aspx Try it!Advanced Excel 2010Transform the data !The Formula tab – take actionFunction Library– arranged by categoriesgroup data ranges for use in formulasFormula Auditing -powerful tools to manage formulasFunctionsDefined names –Trace Precedents/dependents/remove arrowsShow Formula – a hidden GEMError CheckingEvaluate FormulaWatch WindowCalculation OptionsAdvanced Excel 2010Transform the data !The Formula tab – take actionInsert Function‐ fxTIP Use Insert Function option onthe Formula tab to open dialogbox.For help with functions –highlight function in blue andselect “Help on the function”option bottom of screen8

03/18/2013Advanced Excel 2010Transform the data !The Formula tab – take actionFunction LibraryDefined names ‐ simplify data ranges for use in formulasEx, 3 names for Admin, IT, Operations created, review in name manager, use filter to assist with viewing defined names.!Advanced Excel 2010Transform the data ! Formula TabShow Formulas – powerful feature which auto displays allfunctionsHIDDENGEMCalculate Options – specify when functions are calculated, default isrecalculate when formula change madeAdvanced Excel 2010Transform the data !The Formula tab – take actionFunction LibraryEvaluate Formula –Select cell thanevaluate formulaoption to get resultsResults hereCell using datavalidation and containsa constant9

03/18/2013Advanced Excel 2010Transform the data !The Formula tab – take actionFunction LibraryWatch Window – monitor values of specified cellsAdvanced Excel 2010Transform the data !Formula TabRank data with Functions Percentile() – specify ranking and return a value. PercentRank() – returns percentage based on specifiedvalue. Quartile() – returns value of cell range based on specifiedvariable that divides distribution into four groups withequal frequency. Rank() – returns value base on the specified rank.Advanced Excel 2010Transform the data !Formula Tab & FunctionsDate functions‐ for analyzing data Today() – displays current date Now() – displays current date and time Date(Year, Month, Day) – date displayed based onarguments Days360() – calculates # of days between two datesbased on 360 calendar NetWorkDays(StartDate,EndDate,Holidays) – calculatesnumber of work dates using specified dates Year(Date) – returns year portion of a date Month(Date) ‐ returns month portion of a date10