Pass Your Microsoft Excel 77-420 Exam Easy!

Microsoft 77-420 (Excel 2013) exam dumps vce, practice test questions, study guide & video training course to study and pass quickly and easily. Microsoft 77-420 Excel 2013 exam dumps & practice test questions and answers. You need avanset vce exam simulator in order to study the Microsoft Excel 77-420 certification exam dumps & Microsoft Excel 77-420 practice test questions in vce format.

Mastering the 77-420 Exam: Foundational Skills and Data Management

The Microsoft 77-420 Exam is the official test for earning the Microsoft Office Specialist (MOS): Excel 2013 certification. This credential is a globally recognized standard that validates a user's proficiency in the core features and functionalities of Microsoft Excel. Unlike theoretical exams, the 77-420 Exam is a hands-on, performance-based assessment. Candidates are required to perform a series of real-world tasks within a live version of the Excel 2013 application, proving their practical skills in a timed environment.

This certification is designed for a broad audience, including students, office workers, business professionals, and anyone who wants to demonstrate a high level of competence in using Excel. It certifies that an individual has the knowledge to create and edit professional-looking spreadsheets for a variety of purposes and situations. The skills tested are fundamental to roles in finance, accounting, data analysis, administration, and many other fields where spreadsheet software is an essential tool for daily work.

The scope of the 77-420 Exam is comprehensive, covering the entire lifecycle of spreadsheet management. The objectives are broken down into key skill sets, including creating and managing worksheets and workbooks, applying formulas and functions, analyzing data, and visualizing data with charts and graphs. The exam measures a candidate's ability to complete these tasks efficiently and accurately, reflecting the demands of a modern workplace.

For individuals, achieving the MOS Excel 2013 certification is a powerful addition to their professional profile. It provides clear, verifiable proof of their abilities, enhances their productivity, and can significantly improve their career prospects. For employers, it provides a reliable benchmark for assessing an individual's Excel skills, ensuring that their team members are proficient with this critical business tool.

Navigating the Excel 2013 Interface

To succeed in the 77-420 Exam, a candidate must be completely fluent in navigating the Excel 2013 user interface. Speed and confidence in finding the right tools are essential in a timed exam setting. The primary component of the interface is the Ribbon, which is located at the top of the screen. The Ribbon is organized into a series of tabs, such as "Home," "Insert," "Page Layout," and "Formulas," each corresponding to a major area of functionality.

Each tab on the Ribbon is further divided into groups of related commands. For example, the "Home" tab contains groups for "Font," "Alignment," and "Number," which house the most commonly used formatting tools. The 77-420 Exam requires you to know exactly which tab and group to go to for a specific task without hesitation. For instance, you should know instinctively that chart creation tools are on the "Insert" tab, while options for setting print titles are on the "Page Layout" tab.

Above the Ribbon is the Quick Access Toolbar (QAT). This toolbar provides one-click access to frequently used commands, such as "Save," "Undo," and "Redo." A key skill for efficiency is knowing how to customize the QAT to add other commands that you use often. Below the Ribbon is the Formula Bar, which displays the content of the active cell and is where you can enter or edit formulas and data.

Finally, the Status Bar at the bottom of the window provides useful information and quick calculations. For example, if you select a range of numbers, the Status Bar can automatically display their sum, average, and count. Mastering the layout and function of each of these interface elements is the first step toward building the speed and accuracy needed for the 77-420 Exam.

Managing Worksheets and Workbooks

A fundamental skill set tested in the 77-420 Exam is the ability to create, manage, and organize both worksheets and workbooks. An Excel workbook is the file itself, which acts as a container for one or more worksheets. A worksheet is the grid of rows and columns where you enter and work with your data. A candidate must be proficient in all the basic operations related to these objects.

This includes the ability to insert new worksheets into a workbook, delete worksheets that are no longer needed, and rename worksheets to give them meaningful names. You must also know how to reorder worksheets by dragging their tabs and how to change the color of a worksheet tab for better visual organization. A key skill is the ability to group worksheets together to perform the same action, such as applying formatting or entering data, on multiple sheets simultaneously.

At the workbook level, the 77-420 Exam requires you to know how to manage the file itself. This includes saving the workbook in the standard Excel format (.xlsx), as well as exporting it to other common formats like Comma Separated Values (.csv) or Portable Document Format (.pdf). You should also be familiar with inspecting the workbook for hidden properties and personal information and understanding how to modify the core properties of a workbook, such as the author and title.

