Complete Techniques for Excel (365 Apps) MO-210 Exam

This lecture is aimed at beginners to Excel software. If you are inexperienced or have never used Excel, this lecture will introducer you to some of the most important aspects of the software. If you have been using Excel for some time then you may skip this lecture.

This lecture is aimed primarily at beginners to Excel software. If you are inexperienced or have never used Excel, this lecture is a vital resource and you should ensure that you review it carefully. If you have been using Excel for some time then you may skip this lecture.

This lecture is aimed at beginners to Excel software. If you are inexperienced or have never used Excel, this lecture will introduce you to the most important means for entering numeric and textual data into Excel.  If you have been using Excel for some time then you may skip this lecture, but even experienced users may find some helpful tips in the contents.

This lecture describes the various techniques needed to import external data into an Excel workbook. Following this lecture you should be able to import text files, csv files and data from the web.

Note that, for completeness and although we have yet to see it appear on the exam, a question on the importation of Excel workbooks has been included in the TIY. If you have any difficulty with the question please let us know.

Update 2026: Please note the following in relation to importing data directly from the web. The URL used in the lecture is https://en.wikipedia.org/wiki/Microsoft_Technology_Associate. This is obviously a site which is updated by Microsoft on an ongoing basis and so may not look exactly as you see it in my demonstration. There will most likely be an additional option to see multiple tables. Select each and decide which of the tables has data that is worth importing. These will be displayed in the preview window.  In our experience this technique does not generally appear in the exam.

This lecture explains the techniques involved in using the Find & Replace options on the Home tab. It also deals with the Go To and the Go To Special options.

In this section, you'll learn how to enhance your Excel workbooks by adding hyperlinks to web pages, email addresses, documents, and other resources. Key learnings include:

Inserting hyperlinks in cells, text, or objects within your workbook.

Configuring hyperlinks to external websites or URLs.

Creating hyperlinks to email addresses for quick email access.

Linking to specific cells or ranges within the same workbook.

Modifying hyperlink text and appearance for clarity and relevance.

Removing hyperlinks when they are no longer needed.

Verifying and testing hyperlinks to ensure they work correctly.

Proficiency in inserting and removing hyperlinks will enable you to make your Excel documents more interactive and user-friendly, providing easy access to external resources and enhancing the overall functionality of your workbooks.

This section focuses on the fundamental skills for customizing the layout and appearance of your Excel worksheets by adjusting row heights and column widths. Key learnings include:

• • Changing row heights to accommodate content such as text, numbers, and images.

  • Adjusting column widths to ensure that data is displayed clearly and legibly.

  • Using auto-fit options to automatically resize rows and columns to fit content.

  • Employing the "Format" options to precisely specify row heights and column widths.

  • Applying consistent formatting to multiple rows or columns simultaneously.

  • Managing row and column visibility to hide or unhide specific data.

  • Using keyboard shortcuts for quick and efficient adjustments.

Proficiency in adjusting row height and column width is essential for creating well-organized and visually appealing Excel spreadsheets. It enables you to tailor the layout to match your data presentation requirements, making your worksheets more user-friendly and effective.

• In this section, you will learn how to personalize the headers and footers of your Excel worksheets for professional-looking documents. Key learnings include:

  • Adding custom text, such as titles, page numbers, dates, and file paths, to headers and footers.

  • Utilizing built-in header and footer elements to save time and enhance document clarity.

  • Formatting and aligning header and footer content for aesthetic consistency.

  • Inserting page-specific information like the current sheet name and total pages.

  • Using different headers and footers for the first page, odd and even pages, and worksheets.

  • Applying headers and footers to specific sections of your workbook, if needed.

  • Previewing and adjusting headers and footers to ensure they meet your document requirements.

Proficiency in customizing headers and footers empowers you to create professional Excel documents with branded or detailed information in the document margins. This is particularly useful when preparing reports, invoices, or any printed or shared Excel materials where document context is crucial.

