Name: Date: Hour: Worksheet: Topic # 2045 Intro To Pest: Fill & Download for Free

Pretty much every resume I received as an engineering manager included a statement that the applicant was an expert in Excel. I immediately discounted such statements, because most people have no idea how much they don’t know about Excel. And if such a person came in for an interview and bragged about their Excel abilities, it only took a couple of questions to establish otherwise.Former Microsoft Excel MVP Aaron Blood posted a “user scale” to categorize the abilities of Excel users. I like to use it as a self-test when I am teaching a class in Excel because it lets participants see where they stand fairly quickly.NoviceCan successfully navigate and save an Excel workbook.OK with Data Entry tasks.Novices can build formulas using: SUM, IF, AVERAGE, COUNT, ROUNDIntermediateComfortable building formulas to manipulate text and datesKnows what a Pivot Table is and how to build one.Knows what an add-in is and how to install one.Understands and can use the AutoFilter feature.Can record a macro and use it later.Can successfully edit/modify simple recorded macros.This category includes most users who claim to know Excel very well.Intermediate Formulas: SUMIF, COUNTIF, VLOOKUP, CHOOSE, MIDAdvancedKnows what array formulas are and how to use them.Can create and modify macros with or without the macro recorder.Knows how to build a macro that can be triggered by events.Familiar with most, if not all, of the functions in Excel.Comfortable teaching or helping others with Excel.Advanced Formulas: SUMPRODUCT, INDIRECT, INDEX, MATCH, OFFSETExpertCan build add-ins for distribution and widespread use.If you can think it, they can build it with Excel.Most likely, a disturbed individual who spends too much time thinking about spreadsheets. :-)Expert Formulas: A true expert knows the strengths & weaknesses of every single function, and has probably had to build some new ones that were not available.GuruThe true Excel elite.Probably just a handful of them in the world.If you are one, you know who you are.If you're an Expert, you probably know their names.From time to time, you might see one posting in a forum.Adapted from Aaron Blood The Excel Logic PageYou will notice that the above discussion makes no mention of how many keyboard shortcuts you know. This is intentional—because in my opinion, knowing all the keyboard shortcuts does not make you an Excel expert. It merely means that you have spent many hours doing routine Excel data entry and worksheet reformatting. Knowing the shortcuts makes you more efficient at those tasks, but won’t help you when you need to tackle something complicated and start spending more time thinking about how to do something than actually doing it. The analogy I might use is that memorizing the multiplication tables may make you more efficient at doing arithmetic, but it doesn’t make you a mathematician.Microsoft offers a series of exams to test a person’s abilities in Office apps, leading to certification as a Microsoft Office Specialist (MOS), MOS Expert and MOS Master. Two of those exams for Office 2016 have to do with Excel. If a person has MOS certification, you can be assured that they have studied all the areas that Microsoft thought were important, and were able to answer questions testing that knowledge.I have never taken any of the MOS exams, so I can’t tell you how hard they are to pass or how good a person is who has that certification. I imagine people who deliver Office training classes as their profession probably want certification. The fact that Microsoft offers these certifications for each version of Office tells me that there is both a need in some segments of the economy for certification and a fairly significant number of people who have passed the tests.How can you tell if somebody posting on the web is an expert in Excel? Spend a few hours reading their posts and webpages. Somebody who posts prolifically on the subject is likely to be an expert. If you are good at Excel, but perhaps not an expert, I’ll bet that you can still easily recognize a real expert by going through this process.Are their posts in help forums clear, concise and correct? Nobody gets it right 100% of the time in help forums, but an expert would have a higher percentage of times when they get it right the first time. An expert would also stick with the Asker as additional complexities are included. Does the person behave professionally in forums, even when provoked? Can they handle difficult questions, or do they just post on frequently asked topics?After reading their webpage articles, would you feel comfortable tackling a similar problem? Do the articles discuss both how to set up and solve the basic problems as well as factors that make the solution more complex?