These management tasks are the foundation of good spreadsheet organization. The exam will present you with scenarios that require you to quickly and accurately manipulate the structure of a workbook, making these skills essential for success.

Entering and Formatting Data

The most basic function of Excel is to store and format data. The 77-420 Exam will thoroughly test your ability to enter data correctly and to apply various formatting options to make the data clear, readable, and professional. This starts with understanding the different types of data that can be entered into a cell, which primarily include text, numbers, and dates/times. Excel automatically recognizes the type of data as you enter it, which affects how it is aligned and how it can be used in calculations.

Once the data is entered, formatting is used to control its appearance. The exam requires you to be proficient with the formatting tools found on the "Home" tab of the Ribbon. This includes applying font styles (like bold, italic, and underline), changing the font size and color, and applying fill colors to cells. You must also be able to merge cells to create titles and use the alignment options to control the position of data within a cell, including wrapping text.

A particularly important area is the application of number formats. You must be able to apply common formats like Currency, Accounting, Percentage, and different Date and Time formats. The ability to use the Format Painter tool is also a key efficiency skill. The Format Painter allows you to quickly copy the formatting from one cell or range and apply it to another, which is much faster than reapplying each formatting option manually.

These formatting skills are not just about making the spreadsheet look good; they are about making the data easier to understand and interpret. The 77-420 Exam will require you to apply a variety of these formatting options precisely as instructed.

Modifying Cell Content, Rows, and Columns

Beyond initial data entry and formatting, a candidate for the 77-420 Exam must be an expert in modifying the structure and content of a worksheet. This includes a complete command of the standard cut, copy, and paste operations. A key area to master is the "Paste Special" feature, which gives you granular control over what gets pasted. For example, you can choose to paste only the values, only the formatting, or only the formula from a copied cell. You can also use Paste Special to perform a mathematical operation or to transpose data from a row to a column.

Managing the layout of the worksheet by inserting and deleting rows and columns is another fundamental skill. You must be able to perform these actions quickly and understand how they affect the formulas and references in your sheet. This also includes the ability to hide and unhide rows and columns to temporarily conceal data without deleting it.

Adjusting the size of rows and columns is a frequent task. The exam will expect you to know how to manually set a specific row height or column width, as well as how to use the "AutoFit" feature to automatically adjust the size to fit the content. Freezing panes is another critical skill for working with large datasets. You must know how to freeze the top row and/or the first column so that they remain visible as you scroll through the data.

These modification skills are the bread and butter of daily Excel work. The 77-420 Exam will present tasks that require you to manipulate the worksheet structure efficiently and accurately, making mastery of these features essential.

Using Find and Replace

When working with large worksheets, the ability to quickly locate and modify specific pieces of data is crucial. The Find and Replace functionality in Excel is a powerful tool for this purpose, and its use is a key competency for the 77-420 Exam. The standard "Find" feature allows you to search for a specific text string or number within a worksheet or the entire workbook.

However, the exam will expect you to be familiar with the more advanced options available in the Find and Replace dialog box. For example, you can configure the search to be case-sensitive or to only find cells where the entire cell content matches your search term. You can also search within formulas, values, or comments, providing a high degree of control over the search.

The "Replace" functionality builds on this by allowing you to replace the found text with another value. You can choose to replace a single instance or to replace all occurrences at once. This is an incredibly powerful tool for making bulk corrections to a dataset.

A particularly useful advanced feature is the ability to find and replace based on formatting. For example, you could search for all the cells in a worksheet that are formatted with a specific yellow fill color and then replace that formatting with a green fill color. A candidate for the 77-420 Exam should be comfortable with all aspects of the Find and Replace tool, from simple text searches to more complex format-based replacements.

Configuring Print and View Options

A spreadsheet is often created with the intention of being printed or shared as a report. The 77-420 Exam requires a candidate to be proficient in configuring the various view and print options to ensure that the final output is professional and easy to read. Excel provides several different views to help with this process. The "Normal" view is used for most data entry and formatting, but the "Page Layout" view shows you exactly how the worksheet will look when it is printed, including headers, footers, and margins.

The "Page Break Preview" is another essential view. It shows you where the automatic page breaks will occur and allows you to manually adjust them by dragging the page break lines. This gives you complete control over what content appears on each page of the printed report.

From the "Page Layout" tab on the Ribbon, you can control all the critical print settings. A key skill is the ability to set a specific "Print Area." This allows you to define a specific range of cells that should be printed, ignoring all other data on the worksheet. You must also know how to set "Print Titles," which allows you to repeat specific rows or columns (like a header row) on every printed page, which is essential for multi-page reports.

