Excel Intermediate

Excel Intermediate Topics Covered

Tables

A table is a section of rows and columns within a worksheet containing a header row describing the data in each column.

Create a Table:

With the data selected including the top row (header), from the Insert tab, within the Tables group, and click on the the Table command icon.
From the Create Table dialog box, verify that My Table has headers is checked and click on OK.

Screenshot of Excel menu options. The top options are File, Home, Insert. The Insert tab is open and the following options are available. Pivot Table (with an white square icon that has a horizontal gray bar and a vertical gray bar as well as a curved arrow that has arrows on both sides), Recommended PivotTables (with an icon that has a gray vertical bar and a gray horizontal bar on top of which there are lines and a blue question mark), and Table (with a white table that is three cells across and seven cells down with a blue horizontal header bar that has a white carrot-arrow pointing downwards).

Charts

Visually charts and graphs can highlight important trends using the data in the worksheet.

Create a Chart:

With the data selected from the Insert tab, within the Charts group, and click on the the Chart Type button and select the chart from the subtype gallery.

Recommended Charts: This feature displays a list of different chart types based on the selected data, as well as live preview of the chart with your data in it.

Screenshot of Excel menu options. The top options are Insert, Page Layout, Formulas, Data, Review, View, Help, Foxit PDF, and an icon that looks like a lightbulb. The Insert menu is open. There are four sections. The first section has an icon of a table with a blue header and three white arrows pointing down. There is then a vertical line and the second section has an icon of a gray box with a white square, a circle, and a diamond with text that reads Illustrations. There is a black triangle-arrow shape below Illustrations. There is then a black vertical line and the third section has a 2x2 box with a plus icon in the top right and text that says Get Add-Ins. Below that, there is an icon of a circle in front of a cube, both in gray. The text to the right reads My Add-ins. There is a black carrot-arrow next to it. To the right of Get Add-ins, there is an icon of a black circle in front of a gray triangle with a square that has a "V" in it on top. Below that icon, is a white triangular icon. Below that icon, is a gray table with the silhouette of a person in white on top of it. This section is separated by a black vertical line. The next section has a red square around it. The items in this section are: Recommended Charts (there is a bar chart with three vertical bars, a blue, a yellow, and another blue. On top of which is a blue question mark); Series of blue and yellow charts with text that says Charts (the charts include a vertical bar graph with blue, yellow, and another blue bar, a chart with two vertical bars where the first is blue and the second starts out at blue but changes to yellow, a chart with four vertical lines (two blue and two yellow), a line chart with two blue lines, another bar chart with four bars that are all blue, a bar chart with three bars and a line plot, a pie chart, and a plotted chart. Next to this is the icon of a globe on a stand in green and white. Below it, the text reads Maps. To the right of that, there is an icon of three vertical bars with a white square with vertical and horiztonal gray bars as well as a curved arrow with arrows on both ends.

FlashFill

Similar to the Fill handle, FlashFill can enter data automatically by detecting patterns and offer suggestions, saving you time and effort. Enter the data to establish a pattern, when excel recognizes the patter, suggestion are offered, if correct press Enter:

From the Data tab, within the Data Tools group, select FlashFill button.

Additionally, click on the FlashFill button next to recently added data to modify or undo.

**FlashFill Keyboard shortcut**

Place cursor in cell
Press and hold Ctrl + E

Screenshot of an Excel workbook with three columns. The headers of the column are Last Name and First Name. The third column doesn't have a header. The columns then have last names listed in the first column, but they are cut off and unreadable. The second column has the following first names: Heidi, Josie, wendy, and loretta. The last name is selected and there is a suggestion window with the following grayed out options: walter, misty, matilda, elizabeth, alvin, brian. The third column has the following in each of the cells going down: x,x,x.

Quick Analysis

The Quick Analysis galleries are dynamic what is displayed within them changes based on the type of data selected. Quickly select from commonly used formatting features. The live preview allows you to see what is going to happen prior to committing to a change.

Select data and click on button that appears bottom right of selected data.
The default, the Formatting Gallery is displayed listing a handful of choices, in addition there are other categories of tools - Charts, Totals, Tables, or Sparklines tabs.
Hover over each tab to preview and click on the tab to apply that feature to your worksheet.

**Autosum keyboard shortcut (Alt + =)**

Place cursor below the column of numbers you want to sum or to the left of the row of numbers you want to sum.
Hold down the Alt key and press =
Press Enter

Screenshot of an Excel table. There are seven columns and sixteen rows. The first three columns and six rows have grayed out cells with each of the following numbers in them: 277,891;404,937;743,027;281,477;300,189;150,787;195,649;559,457;842,889;294,048;528,557;784,345;121,654;300,189;787,341;170,904;591,303;300,087. All of the cells with numbers in them are selected. There is a green box around them and at the bottom right corner, there is a small green button with a table icon. There is a dialog box open from the small green button. The options within the window are FORMATTING (selected), CHARTS, TOTALS, TABLES SPARKLINES. Between each one is a vertical line. Below the headers, there are a series of icons with text. The first is a table with blue horizontal bars with text that says Data Bars. The next icon is a table with blue, green, yellow, red, and orange horiztonal bars with text that says Color Scale. The next icon is a table with blue, red, and green text that isn't legible. The text with this icon says Icon Set. The next icon is a table with a yellow cell and a square with a right pointing carrot. The text with it says Greater Than. The next icon is a table with a yellow cell and a box that has "ab" on it. The text with it says Text Contains. The next icon is a table with a yellow cell and a red pencil. The text with it says Clear format. Below all of that is text that reads: Conditional Formatting uses rules to highlight interesting data.

Conditional Formatting

Conditional formatting lets the user apply formatting to a cell or a range of cells based on specific criteria.

With your data selected, from the Home tab, within the Styles group, click on the Conditional Formatting command selecting Highlight Cell Rules and select a rule (i,e., Greater Than) from the drop down menu.

Screenshot of Excel options. The header buttons across the top are Conditional Formatting with a down-pointing carrot-arrow icon(with a table icon that has red and blue cells) that is highlighted in yellow; Format as Table with a down-pointing carrot-arrow icon (with a table that has blue cells and a blue paintbrush); Cell Styles with a down-pointing carrot-arrow icon (with a table with blue cells in the middle and a paintbrush icon); Insert with a down-pointing carrot-arrow icon (with a row of white cells, followed by a row with blue cells and an arrow pointing to the left, then two more white rows); Delete with a down-pointing carrot-arrow icon (with an icon that has a row of white cells followed by a red "X" and the two more rows of white cells), Format with a down-pointing carrot-arrow icon (with a table that has a blue cell in the center). The Conditional Formatting option is open with the following menu options. Highlight Cells Rules with a carrot-arrow pointing to the right (and a white table with a yellow row), all of which is highlighted in yellow; Top/Bottom Rules with a carrot-arrow pointing to the right (and a table with a yellow square on it): Data Bars with a carrot-arrow pointing to the right (and a table icon with blue rows); Color Scales with a carrot-arrow pointing to the right (and a table with red, green, yellow, and orange rows); Icon Sets with a carrot-arrow pointing to the right (and a table with red, green, and blue icons); New Rules (with a book icon with a yellow square in the top right); Clear Rules (with a white document and a white eraser); and Manage Rules (with two overlapping white documents). In this menu, the Highlight Cell Rules option is selected and the following menu items are open. Greater Than (with a white table with a single yellow row and a greater than icon); Less Than (with a white table and a yellow row with a less than icon); Between (with a table that has a blue cell followed by a yellow row and another blue cell icon); Equal to (with a white table with a yellow row and an equal sign); Text that contains (with a white table with a single yellow row and a box with "ab" on it); A date occuring (with a white table with a yellow row and a calendar icon); Duplicate Values (with a white table with two yellow rows).

Sparklines

Sparklines are small charts embedded into cells that are aligned with rows of data used to show trends or relationships visually.

With a range of data selected, from the Insert tab, within the Sparklines group, and select a Sparkline type.
Within the Create Sparklines dialog box, specify a Location Range for the sparkline to be placed on the worksheet and click on OK.

Screenshot of Excel Sparkline Options. There are three options across the top. The first is Line (with a blue line on a white chart); Column (with four vertical blue bars in a chart); Win/Loss (with three blue bars and red bars on a chart). Below all three is text that says Sparklines.

Screenshot of the Sparklines dialog box. At the top left is text that says Sparklines and in the top right is a white question mark icon and a "X" icon. There is a translucent background that oulines a gray box. Within the gray box is text that says Choose the data that you want. Below that the text reads Data Range and has a field with "C3:L10" in it. There is a table icon to the right of the field. Below that is text that says Choose wehre you want the sparklines to be placed. Below that is text that reads: Location Range:. There is a field to the right of that that is empty. There is also a table icon. Below all of that are two buttons, one is OK and the other is Cancel.

3. With the sparkline selected, from the Sparkline Tools Design tab, used options within the Show and Style group to format the sparkline.

Screenshot of the Excel menu options. There is a blue bar at the top of the page. To the far left, there is a green "X" in a white box, a floppy disk icon, a curved arrow with a drop-down carrot-arrow icon, a grayed out curved arrow pointing to the right with a carrot-arrow, and a triangle pointing down with a horizontal line above it. In the middle of the blue bar, there is text that reads exercisefile - Microsoft Excel. Below the blue bar, there are header options: File (in a green background), Home, Insert, Page Layout, Formulas, Data, Review, View, Add-Ins, Acrobat, Design, Sparkline Tools. The Design tab is open with six sections. The first section has a chart icon with a pencil on top of it and the text says Edit Data with a carrot-arrow pointing down. The word, Sparkline, is below. There is a vertical line that separates the first and second section. The second section has a blue line chart with the text, line below it and is highlighted in yellow. To the right, there is another icon with a blue bar chart with the text, column. To the right of that is a chart icon with three blue bars and two red bars. The text underneath says Win/Loss. Below all of the icons in the second section, there is text that says Type. There is a line separating the second and third section. The third section has six checkmarks: High Point (selected), Low Point (selected), Negative Points (selected), First Point, Last Point, and Markers (selected). Below the checkmarks is text that says Show. Between the third and the fourth section, there is a line. The fourth section has six line charts with plotted data. There is also a down arrow to indicate that there are more options below. They are blue, pink, red, green, purple, and orange. To the right, there is a fifth section. In the fifth section, there is a square with a pencil and a thin green line icon with text that says Sparkline Color with a carrot-arrow pointing down; and a square with four equal squares inside that are black, white, blue and red with the text, Marker Color with a carrot-arrow pointing down. There is a line separating the fifth and sixth sections. The last section has a chart icon with a blue line and orange lines along both axis of the chart. The text below reads Axis with a carrot-arrow pointing down. To the right, there are two overlapping squares with the text, Group. All of which is grayed out. Below that, there are two overlapping squares with the text Ungroup. Below that is a white eraser icon with the text, Clear with a carrot-arrow pointing down. The entire section has text below it that says Group.

Combine Cells

In Excel, you can combine cells with different data into one cell by using the "&" sign in your function.

Combine first and last names into full names in a single column
Put cursor in blank cell where you want the full name to appear
Highlight one cell that contains a first name, type in an "&" sign, and then highlight a cell with the corresponding last name.
If all you type is: =A2&B2, then there will not be a space between first and last names.
To add space: add quotation marks around the space to tell Excel to add a space between names. Example: A2&” “&B2
To apply to multiple rows: simply drag the corner of the first cell downward.

Remove Duplicates

Highlight row or column you want to remove duplicates of.
From the Data tab, within the Data Tools group and select Remove Duplicates.

Transpose

Highlight the column that you want to transpose into rows.
Right-click and select Copy.
Select the cells on your spreadsheet where you want your first row or column to start
Right-click on the cell, and select “Paste Special”
From the Paste Special dialog box, select Transpose and click OK.

Text to Columns

Select the range
From the Data tab, click on Text to Columns
From the Convert Text to Columns Dialog box, select Delimited and click on Next.
Under Delimiters, ensure Comma and Space are selected and click on Finish.

Note: Your data could have other delimiters, you get a live preview of how your data will be separated

Create a Drop Down List

Select a cell or range of cells
From the Data Tab, click on Data Validation
Within the Allow box, select List
In Source Box , enter items you want to appear in drop down.
Ensure the In-cell dropdown box is checked.
Optionally, select ignore blanks if you want to allow your users to leave a cell empty.
Click OK.

Screenshot of the Data Validation window in Excel. There is a white bar across the top. In the left, there is text that says Data Validation. In the right corner, there is a question mark icon in a gray box. To the left of that, there is a white "x" in a red box. The actual dialog box has a gray background. Within that, there are three tabs across the top, they are Settings, Input Message, and Error Alert. The Settings tab is open. At the top of the open tab, there is text that says Validation criteria and a black line going across. Below that, there is text that says Allow and a dropdown field with the text, List, in it. To the right there are two checkboxes that say Ignore blank and In-cell dropdown. There is a orange square around all of this. Below that is text that reads Data and a dropdown field with text that says between. Both the text and the field is grayed out. Below that is text that reads, Source. Below that is text that says Yes, No, Not Sure (all of which is inside an orange square) and a field that is empty. To the right is a table icon. Below that is a green rectangle with the words, Step 3. Type the entries and select the options. The text points to the orange square around Validation Criteria and the orange square around the Source field. Below that, the bottom left has a gray Clear All button. In the bottom right, there is a gray button that says Okay with a blue outline. To the right, there is a Cancel button.

When using educational technology tools, be FERPA aware. To learn more, visit the Registrar's FERPA Guidelines site.