What is Proficient?Being proficient means knowing how to complete the task quickly and effectively. Here is a list from Microsoft of what you should know if you want to say you are proficient. The CORE competency covers about 80% of all the options in Excel. (Think intermediate skills, not beginner, OK?) The EXPERT requirements are more advanced. I included the link to CertiPort as well: They offer the tests. The CertiPort site has many resources.For More Information:CertiPort: Microsoft Office Certification TestsMicrosoft: Excel Certification TopicsHow Can You Learn Quickly?A good course can save you hours of time looking for the examples on YouTube. We develop Microsoft Office Specialist Certification training. We have helped thousands of people master Excel.Please consider my FREE COURSE: Top Ten Tools in Microsoft Excel.Here are the steps: Sign into the school. Then enroll in the course.Our promise: No Cost. No ankle biters.Keep going with Excel. Knowing how to use Excel has done me well in life: as a business owner, a teacher, and a course developer.Good question. Than you for asking.eBethElizabeth Nofs, the Computer MamaNew Subscribers Welcome: https://thecomputermama.us Buy the books at Amazon: https://thecomputermama.us/books-page/ Visit our school at: https://thecomputermama.teachable.com/Exam 77-727 Excel 2016: Core Data Analysis, Manipulation, and PresentationCreate and manage worksheets and workbooksCreate worksheets and workbookCreate a workbook, import data from a delimited text file, add a worksheet to an existing workbook, copy and move a worksheetNavigate in worksheets and workbooksSearch for data within a workbook; navigate to a named cell, range, or workbook element; insert and remove hyperlinksFormat worksheets and workbooksChange worksheet tab color, rename a worksheet, change worksheet order, modify page setup, insert and delete columns or rows, change workbook themes, adjust row height and column width, insert headers and footersCustomize options and views for worksheets and workbooksHide or unhide worksheets, hide or unhide columns and rows, customize the Quick Access Toolbar, change workbook views, change window views, modify document properties, change magnification by using zoom tools, display formulasConfigure worksheets and workbooks for distributionSet a print area, save workbooks in alternative file formats, print all or part of a workbook, set print scaling, display repeating row and column titles on multipage worksheets, inspect a workbook for hidden properties or personal information, inspect a workbook for accessibility issues, inspect a workbook for compatibility issuesManage data cells and rangesInsert data in cells and rangesReplace data; cut, copy, or paste data; paste data by using special paste options; fill cells by using Auto Fill; insert and delete cellsFormat cells and rangesMerge cells, modify cell alignment and indentation, format cells by using Format Painter, wrap text within cells, apply number formats, apply cell formats, apply cell stylesSummarize and organize dataInsert sparklines, outline data, insert subtotals, apply conditional formattingCreate tablesCreate and manage tablesCreate an Excel table from a cell range, convert a table to a cell range, add or remove table rows and columnsManage table styles and optionsApply styles to tables, configure table style options, insert total rowsFilter and sort a tableFilter records, sort data by multiple columns, change sort order, remove duplicate recordsPerform operations with formulas and functionsSummarize data by using functionsInsert references, perform calculations by using the SUM function, perform calculations by using MIN and MAX functions, perform calculations by using the COUNT function, perform calculations by using the AVERAGE functionPerform conditional operations by using functionsPerform logical operations by using the IF function, perform logical operations by using the SUMIF function, perform logical operations by using the AVERAGEIF function, perform statistical operations by using the COUNTIF functionFormat and modify text by using functionsFormat text by using RIGHT, LEFT, and MID functions; format text by using UPPER, LOWER, and PROPER functions; format text by using the CONCATENATE functionCreate charts and objectsCreate chartsCreate a new chart, add additional data series, switch between rows and columns in source data, analyze data by using Quick AnalysisFormat graphic elementsResize charts, add and modify chart elements, apply chart layouts and styles, move charts to a chart sheetInsert and format objectsInsert text boxes and shapes, insert images, modify object properties, add alternative text to objects for accessibilityExam 77-728 Excel 2016 Expert: Interpreting Data for InsightsManage workbook options and settingsManage workbooksSave a workbook as a template, copy macros between workbooks, reference data in another workbook, reference data by using structured references, enable macros in a workbook, display hidden ribbon tabsManage workbook reviewRestrict editing, protect a worksheet, configure formula calculation options, protect workbook structure, manage workbook versions, encrypt a workbook with a passwordApply custom data formats and layoutsApply custom data formats and validationCreate custom number formats, populate cells by using advanced Fill Series options, configure data validationApply advanced conditional formatting and filteringCreate custom conditional formatting rules, create conditional formatting rules that use formulas, manage conditional formatting rulesCreate and modify custom workbook elementsCreate custom color formats, create and modify cell styles, create and modify custom themes, create and modify simple macros, insert and configure form controlsPrepare a workbook for internationalizationDisplay data in multiple international formats, apply international currency formats, manage multiple options for +Body and +Heading fontsCreate advanced formulasApply functions in formulasPerform logical operations by using AND, OR, and NOT functions; perform logical operations by using nested functions; perform statistical operations by using SUMIFS, AVERAGEIFS, and COUNTIFS functionsLook up data by using functionsLook up data by using the VLOOKUP function, look up data by using the HLOOKUP function, look up data by using the MATCH function, look up data by using the INDEX functionApply advanced date and time functionsReference the date and time by using the NOW and TODAY functions, serialize numbers by using date and time functionsPerform data analysis and business intelligenceReference the date and time by using the NOW and TODAY functions; import, transform, combine, display, and connect to data; consolidate data; perform what-if analysis by using Goal Seek and Scenario Manager; use cube functions to get data out of the Excel data model; calculate data by using financial functionsTroubleshoot formulasTrace precedence and dependence, monitor cells and formulas by using the Watch Window, validate formulas by using error checking rules, evaluate formulasDefine named ranges and objectsName cells, name data ranges, name tables, manage named ranges and objectsCreate advanced charts and tablesCreate advanced chartsAdd trendlines to charts, create dual-axis charts, save a chart as a templateCreate and manage PivotTablesCreate PivotTables, modify field selections and options, create slicers, group PivotTable data, reference data in a PivotTable by using the GETPIVOTDATA function, add calculated fields, format dataCreate and manage PivotChartsCreate PivotCharts, manipulate options in existing PivotCharts, apply styles to PivotCharts, drill down into PivotChart details