Other important settings include adjusting the page orientation (portrait or landscape), setting the margins, and scaling the output to fit on a specific number of pages. You must also be able to add and customize headers and footers to include information like the page number, date, or file name. Mastery of these print settings is a required skill for the 77-420 Exam.

Preparing for the 77-420 Exam: Mastering the Basics

As you begin your preparation for the 77-420 Exam, the most important strategy is to build a flawless foundation in the basic skills. This exam is a test of both knowledge and speed. The tasks themselves are not necessarily complex, but the timed nature of the exam means that you must be able to perform them quickly, accurately, and without hesitation. Therefore, your initial focus should be on achieving complete mastery of the fundamental operations.

Before you even start memorizing complex function syntax, you should be able to navigate the Ribbon and the Quick Access Toolbar with your eyes closed. Practice all the basic data entry, formatting, and worksheet management tasks until they become second nature. You should not have to spend any time during the exam searching for the "Merge & Center" button or trying to remember how to freeze panes.

A great way to practice is to download sample datasets and then try to replicate a formatted report. This will force you to use all the core skills in a practical context. Time yourself as you perform these tasks. The goal is to build up both your accuracy and your speed. Remember that the 77-420 Exam is a performance-based test, and your performance is measured by your ability to complete the required tasks within the allotted time.

By investing the time upfront to master these foundational skills, you will be able to handle the basic tasks on the exam quickly, leaving you more time and mental energy to focus on the more complex questions that involve formulas, functions, and data analysis. A solid foundation is the key to a calm and confident performance on exam day.

The Core of Excel Formulas for the 77-420 Exam

The true power of Excel lies in its ability to perform calculations, and a deep understanding of formulas and functions is at the heart of the 77-420 Exam. A formula is an expression that calculates the value of a cell. Every formula in Excel begins with an equals sign (=). Following the equals sign, a formula can contain constants, operators (like + for addition or * for multiplication), and, most importantly, references to other cells.

A critical concept you must master is cell referencing. Excel supports three types of cell references: relative, absolute, and mixed. A relative reference (e.g., A1) will automatically adjust when the formula is copied to other cells. An absolute reference (e.g., $A$1), created by using dollar signs, will not change when the formula is copied. A mixed reference (e.g., $A1 or A$1) locks either the column or the row, but not both.

The ability to use these different reference types correctly is essential for building flexible and scalable spreadsheets and is a frequent topic on the 77-420 Exam. For example, when calculating sales tax, you would use an absolute reference to a single cell that contains the tax rate, so that all your calculations consistently point to that one cell.

A formula can also contain a function, which is a predefined calculation. Excel has a vast library of built-in functions that can perform a wide range of tasks, from simple mathematical sums to complex financial or statistical calculations. Proficiency in using the most common functions is a major requirement for the exam.

Working with Basic Mathematical and Statistical Functions

The 77-420 Exam will expect you to be completely fluent in the most common mathematical and statistical functions. These are the workhorse functions that are used in countless spreadsheets every day. While they may seem simple, you must be able to use them quickly and accurately in a variety of scenarios. The most basic of these is the SUM function, which is used to add together a range of numbers.

Building on this, the AVERAGE function calculates the arithmetic mean of a range of numbers. The MIN and MAX functions are used to find the smallest and largest values in a range, respectively. These four functions provide the foundation for most basic data summary tasks.

For counting, Excel provides several related functions. The COUNT function is used to count the number of cells in a range that contain numeric values. The COUNTA function is similar, but it counts the number of cells in a range that are not empty, regardless of whether they contain numbers or text. The COUNTBLANK function does the opposite, counting the number of empty cells in a range.

The 77-420 Exam will not just ask you to write a simple =SUM(A1:A10) formula. It will present you with a dataset and a problem, and you will need to choose the correct function to solve that problem. For example, you might be asked to find the average sales for a particular region or to count the number of employees who have completed their training.

Mastering Logical Functions

Logical functions allow you to introduce decision-making into your spreadsheets. They are an incredibly powerful tool for creating dynamic and intelligent models, and a deep understanding of them is a core requirement for the 77-420 Exam. The most important logical function, and one you must master completely, is the IF function.

The IF function allows you to perform a logical test and then return one value if the test is true, and another value if the test is false. The syntax is =IF(logical_test, value_if_true, value_if_false). For example, you could use an IF function to check if a student's score is greater than 60 and return "Pass" if it is true, or "Fail" if it is false.