• This section focuses on the skills required to efficiently customize and manage the Quick Access Toolbar (QAT) in Excel. Key learnings include:

  • Adding frequently used commands to the QAT for quick access and increased productivity.

  • Customizing the QAT by adding, removing, or rearranging commands based on your workflow.

  • Accessing additional commands from the Ribbon and adding them to the QAT.

  • Creating and using custom groups on the QAT to organize related commands.

  • Personalizing the QAT settings for your specific Excel tasks and preferences.

  • Backing up and restoring your QAT customizations when using Excel on multiple devices.

  • Understanding the role of the QAT in providing quick and easy access to essential Excel functions.

Proficiency in managing the Quick Access Toolbar empowers you to tailor Excel to your unique workflow, making it easier to access your most-used commands and functions quickly. This customization can significantly enhance your efficiency and productivity when working with Excel.

• In this section, you'll learn how to customize the way you view and work with worksheets in Excel by using various view options. Key learnings include:

  • Normal View: Viewing worksheets in the standard layout, which includes the gridlines, headers, and data.

  • Page Layout View: Adjusting and reviewing the appearance of worksheets as they would appear when printed.

  • Page Break Preview: Identifying and modifying page breaks to control how content is divided for printing.

  • Custom Views: Saving and managing different custom views to quickly switch between specific worksheet settings.

  • Zoom Options: Adjusting the zoom level to increase or decrease the size of worksheet content.

  • Splitting Panes: Dividing the worksheet into multiple panes for simultaneous viewing and scrolling.

  • Freeze Panes: Locking specific rows or columns in place to keep them visible while scrolling through large datasets.

Proficiency in displaying and modifying worksheets in different views allows you to work more effectively in Excel. You can fine-tune the layout, optimize printing settings, and customize your view to suit different tasks and document requirements.

• In this section, you'll learn how to use the freeze panes feature in Excel to keep specific rows and columns visible while scrolling through large datasets. Key learnings include:

  • Freezing Rows: You can freeze rows at the top of your worksheet to keep header information or titles visible as you scroll down.

  • Freezing Columns: Similarly, you can freeze columns on the left side of your worksheet to keep important data or labels in view when scrolling horizontally.

  • Unfreezing Rows and Columns: Knowing how to unfreeze rows and columns is important when you want to revert to the default scrolling behavior.

  • Multiple Freeze Panes: Excel allows you to freeze both rows and columns simultaneously, providing greater control over the view.

  • Splitting Panes: Understanding how to split panes allows you to create multiple frozen sections within a worksheet.

  • Scrolling in Frozen Panes: You'll also learn how to scroll within the frozen panes to navigate your worksheet effectively.

Proficiency in freezing worksheet rows and columns is crucial for working with large Excel datasets. It ensures that important information remains visible, making data analysis and manipulation more efficient and user-friendly.

• In this section, you'll learn how to manipulate the way you view and manage multiple open workbooks and windows in Excel. Key learnings include:

  • Switching Between Workbooks: Understanding how to navigate and switch between multiple Excel workbooks when you have several open at once.

  • Splitting Windows: Learning to divide a single workbook into multiple panes or windows to view different parts of the same worksheet simultaneously.

  • Arranging Windows: Utilizing various window arrangement options like "Cascade," "Tile Horizontally," and "Tile Vertically" to organize open workbooks on your screen.

  • Viewing Different Worksheets: Knowing how to work with different worksheets within the same workbook and switching between them.

  • Zooming In and Out: Adjusting the zoom level of your Excel windows to focus on specific details or to see an overview of your data.

  • Minimizing, Maximizing, and Restoring Windows: Managing the size and visibility of Excel windows to optimize your workspace.

Proficiency in changing window views is essential for efficient multitasking and managing multiple Excel workbooks and worksheets simultaneously. It helps you tailor your Excel workspace to suit your workflow and enables you to work more productively with complex data sets and documents.

• In this section, you'll learn how to adjust the built-in properties of an Excel workbook to provide essential information and enhance document management. Key learnings include:

  • Title and Author: Changing the title and author properties to provide document identification.

  • Subject and Keywords: Adding subject and keywords to help with document categorization and search.

  • Comments: Including comments and descriptions for additional context about the workbook.

  • Tags: Assigning tags to facilitate sorting and organizing workbooks.

  • Document Properties Panel: Navigating the Document Properties Panel to access and modify these properties.

  • Custom Properties: Understanding how to create and manage custom document properties if specific information is not covered by the built-in properties.

  • Document Information Panel: Using the Document Information Panel to view and edit these properties.