How to Learn Excel Online: 7 Best Resources for Excel Training1) Microsoft's Excel Training CenterPrice: FreeWhen it comes to learning a new application, why not start at the source? After all, no one knows Excel better than the people at Microsoft.In fact, they've done a great job putting together the Office Training Center: A resource hub for all Microsoft Office applications and services. The training center for Excelhas a whole bunch of free tutorials, videos, and guides on Windows, Mac OS, Android, iOS, and Windows Phone that cover the latest version of Excel, as well as older ones.Once you click into a platform, you'll find resources divided by Excel ability: For beginners (like basic math and creating a chart), intermediate users (like sorting and filtering data, conditional formatting, and VLOOKUPs), and advanced users (like pivot tables, advanced IF functions, and how to password-protect worksheets and workbooks).Source: Microsoft2) The Spreadsheet PagePrice: FreeHere's a very well-organized site that's chock full of helpful Excel tips, collected by an expert named John Walkenbach. Over the past thirty years, he's written more than 60 Excel books for users of all levels, and around 300 articles and reviews for magazines like InfoWorld, PC World,and PC/Computing. At one point, he wrote the monthly spreadsheet column for PC World. In other words, the man knows his stuff -- and he knows how to present it.The most helpful part of his website is probably the Excel Tips tab, which has a long list of useful pointers on formatting, formulas, charts and graphics, and printing. The tips themselves include everything from working with fractions, to unlinking a pivot table from its source data, to spreadsheet protection FAQs.The Downloads tab is another particularly helpful section of Walkenbach's site, where he's added free, ungated download links to files he created, like free Excel workbooks and add-ins. For example, there's one Excel workbook available for download that gives examples of custom number formats, which you can play with and tweak on your own time, and get familiar with them without having to start from scratch.3)Spreadsheets PagePrice: FreeMany of you are likely familiar with the content website About.com, but did you know it has its own spreadsheets subdomain -- much of which is devoted to Excel? There are likely thousands of instruction sets on that site, most of which are illustrated, how-to posts. Plus, fresh content is added regularly.Each piece of content is categorized according to everything from formulas and formatting, to videos, tools, and templates. If you want to stay up-to-date on the latest spreadsheet news and tips, you can sign up for a free newsletter. There's just one caveat, which is that the site contains a good amount of ads -- but if you can stand them, the content is worth it.4) Learn Excel, Charting OnlinePrice: FreePurna "Chandoo" Duggirala, Learn Excel, Charting Online's founder, says he has one goal: "to make you awesome at Excel and charting." He started the blog in 2007 and, today, it contains over 450 articles and tutorials on using Excel and making better charts. He's built the blog as a community, citing values like humility, passion, fun, and simplicity. He also works to make it a valuable resource for the folks for whom English is not their first language.Most of his tips stem from forums, where people ask questions about Excel -- about formulas, formatting, shortcuts, pivot tables, and so on -- and anyone can answer them. Chandoo then uses some of the more helpful forum questions to create articles and tutorials.Source: Chandoo.orgBut it's not all so formal. For example, Chandoo once created a digital Easter egg hunt for a blog post, which included a downloadable Excel workbook containing seven hidden pandas. Readers were challenged to locate the pandas using clues, Excel techniques, and even "I-Spy" skills.Source: Chandoo.orgWhile the articles, forums, and other parts of the site are free, you can pay to join one of Chandoo's structured training programs, like Excel School($97 - $247), or VBA Classes ($97 - $347). Plus, there's aways the option to buy one of his books -- The VLOOKUP Book or Excel Formula Helper Ebook.5) HubSpot Excel ReourcesPrice: FreeSeeing as Excel is one of the most in-demand skills for data-driven marketers -- and because we want marketers like you to succeed -- we've created some of our own educational content about Excel here at HubSpot. From free ebooks, to templates, to video tutorials, we aim to cover a wide range of Excel-relevant topics.Here are a few of our best:"How to Use Excel: The Essential Training Guide for Data-Driven Marketing" (free ebook)"9 Excel Templates to Make Marketing Easier" (free templates)"How to Create a Pivot Table in Excel: A Step-by-Step Tutorial" (video & blog post)6) Excel Tips & Solutions Since 1998Price: FreeHere's a resource that puts we mere mortals in touch with Excel experts. Excel Tips & Solutions Since 1998's claim to fame is its interactive message board, which is constantly monitored by its community of Excel gurus.The board is organized according to subject, like general announcements, questions, and Excel Tips & Solutions Since 1998 products. When a user posts a question, a member of the Excel Tips & Solutions Since 1998 expert community will reply with an answer. The questions range from simplifying an Excel task, to solving urgent inquiries.Not a native English speaker? You can ask questions in your native language.Aside from posting questions on the message board, you can also browse Mr. Excel's "Hot Topics" -- found on the left-hand side of its homepage -- which includes things like finding the cumulative sum of even or odd rows, or removing the leading zero within a text field. The site also has a library of helpful Excel books and ebooks, and if you need help with problems that are more complex, you can even hire an Excel consultant directly from the website, for a fee.7) Annielytics Video TutorialsPrice: FreeAnnie Cushing, a web analytics data expert, created the Annielytics blogand YouTube channel to share her knowledge with the world. Don't let the punny name fool you -- both are chock full of really good, specific, and in-depth web analytics tips.While the content here isn't all Excel-related -- much of it is about Google Analytics, for example -- it does contain some great Excel video tutorials. Even better, they were created with marketing and web analytics in mind, so they're directly applicable to things like marketing data reports. The Excel-specific videos can be found here, or by searching her YouTube channel for "Excel".The Excel topics vary widely, from how to create interactive pivot tables, to how to add a scrolling table to your dashboard using the INDEX function. The videos also vary in length depending on topic complexity, ranging from two-and-a-half-minutes, to those over half an hour long. To give you an idea of what the videos are like, here's one of our favorites, which covers a comprehensive overview of excel.Enjoy learning.Thanks.