For more complex conditions, you can use the AND, OR, and NOT functions, often nested inside an IF function. The AND function returns true only if all of its arguments are true. The OR function returns true if any of its arguments are true. For example, to give a bonus to an employee who is in the "Sales" department AND has sales greater than $50,000, you would use an AND function inside your IF function's logical test.

The exam may also require you to understand how to create "nested" IF functions, where one IF function is placed inside another to handle more than two possible outcomes. For example, you could use a nested IF to assign a letter grade (A, B, C, D, or F) based on a student's score. The ability to build these logical formulas is a key skill for a certified user.

Using Text Functions

While Excel is often used for numbers, it also has a rich set of functions for manipulating text strings. These text functions are a key part of the 77-420 Exam curriculum, as they are essential for cleaning and preparing data for analysis. One of the most common tasks is combining text from multiple cells. This can be done with the CONCATENATE function or, more simply, by using the ampersand (&) operator.

Another common requirement is to extract a portion of a text string. Excel provides several functions for this. The LEFT function extracts a specified number of characters from the beginning of a string. The RIGHT function extracts from the end of a string. The MID function allows you to extract characters from the middle of a string, by specifying a starting position and the number of characters to extract.

For cleaning up text data, functions like TRIM, UPPER, LOWER, and PROPER are invaluable. The TRIM function removes any leading or trailing spaces from a text string. The UPPER, LOWER, and PROPER functions are used to convert text to all uppercase, all lowercase, or proper case (where the first letter of each word is capitalized), respectively.

The LEN function is used to find the total number of characters in a text string. The 77-420 Exam will expect you to be able to combine these functions to solve common data cleaning problems, such as extracting a first name from a full name or standardizing the case of a list of city names.

A Deep Dive into Lookup Functions

The ability to look up a value in a table of data is one of the most powerful and frequently used capabilities in Excel. The 77-420 Exam requires a complete mastery of the primary lookup functions, VLOOKUP and HLOOKUP. These functions are essential for bringing together data from different lists or tables within your workbook.

The VLOOKUP function is used for vertical lookups. It searches for a value in the first column of a table and returns a corresponding value from a specified column in the same row. The function has four arguments that you must understand perfectly. The first is the lookup_value, which is the value you are searching for. The second is the table_array, which is the range of cells that contains the data table.

The third argument is the col_index_num, which is the number of the column in the table from which you want to retrieve the value. The final argument is the range_lookup, which is a logical value that specifies whether you want to find an exact match (FALSE) or an approximate match (TRUE). For most business uses, you will use FALSE for an exact match.

The HLOOKUP function works in exactly the same way as VLOOKUP, but it performs a horizontal lookup, searching for a value in the top row of a table and returning a value from a specified row. The 77-420 Exam will present you with scenarios where you must construct a correct VLOOKUP or HLOOKUP formula to retrieve the required information.

Date and Time Functions

Excel has a powerful set of functions for working with dates and times, and proficiency in their use is a requirement for the 77-420 Exam. Dates and times in Excel are stored as serial numbers, which allows you to perform mathematical calculations on them. For example, you can subtract one date from another to find the number of days between them.

Several functions are used to work with the current date and time. The TODAY() function returns the current date. The NOW() function returns the current date and time. These functions are dynamic, meaning they will update every time the worksheet is recalculated.

Other functions are used to deconstruct a date into its component parts. The YEAR, MONTH, and DAY functions take a date as input and return the corresponding year, month, or day number. The DATE function does the opposite; it takes a year, month, and day as input and constructs a valid Excel date.

These functions are often combined to perform more complex date calculations. For example, you could use them to calculate an invoice due date that is 30 days from the invoice date, or to calculate an employee's age based on their birth date. The 77-420 Exam will expect you to be able to use these functions to solve common business problems that involve date-based calculations.

Auditing Formulas

When you are working with a complex spreadsheet that contains many interdependent formulas, it can become difficult to understand how the calculations are flowing. To help with this, Excel provides a set of powerful formula auditing tools. The ability to use these tools to trace and debug formulas is a key skill for the 77-420 Exam, as it demonstrates an advanced level of understanding.

The two most important auditing tools are "Trace Precedents" and "Trace Dependents." The "Trace Precedents" tool draws arrows on the worksheet that show you which cells are directly referenced by the formula in the currently selected cell. This helps you to quickly see all the inputs to a particular calculation.