Proficiency in modifying built-in workbook properties allows you to provide important metadata, making it easier to locate, identify, and manage Excel workbooks. These properties are valuable for document organization and retrieval in larger projects and shared environments.

• Formula Bar: Accessing the Formula Bar to view and edit formulas for the selected cell.

• Displaying Formulas: Understanding how to display all formulas in a worksheet instead of their calculated results.

• In this section, you'll learn how to define a specific range of cells as the print area in an Excel worksheet, ensuring that only the desired content is printed. Key learnings include:

  • Selecting Cells: Highlighting the cells or range of cells that you want to set as the print area.

  • Page Layout Tab: Accessing the "Page Layout" tab in the Excel Ribbon to find the "Print Area" option.

  • Set Print Area: Using the "Set Print Area" command to designate the selected cells as the print area.

  • Clear Print Area: Knowing how to clear the print area if you need to remove it or set a new one.

  • Print Preview: Using the "Print Preview" feature to verify how the print area will appear on paper before printing.

  • Print Settings: Adjusting print settings, such as margins, orientation, and scaling, to fine-tune the printed output.

  • Page Breaks: Ensuring that the print area aligns with page breaks for optimal page layout.

Proficiency in setting a print area is essential for controlling the printed output of your Excel worksheets. It helps you avoid unnecessary printing of irrelevant data and ensures that your documents are well-formatted and professional-looking when printed or shared.

In this section, you'll learn how to save and export Excel files in various file formats to accommodate different use cases and compatibility requirements. Key learnings include:

• Save As: Using the "Save As" command to create a copy of your Excel workbook and specify the desired file format.

• File Formats: Understanding the different file formats supported by Excel, such as XLSX, XLS, CSV, PDF, and more.

• PDF Export: Exporting Excel workbooks as PDF files to ensure they can be easily viewed and shared while preserving formatting.

• CSV Export: Saving worksheets as Comma-Separated Values (CSV) files for data interchange with other applications.

• Excel Workbook Versions: Saving files in different Excel workbook versions for compatibility with older Excel software.
  
  Proficiency in saving and exporting Excel files in alternative formats is essential for effective data sharing and collaboration with others who may use different software or have specific format preferences. It also ensures that your data retains its integrity and appearance when transferred to other applications or platforms.

  
  

• In this section, you'll learn how to customize various print settings in Excel to ensure that your documents are printed exactly as desired. Key learnings include:

  • Page Setup: Accessing the "Page Setup" dialog to configure page layout settings, including margins, orientation (portrait or landscape), and paper size.

  • Print Area: Defining the print area to specify which part of the worksheet should be printed.

  • Scaling Options: Adjusting the scale to fit content on a single page or to print on multiple pages.

  • Headers and Footers: Adding and formatting headers and footers to include page numbers, titles, dates, and other information.

  • Page Breaks: Inserting manual page breaks or configuring automatic page breaks to control how content is divided when printing.

  • Print Quality: Setting the print quality to control the resolution and quality of printed graphics and images.

  • Print Preview: Using the "Print Preview" feature to review how the document will appear when printed and make adjustments as needed.

  • Print Order: Configuring the print order for worksheets with multiple pages or sections.

Proficiency in configuring print settings is essential for ensuring that your Excel documents are printed with the correct layout, appearance, and information. It allows you to tailor the printed output to meet specific document requirements, whether it's for reports, presentations, or other purposes.

• In this section, you'll learn how to review Excel workbooks for potential issues, such as hidden data, personal information, or compatibility problems, and correct them. Key learnings include:

  • Document Inspector: Utilizing the Document Inspector tool to identify and remove sensitive or hidden information, such as document properties, comments, and workbook history.

  • Compatibility Checker: Running the Compatibility Checker to assess whether the workbook contains features or elements that may not be supported in earlier versions of Excel or other applications.

  • Accessibility Checker: Running the Accessibility Checker to ensure that the workbook is accessible to users with disabilities and making necessary adjustments.

