Module 7 - LinkedIn - Excel Essential Training (Office/Microsoft 365)

Worksheets & Workbooks

- sheet names can have up to 31 spaces, can have spaces and underscores - you can only unhide one sheet at a time

To use AutoSum with a row of four values, where must your cursor be to have the sum appear in the row if you have not highlighted the cells?

on the first empty cell to the right of the last cell containing a value

Getting Started with Excel / Entering Data

- Right mouse button - shortcut menu plus mini toolbar -

You have a worksheet with multiple hidden columns and you need to view all of the data that is hidden. What is the best way to unhide the columns?

Select the entire worksheet and double-click any column boundary.

The undo command

cannot delete a worksheet

If you have a worksheet containing data you do not want to be sorted, what must you do before sorting?

Delineate the data you do not want to be sorted.

As you are entering sales data into a new worksheet, you inadvertently type 06/31/2019. Which action will Excel take with this?

Display it as 06/31/2019, but it will be left-justified and just a set of numbers and dashes, not an actual date.

What action will edit locked cells in a protected worksheet?

Enter the password to turn off the protection.

You want to view how your worksheet will print and make edits to the worksheet in this mode. What is the easiest way to accomplish this?

Click Page Layout on the bottom of the page in the status bar.

XLOOKUP Functions

Replaces/combines VLOOKUP and HLOOKUP in Excel 2019 =xlookup(lookup value, value, lookup array, return array,

You are entering Sales data for each month in 2018. How can you quickly enter all 12 months in columns?

Type in "January" point to the lower right-hand corner of the cell, hold down the left mouse button and drag across the next 11 rows.

How can you quickly add a border around a set of cells covering 3 rows and 12 columns?

Use Draw Border under Border on the ribbon under Home

Your worksheet includes data for your company's IT department. How can you use Find and Select to quickly capitalize the many instances of IT appearing in the worksheet?

Use the Match entire cell contents option under Replace

Splitting Screens Horizontally & Vertically

- View Tab - Split - To remove it - go back to View Tab, or just double click on the line. - Click to the right of where you want the split for vertical.

Charts

Create a Chart from Existing Data - Do not include totals. Do include headers - Alt+F1 = shortcut key (creates clustered column chart) - Chart Tools Tab appears (Design & Format Tabs) - Alt Drag causes cell boundaries to line up - Create Chart in Separate Sheet - F11 - OR Insert Recommended Charts - Quick Analysis button appears in bottom right corner of chart Chart Types - Most common clustered column chart (autocreated) - Design Tab - change chart type Working with Excel Ideas - previously called Insights renamed Ideas - Home - Lightning Bolt (Ideas) Tab

You manage the worksheet shown below that contains a list of employees and a list of building numbers. The employee list is 1500 rows of data. What is the easiest way to sort the employee list without affecting the building number list?

Add a blank column between Columns B & C, click in cell A2 and select Sort.

You have a workbook with multiple worksheets and want an exact copy of one of the worksheets. How can you duplicate this sheet within the same workbook?

Click in the tab name of the worksheet, hold the Ctrl key, and drag the worksheet.

Which feature can only be accessed from the Format Cells dialog box?

Double Accounting

Sharing Workbooks

Protecting Worksheets & Workbooks - View Tab - Protected Workbook (practically all commands are unavailable) - Protect Workbook - Protect Worksheet - right click any cell - Format Cells - Protection Tab - Highlight - format cells - unlock all cells - now lock the ones you want to - You can lock certain columns or rows only - Protect Sheet (list of options appears) - password option, etc. Sharing Workbooks - Activate sharing and track change - Add icon to quick access toolbar (top left of screen) - Drop Arrow - Choose commands (letter s) - share workbook - add to toolbar - NEW FEATURE - Learn more about co-authoring, collaborate Tracking Changes - Legacy feature add to quick access toolbar

New Data Types coming to Excel for 365 --Data Management Features (10) - REFERENCE!!

- Data Type (Excel 2019): use to be only stocks and geography, there are now preview data types available - Go to Data tab to see these options - i.e. Fruit types in a column, want to pull in nutritional information - 1 convert data into a table on CTRL T - Data Tab, under Data Types, food, pulling in information through the internet - Quick Analysis button for the particular item - information is being stored, we can refresh data, even pull in pictures and maps More Info - insider.office.com Power Query or Power BI - have - Data - From Table Range - Power Query Editor - Create Data Type - - Use Queries & Connections to pull in data from elsewhere Power Query (Get & Transform) Power BI Essential Training

Data Management Features

Sorting - Click within cell, click Cntrl A, hit Cntrl period a few times Filters - located on Home tab and Data tab Pivot Tables - creates quick summary based on a list of data - no empty rows or columns - create table without formulas - Insert Tab (not Data tab) - can add to the same or new worksheet - Pivot Tables fields list - When the active field is in the Pivot Table, there is a new tab for PivotTable - If we make changes to source data the Pivot Table, it does not update automatically. You must go to Analyze tab refresh the data or use Alt+F5 to refresh.

You have numerical data across 12 rows and 4 columns. When you highlight this range of cells, and then press Average and AutoSum in the Formula ribbon, which outcome occurs?

The averages will display in the column at the end of each row and below the bottom row of each column.