The "Trace Dependents" tool does the opposite. It draws arrows from the currently selected cell to all the other cells that contain a formula that references it. This shows you all the calculations that would be affected if you were to change the value in the current cell. You can use these tools repeatedly to trace the entire flow of a calculation across the worksheet.

Another useful tool is "Show Formulas," which is a toggle that displays the formulas in the cells instead of their calculated results. This provides a quick way to review all the formulas in a sheet at once. The "Evaluate Formula" tool allows you to step through a complex, nested formula one calculation at a time to see how it arrives at its final result. These tools are invaluable for finding and fixing errors in your formulas.

Defining and Using Named Ranges

As your formulas become more complex, they can become difficult to read and understand, especially if they contain a lot of absolute cell references like $A$1. To make your formulas more readable and your spreadsheets easier to maintain, Excel provides a feature called "Named Ranges." The ability to create and use named ranges is an important topic for the 77-420 Exam.

A named range is simply a user-friendly name that is given to a cell or a range of cells. For example, instead of referring to cell B1 as $B$1 in your formulas, you could give that cell the name "TaxRate." Then, your formulas could be written as =A10*TaxRate, which is much more intuitive and self-documenting than =A10*$B$1.

Named ranges can be created using the Name Box (to the left of the formula bar) or, more formally, through the Name Manager, which is found on the "Formulas" tab of the Ribbon. The Name Manager allows you to create, edit, and delete all the named ranges in your workbook.

In addition to making formulas more readable, named ranges are also easier to use when navigating a large workbook. You can simply select the name from the Name Box dropdown list, and Excel will immediately jump to that range. Using named ranges is a key best practice for building professional and maintainable Excel models, and the 77-420 Exam will expect you to be proficient in their use.

Data Analysis Techniques for the 77-420 Exam

Beyond performing calculations, a primary use of Excel is to manage and analyze lists of data. The 77-420 Exam dedicates a significant portion of its objectives to the tools and techniques used for data analysis. A certified user must be proficient in the methods used to organize, query, and summarize large datasets to extract meaningful insights. This involves a set of core skills that transform a simple list of data into a powerful analytical tool.

The foundational techniques for data analysis in Excel are sorting and filtering. Sorting allows you to arrange your data in a specific order, which can make it easier to find information and spot trends. Filtering allows you to temporarily hide the rows that you are not interested in, so that you can focus on a specific subset of your data. The exam requires mastery of both basic and advanced sorting and filtering techniques.

A key best practice for data analysis is to format your data range as an Excel Table. An Excel Table is a special object that provides a range of powerful features for managing structured data, including enhanced sorting and filtering capabilities and the use of structured references in formulas.

Other important data management techniques covered in the 77-420 Exam include tools for cleaning data, such as removing duplicate values, and features for controlling data entry, such as data validation. A comprehensive understanding of these tools is essential for any user who needs to work with data effectively in Excel.

Sorting Data on Single and Multiple Levels

Sorting is the most basic way to organize a list of data, and the ability to perform sorts quickly and correctly is a fundamental skill for the 77-420 Exam. The simplest type of sort is a single-level sort, where you arrange the data based on the values in a single column. For example, you could sort a list of employees alphabetically by their last name, or sort a list of sales transactions from the largest sale to the smallest.

Excel provides simple one-click buttons for performing these basic ascending (A to Z, smallest to largest) and descending (Z to A, largest to smallest) sorts. However, for more control, you must use the "Sort" dialog box, which is found on the "Data" tab of the Ribbon.

The Sort dialog box is where you can perform a multi-level sort. A multi-level sort allows you to sort your data by more than one column. For example, you could sort a list of employees first by their department (the first level) and then, within each department, sort them by their last name (the second level). You can add many levels to a sort to create a highly organized list.

The Sort dialog box also gives you more options for each sort level. You can choose to sort based on the cell values, the cell color, the font color, or a cell icon. For the 77-420 Exam, you must be completely comfortable with the Sort dialog box and be able to quickly configure a multi-level sort based on a given set of requirements.

Filtering Data with AutoFilter

When you are working with a large list of data, you often want to focus on a specific subset of that data. The AutoFilter feature is the primary tool for this, and its use is a core competency for the 77-420 Exam. When you apply AutoFilter to a range, a small drop-down arrow appears in the header cell of each column.

Clicking on this drop-down arrow reveals a list of all the unique values in that column, along with a series of filter options. The simplest way to filter is to select one or more of the unique values from the list. For example, in a list of sales data, you could click the filter arrow in the "Region" column and select only the "North" region to see the sales for that area.