Proficiency in inspecting workbooks and correcting issues is crucial for maintaining data accuracy, compatibility, and privacy when working with Excel. It helps ensure that your documents are error-free, accessible, and suitable for sharing with others.

• In this section, you'll learn how to effectively use comments and notes in Excel to add context, explanations, and collaborative input to your worksheets. Key learnings include:

  • Inserting Comments: Adding comments to specific cells or ranges to provide explanations, clarifications, or additional information about the data.

  • Formatting Comments: Customizing comment text, font, and appearance to make them more informative and visually appealing.

  • Displaying Comments: Learning how to show or hide comments to control their visibility on the worksheet.

  • Navigating Comments: Using navigation features to move between comments when there are multiple comments in the workbook.

  • Editing Comments: Modifying and updating existing comments to keep them current and relevant.

  • Replying to Comments: Collaborating with others by replying to comments and engaging in discussions within the workbook.

  • Notes: Differentiating between comments and notes, where notes are more suitable for adding information to individual cells without obstructing the view.

  • Review and Manage Comments: Accessing review and management tools to track changes, resolve comments, and ensure comments are addressed appropriately.

Proficiency in managing comments and notes enhances communication, collaboration, and documentation within Excel workbooks. It allows users to provide and receive feedback, maintain clarity in complex worksheets, and facilitate effective teamwork.

• In this section, you'll learn how to leverage Excel's Special Paste options to control and customize how data is pasted into worksheets. Key learnings include:

  • Copy and Cut: Understanding the basics of copying (Ctrl+C) and cutting (Ctrl+X) data in Excel.

  • Paste Values: Pasting only the values from the clipboard, excluding any formulas or formatting.

  • Paste Formulas: Pasting formulas without altering cell references.

  • Paste Formatting: Applying the formatting (e.g., font, color) from the copied cells to the destination cells.

  • Transpose: Using the Transpose option to switch rows and columns when pasting.

  • Paste as Link: Creating links between the copied data and the destination cells, so changes in the source data are reflected in the destination.

  • Paste Special Dialog Box: Accessing the Paste Special dialog box to choose from a wide range of paste options, including operations like addition, subtraction, multiplication, and division.

  • Skip Blanks: Pasting data while skipping blank cells in the destination range.

  • Paste Column Widths and Row Heights: Copying and pasting not just the data but also the column widths and row heights.

Proficiency in using Special Paste options provides greater control over data transfer in Excel. It allows you to tailor how data is pasted, ensuring that it retains its intended format and behaviour in the destination cells, which is essential for effective data manipulation and presentation.

This lecture has been included because the technique which is explained seems to appear quite option in MO-210 tasks, and the option is relatively obscure. Pay special attention!

• In this section, you'll learn how to efficiently populate cells with a series of data or patterns using Excel's Auto Fill feature. Key learnings include:

  • Auto Fill Basics: Understanding the basic functionality of Auto Fill, which enables you to extend a sequence or pattern of data across a range of cells.

  • Fill Handle: Recognizing and using the fill handle, a small square at the bottom-right corner of the selected cell, to initiate Auto Fill.

  • Fill Series: Filling cells with a series of numbers, dates, months, days of the week, or custom patterns.

  • Fill Down and Fill Across: Applying Auto Fill either vertically (Fill Down) or horizontally (Fill Across) to populate cells based on adjacent data.

  • Fill Options: Accessing and customizing Auto Fill options, such as Fill Formatting Only, Fill Without Formatting, and more, from the Auto Fill Options button.

  • Custom Lists: Creating and using custom lists in Auto Fill to extend sequences based on your specific requirements.

  • Fill with Formulas: Extending formulas using Auto Fill, taking care to adjust cell references appropriately.

  • Flash Fill: Utilizing Excel's Flash Fill feature to automate data formatting and extraction based on patterns detected in adjacent cells.

Proficiency in using Auto Fill simplifies the task of filling cells with data, patterns, or sequences, saving time and reducing errors in data entry and formatting. It is a valuable tool for managing and organizing data efficiently in Excel.