The filter menu also provides options for criteria-based filtering. For a text column, you can filter for rows that "begin with" a certain letter or "contain" a specific word. For a number column, you can filter for values that are "greater than," "less than," or "between" a certain range. You can also filter by cell color or font color.

You can apply filters to multiple columns at the same time to progressively narrow down your data set. For example, you could filter for the "North" region and then apply another filter to the "Product" column to see the sales of a specific product only within that region. The ability to apply these filters quickly and accurately is an essential data analysis skill.

Using Advanced Filtering

While the AutoFilter is excellent for most day-to-day filtering tasks, Excel provides a more powerful and flexible tool called the "Advanced Filter" for handling more complex query requirements. An understanding of the concepts behind the Advanced Filter is a topic covered in the 77-420 Exam. The Advanced Filter allows you to define complex filter criteria in a separate range of cells on your worksheet.

To use the Advanced Filter, you must first set up a "criteria range." This range must have the exact same column headers as your data list. Below these headers, you specify the criteria for your filter. If you enter criteria in the same row, they are treated as an "AND" condition (e.g., Region = "North" AND Sales > 5000). If you enter criteria in different rows, they are treated as an "OR" condition (e.g., Region = "North" OR Region = "South").

Once the criteria range is set up, you can run the Advanced Filter tool from the "Data" tab. You will need to specify the location of your data list and the location of your criteria range. The Advanced Filter can either filter the list in-place (like AutoFilter) or it can extract a copy of the matching rows to a different location on the worksheet.

The Advanced Filter is a powerful tool for performing complex queries that are not possible with the standard AutoFilter. The 77-420 Exam will expect you to understand the concept of a criteria range and how to use it to perform both AND and OR-based filtering.

Working with Excel Tables

One of the most powerful features for managing structured data in Excel is the "Table" feature. The 77-420 Exam places a strong emphasis on the ability to work with Excel Tables. When you format a range of data as a Table (using the "Format as Table" button on the "Home" tab or the shortcut Ctrl+T), Excel treats that range as a single, related object. This provides a host of benefits that make the data much easier to manage and analyze.

First, tables have a distinct and professional appearance, with banded rows and a clear header row. The filter and sort buttons are automatically added to the header row. As you scroll down a large table, the column headers will automatically replace the column letters at the top of the screen, so you always know what data you are looking at.

A major advantage of tables is that they automatically expand to include new rows or columns that you add. This means that any formulas, charts, or PivotTables that are based on the table will automatically update to include the new data, without you having to manually adjust their source range.

Perhaps the most powerful feature is "structured referencing." Instead of using cell references like A2, you can write formulas that use the table and column names, such as =Table1[Sales]. This makes formulas much more readable and less prone to breaking when the table structure changes. The exam will expect you to be able to create a table and understand all these key benefits.

Removing Duplicate Data

Data that is collected from multiple sources often contains duplicate records. Before performing any analysis, it is a common data cleaning task to identify and remove these duplicates. Excel 2013 provides a simple and powerful tool for this purpose, and its use is a skill that is tested on the 77-420 Exam. The "Remove Duplicates" tool is found on the "Data" tab of the Ribbon.

To use the tool, you simply select a cell within your data range and click the "Remove Duplicates" button. A dialog box will appear that shows a list of all the columns in your data. By default, the tool will consider a row to be a duplicate only if the values in all the columns are identical to another row.

However, you can customize the behavior by selecting which columns the tool should use to identify duplicates. For example, if you have a list of customers, you might choose to identify duplicates based only on the "Email Address" column. In this case, if two rows have the same email address, the tool will consider them to be duplicates and will remove one of them, even if the other data in the rows (like the name or address) is slightly different.

The Remove Duplicates tool is a fast and efficient way to clean up a dataset. It is an essential skill for anyone who works with real-world data, and the 77-420 Exam will expect you to be able to use it to remove unwanted duplicate records based on a specific set of criteria.

Data Validation and Input Control

To ensure the integrity and accuracy of the data in your spreadsheets, it is often necessary to control what kind of information a user can enter into a cell. The "Data Validation" feature is the primary tool for this, and its configuration is a key topic for the 77-420 Exam. Data Validation allows you to create a rule for a cell or a range of cells that restricts the type of data that can be entered.

For example, you could use data validation to ensure that a user can only enter a whole number between 1 and 100 in a specific cell. If they try to enter text or a number outside of this range, the system will show an error message and reject the entry. You can customize this error message to provide clear guidance to the user.

A very common use of data validation is to create a drop-down list. You can configure a cell so that the user can only choose a value from a predefined list of options. This is an excellent way to prevent spelling mistakes and to ensure that the data entered is consistent. The source for this list can be a range of cells elsewhere in the workbook.

Data Validation also allows you to create an "input message." This is a small pop-up message that appears when a user selects the cell, providing them with instructions on what kind of data to enter. Using data validation is a key technique for building robust and user-friendly data entry forms.

Consolidating and Grouping Data

When working with detailed transactional data, it is often useful to summarize or consolidate it to see the bigger picture. The 77-420 Exam covers some of the built-in tools that Excel provides for this purpose. One such tool is the "Subtotal" feature, which is found on the "Data" tab. The Subtotal feature automatically inserts summary rows into a sorted list of data.

To use the Subtotal tool, you must first sort your data by the column you want to group by. For example, you would sort a list of sales data by the "Region" column. Then, you can run the Subtotal tool and tell it to insert a new row every time the value in the "Region" column changes. In that new row, you can have it perform a calculation, such as a SUM of the "Sales" column for that region.

When you use the Subtotal tool, it also automatically creates an "outline" for your data. This outline appears on the left side of the worksheet and provides buttons that allow you to quickly collapse or expand the view to show just the summary rows or all the detail rows.

You can also create these outlines manually by using the "Group" and "Ungroup" features. This allows you to group together specific rows or columns so that they can be easily hidden or displayed with a single click. These tools are useful for creating summary reports and for making large, detailed worksheets easier to navigate.

Visualizing Data for the 77-420 Exam

While numbers and tables are the foundation of a spreadsheet, the most effective way to communicate insights and trends from data is often through visualization. The 77-420 Exam places a strong emphasis on a user's ability to create, format, and interpret charts and other graphic objects. A chart can transform a complex table of data into a simple, easy-to-understand visual story, making it a critical skill for any professional who needs to present data to others.

Excel 2013 offers a rich library of chart types, each designed to represent data in a different way. A key skill for the exam is not just knowing how to create a chart, but knowing which type of chart is the most appropriate for a given dataset and the message you are trying to convey. For example, a line chart is ideal for showing a trend over time, while a pie chart is best for showing the proportional contribution of different categories to a whole.

The exam curriculum covers the entire process of chart creation, from selecting the source data to formatting the final chart to meet professional standards. This includes the ability to modify all the individual elements of a chart to make it as clear and impactful as possible.

In addition to traditional charts, the 77-420 Exam also covers other visualization techniques, such as the use of in-cell charts called sparklines and the application of conditional formatting to highlight important data directly within the cells. A mastery of these visualization tools is essential for demonstrating a comprehensive understanding of Excel's capabilities.

Creating and Formatting Charts

The process of creating a chart in Excel 2013 is designed to be straightforward, but a candidate for the 77-420 Exam must be proficient in all the options. The process begins by selecting the range of data that you want to visualize. All the chart creation tools are located on the "Insert" tab of the Ribbon. Excel's "Recommended Charts" feature can analyze your data and suggest the chart types that would be most suitable.

The most common chart types that you must be familiar with are Column, Bar, Line, and Pie charts. A Column chart is used to compare values across different categories. A Bar chart is essentially a horizontal column chart and is often used when the category labels are long. A Line chart is used to show a trend over a continuous period, such as months or years. A Pie chart is used to show the breakdown of a total into its constituent parts.

Once a chart is created, it appears on the worksheet as an object that can be moved and resized. When the chart is selected, two new contextual tabs, "Design" and "Format," appear on the Ribbon. These tabs contain all the tools needed to customize the chart's appearance. The "Design" tab allows you to change the overall style and color scheme of the chart, switch the rows and columns, and change the chart type itself.

The 77-420 Exam will require you to create a specific type of chart from a given data set and then apply a specific style or layout to it. Speed and accuracy in these initial creation and formatting steps are crucial.

Modifying Chart Elements

A standard chart is made up of several distinct elements, and the 77-420 Exam requires you to know how to add, remove, and format each of these elements to create a clear and professional-looking visualization. A proficient user must be able to manipulate all the components of a chart to tailor it to their specific needs.

The most common elements to work with are the Chart Title and the Axis Titles. These provide context for what the chart is showing. The Legend is another key element, which identifies what each different colored series in the chart represents. Data Labels can be added to display the exact value of each data point directly on the chart, which can make it easier to read than having to refer back to the axis.