• In this section, you'll learn how to manipulate the structure of Excel worksheets by inserting and deleting multiple columns, rows, and cells. Key learnings include:

  • Insert Columns and Rows: Adding multiple columns or rows at once to accommodate data or adjust the worksheet layout.

  • Delete Columns and Rows: Removing multiple columns or rows to streamline the worksheet or eliminate unnecessary data.

  • Insert and Delete Options: Understanding the various options available for inserting and deleting, such as entire columns, rows, or specific cell shifts.

  • Cut and Paste: Using the Cut (Ctrl+X) and Paste (Ctrl+V) commands to move columns, rows, or cells from one location to another.

  • Copy and Paste: Using the Copy (Ctrl+C) and Paste (Ctrl+V) commands to duplicate columns, rows, or cells within the same worksheet or between worksheets.

  • Hide and Unhide: Hiding multiple columns or rows to temporarily remove them from view without deleting data.

Proficiency in inserting and deleting multiple columns, rows, and cells in Excel is crucial for managing and organizing data effectively. It allows you to adapt your worksheets to changing data requirements, improve data presentation, and maintain a well-structured workbook.

• The RANDBETWEEN(min, max) function generates a random integer between the specified minimum (min) and maximum (max) values, inclusive. Here's how to use it:

  • In a cell where you want to generate random data, type =RANDBETWEEN(min, max).

  • Replace min and max with the desired range for your random data. For example, =RANDBETWEEN(1, 100) will generate random integers between 1 and 100.

  • Press Enter, and Excel will calculate and display a random number within the specified range.

Generate Numeric Data by Using SEQUENCE():

• The SEQUENCE(rows, [columns], [start], [step]) function generates a sequence of numbers based on the specified parameters. Here's how to use it:

  • In a cell, type =SEQUENCE(rows, [columns], [start], [step]).

  • Replace rows with the number of rows you want in your sequence.

  • Optionally, specify columns to determine the number of columns (default is 1).

  • Optionally, set start to specify the starting value of your sequence (default is 1).

  • Optionally, define step to set the increment between numbers in the sequence (default is 1).

  • Press Enter, and Excel will create a sequence of numbers based on your criteria.

For example, to generate a sequence of numbers from 1 to 10 in a single column, you can use =SEQUENCE(10).

These functions are useful for creating datasets for testing, simulations, or any scenario where you need random or sequential numeric data in Excel.

Merge Cells:

• Learn how to combine adjacent cells into a single, larger cell for improved formatting and aesthetics in Excel worksheets.

Unmerge Cells:

• Discover how to separate merged cells back into individual cells, allowing for independent data entry and formatting.

Learn how to customize the alignment of text or content within cells, change text orientation, and apply indentation for better presentation and readability in Excel.

Discover how to quickly copy the formatting (such as fonts, colors, and cell styles) from one cell or range and apply it to other cells or ranges using the Format Painter tool in Excel.

Learn how to enable text wrapping in Excel cells to automatically adjust the row height and display long text or content within a cell in a readable format.

Discover how to format numbers in Excel to change their appearance, such as currency symbols, decimals, percentages, and more, to present data in a clear and meaningful way.

Learn how to access and use the Format Cells dialog box to apply a wide range of cell formatting options, including font styles, borders, fill colors, alignment settings, and number formats, for precise customization of cell appearance and content presentation in Excel.

Learn how to use predefined cell styles in Excel to quickly apply consistent and visually appealing formatting to your worksheets, including font styles, colors, borders, and number formats, ensuring a polished and professional appearance for your data.

Learn how to assign a descriptive name to a specific cell or range of cells in Excel, allowing for easy and meaningful referencing of that data throughout your workbook. Named ranges make formulas and functions more understandable and maintainable.

Learn how to create small, compact, and visually informative charts called sparklines within individual cells to represent trends and patterns in your data, providing a quick and concise overview of data changes over time or across categories in Excel.

Learn how to use Excel's pre-defined conditional formatting rules to automatically format cells based on specific conditions or criteria, allowing you to visually highlight important data, trends, or anomalies in your worksheets.

Discover how to remove previously applied conditional formatting rules from cells and ranges in Excel when they are no longer needed, ensuring that your worksheets maintain the desired formatting and appearance.