You can also add a Data Table below the chart, which shows the source data in a grid format. The axes themselves can be heavily formatted. You can change the scale of a value axis, for example, by setting the minimum and maximum bounds. You can also change the number format used on the axis labels.

The primary way to manage these elements in Excel 2013 is through the "Chart Elements" button, which appears as a plus sign next to the selected chart. This provides a simple checklist interface for adding or removing the main chart components. For more detailed formatting, you can right-click on any element and choose the "Format" option to open a detailed task pane.

Advanced Chart Customization

Beyond managing the basic elements, the 77-420 Exam will expect you to be familiar with some more advanced chart customization techniques. These are the skills that allow you to create highly tailored and sophisticated data visualizations. One common requirement is the need to change the chart type of a single data series within a chart. For example, you could have a chart that shows monthly sales as columns and the sales target as a line, creating a combination chart.

Another powerful advanced technique is the use of a secondary axis. This is useful when you are plotting two different data series on the same chart, but their values are on a vastly different scale. For example, you might want to plot the number of units sold (which could be in the thousands) and the average price (which might be in the tens). By plotting one of these series on a secondary vertical axis, you can make the chart readable.

You can also apply detailed formatting to the chart's background areas. The "Chart Area" is the entire chart object, while the "Plot Area" is the specific area where the data is plotted. You can apply fill colors, gradients, pictures, or borders to these areas to match your corporate branding or to enhance the visual appeal of the chart.

These advanced techniques provide a high degree of control over the final appearance and effectiveness of your charts. The 77-420 Exam may present you with a standard chart and ask you to perform a specific advanced customization, testing your ability to find and use these powerful features.

Using Sparklines for In-Cell Visualization

Not all data visualization requires a large, separate chart object. For quickly visualizing a trend within a single row of data, Excel provides a feature called Sparklines. An understanding of how to create and format sparklines is a key skill for the 77-420 Exam. A sparkline is a tiny chart that is placed inside a single cell. It provides a compact and powerful way to visualize data without taking up a lot of space on the worksheet.

There are three main types of sparklines. A "Line" sparkline shows the trend of the data as a miniature line chart. A "Column" sparkline represents the data as a series of tiny vertical bars. A "Win/Loss" sparkline is a special type that is used to show a sequence of positive and negative values, with positive values shown as an upward-pointing bar and negative values as a downward-pointing bar.

Creating sparklines is very straightforward. From the "Insert" tab of the Ribbon, you choose the type of sparkline you want. You then select the range of data that the sparkline should represent and the cell where you want the sparkline to be placed.

Once a sparkline is created, a contextual "Design" tab appears on the Ribbon. This tab allows you to customize the appearance of the sparkline. You can change its color, and you can choose to highlight specific points, such as the high point, the low point, or any negative points. Sparklines are an excellent tool for creating data-dense dashboards and reports.

Conclusion

In addition to charts, a professional-looking spreadsheet often includes other graphic objects to enhance its appearance and to communicate information. The 77-420 Exam requires you to be proficient in inserting and formatting these objects. This includes working with pictures, shapes, and SmartArt graphics.

You can insert a picture from a file on your computer to add a logo or other image to your worksheet. Once a picture is inserted, a contextual "Format" tab appears, which provides a rich set of tools for modifying the picture. You can crop the picture, apply artistic effects and color corrections, add a border, and apply various picture styles.

Shapes are a set of predefined vector graphics, such as rectangles, ovals, arrows, and flowchart symbols, that you can draw directly on the worksheet. Shapes are often used to create diagrams or to draw attention to a specific part of the spreadsheet. You can add text to a shape and apply a wide range of formatting options, including fill colors, gradients, outlines, and effects like shadows and glows.

SmartArt is a tool for creating sophisticated business diagrams, such as organization charts, process flows, and cycle diagrams. You choose a layout from a gallery, and then simply type your text into a text pane. SmartArt automatically arranges and formats the shapes for you, allowing you to create professional-looking diagrams with very little effort.

Go to testing centre with ease on our mind when you use Microsoft Excel 77-420 vce exam dumps, practice test questions and answers. Microsoft 77-420 Excel 2013 certification practice test questions and answers, study guide, exam dumps and video training course in vce format to help you study with ease. Prepare with confidence and study using Microsoft Excel 77-420 exam dumps & practice test questions and answers vce from ExamCollection.