MICROSOFT EXCEL

Chapter 1 Microsoft excel fundamentals

1) Templates- Basic structure of a document that we can reuse for later purpose.
2) Prior documents can be found out in recent list in home category
3) office templates can be found in new category
4) AT top of excel file, RIBBON contains various tools that are to be used. there are many tabs in ribbon of which
home tab has tools related to formatting the font, paragraph, alignment no. formatting styles
5) quick access tool bar has common quick access commands like save undo redo
6) Double click on tabs to hide ribbon and vice versa to get it back.
single click to get it temporarily but as soon click away from it it closes in this case.
7) small button in quick access toolbar helps to customise the tool bar enabling it show different legends in the
toolbar
8) workbook contains the bulk of screen and there are worksheets down below status bar at bottom of screen
9) ctrl + down arrow shows no. of rows and ctrl+right arrow gives no. of columns ctrl + home reverts these.

Chapter 2 Entering and Editing Text and Formulas

10) Look at the months and the dollar amounts. The months are left-aligned, while the numeric values are right-
aligned. This alignment difference is due to formatting defaults: text values are left-aligned, numeric values are
right-aligned.
This alignment difference is not unique to Excel; many applications behave this way. It is cleaner and helps
distinguish text from numbers. When decimals are introduced in numeric values, they align nicely on the right
side, creating a clean column.
If I change these cells to left-align, the decimals no longer line up and the column looks messy, creating a
'wave' effect. This is undesirable for readability and presentation.
Undoing the left alignment restores the clean decimal alignment. This distinction between text and numeric
alignment is important for clarity and accuracy in spreadsheets
11) If you import or copy data into Excel and see numeric values left-aligned, Excel may treat them as text. This
often results in a small green triangle in the corner of the cell, indicating a potential issue.
When you see this green triangle, clicking on it reveals options, including 'Convert to Number'. Using this option
converts the text-formatted numbers back to numeric values, right-aligning them and fixing potential calculation
issues.
12) When entering dates like "January-2016", Excel may automatically convert the input into a date value. This is
evident when the text aligns to the right side of the cell, which is typical for numeric values. Excel treats dates as
numeric values internally, even if they appear as text like "Jan" or "Feb".
13) If you want to display dates more clearly, such as showing the full year instead of an abbreviated two-digit
year, you can customize the date format:

1. Select the cells containing the dates.

2. Go to the Home tab on the ribbon.
3. In the Number section, click the dropdown menu.
4. Choose "More Number Formats" at the bottom.
5. In the Format Cells dialog, select the "Date" category to see preset formats.
6. If none of the presets meet your needs, select "Custom" to create your own format.

14) Creating a Custom Date Format

To display dates as "Jan-2019", use the following custom format code:

 mmm for the three-letter month abbreviation

  yyyy for the four-digit year

Combine them with a hyphen as mmm-yyyy. This will display dates with the abbreviated month followed by the
full year. After entering the custom format mmm-yyyy and clicking OK, the dates in the selected cells will display
as "January-2019", "February-2019", and so forth, providing a clear and consistent date format for your
spreadsheet.

15) A couple of shortcuts: you can reference noncontiguous cells, which are cells that are not touching. This
becomes important when creating formulas, charts, or pivot tables where you want to reference a range of cells
that do not touch. For example, you can select the headers and rent cells, then hold down the 'Control' key on your
keyboard and select the food cells, which are three rows down from bills and rent. This way, you have a
noncontiguous group of cells selected. This is useful if you want to create charts and only include specific data,
such as the headers, months, rent, and food, without selecting all the data.

16) A relative reference means that from the formula's location, it refers to cells one, two, three, four, five
positions away. For example, the formula in the total cell sums B4, B5, B6, B7, and B8. If you copy this formula
to the next column, it will adjust to sum the corresponding cells in that column

17) Relative Reference Behavior Example

If you enter the value 10 in five cells above a new location and paste the formula there, it will sum those five cells,
resulting in 50. This illustrates that relative references always adjust based on the formula's position, moving a
specific number of cells in a given direction. Similarly, if you create a formula in column E that sums cells B4, C4,
and D4 (three cells to the left), copying this formula down will adjust the row references accordingly, summing the
corresponding cells in each row. This consistency in relative positioning saves significant time when working with
structured data.

18) Now, let's discuss absolute references. By default, Excel uses relative references, but sometimes you need to
fix a reference to a specific cell regardless of where the formula is copied. This is where absolute references come
into play To fix this, you make the grand total cell reference absolute by adding dollar signs before the column and
row references, like this: $E$9. This locks the reference to that specific cell, so when you copy the formula down,
the grand total reference remains constant.

19) In formula tab check how the formula operates in evaluate formula in formula auditing.

Chapter 3 Working with Basic Excel Functions

20) FUNCTION: An Excel function is essentially a predefined calculation. Microsoft has built hundreds of these
functions for us to use. These predefined packages perform various calculations, such as summing a range of cells,
finding averages, or looking up information based on criteria.

21) Every Excel function consists of three parts:

1. Equal Sign (=): Every calculation starts with an equal sign.

2. Function Name: For example, SUM, AVERAGE, MIN, MAX.
3. Function Arguments: The parameters or inputs the function needs, enclosed in parentheses.

For example, to sum cells B4 through B8, you write:

=SUM(B4:B8)
Here, the colon : denotes the range from B4 through B8.

22) The Function Arguments Window

Excel includes a helpful feature called the Function Arguments window. This window assists users in entering the
correct arguments for functions, especially when starting out. You can access it by clicking the "FX" button next
to the formula bar at the top of the Excel window.

23) Excel contains a vast library of functions. As of the 2013 edition, there were 461 functions built into the
application, with newer versions adding or replacing functions to improve efficiency and accessibility. You can
find these functions in the Formulas tab on the Excel ribbon. The Function Library groups functions into
categories, making it easier to find the one you need.

24) Within More Functions, there is a Statistical category where the MIN function resides. Alternatively, you can
use the Insert Function button (the small FX icon) to search for the MIN function by typing "min" or "minimum"
in the search box. This method is helpful when you are learning functions and unsure of their [Link] you
select the MIN function, the argument window will open. Excel may pre-fill a range, such as B4 through B10, but
you should adjust this to exclude empty cells or totals you do not want to include. For example, select B4 through
B8 to get the minimum value of the monthly data excluding totals. MAX in the same way

25) At first, I do not see AVERAGE listed under Math and Trig. So, I will go to More Functions, then to
Statistical, and there it is: the AVERAGE function

26) The COUNT function counts numeric values only. It does not count empty cells or text values; it exclusively
counts numeric [Link] COUNT function counts the number of cells in a range,

27) Here, we are using the Sum function to sum cells B4 to B8. That is great; that is what we want. But Excel sees
the date at the top and says, wait, that is a number too, so we should include it inside this formula. Alternatively, I
can select all these cells, go to the little diamond, and say, "Ignore the error." The green triangles disappear.

28) After pressing Alt equals, I hit the Enter key, and the total is calculated. Then I jump over to the next cell and
press Alt equals again, which does the AutoSum for me. This is a very slick and fast shortcut.

29) The AutoFill handle is a small box in the corner of the selected cell. When you hover over it, the cursor
changes to a black plus sign. Clicking and dragging this handle across adjacent cells copies the formula, adjusting
cell references automatically based on the new positions.

Chapter 4 Modifying an Excel Worksheet

30) To add a row first select a row or column near which we will add the new row or column then we do ctrl with
+ simultaneously. + is in number pad or ctrl+ shift + (+) to add any column or row.
31) You can also delete rows and columns using shortcuts. For example, to delete the water bill row, highlight row
seven and press Control minus. To delete a column, highlight it and press Control minus. If you do not have a
number pad, use Control and the minus key at the top of the keyboard. The Home tab also provides Delete options
for rows and columns.
32) If you want to automatically adjust a column's width to fit the largest value in that column, move the cursor
between the two column headers until the double-headed arrow appears, then double-click. Excel will auto-fit the
column width to the largest cell content. For example, if you shrink a column and then double-click between the
column headers, Excel will find the largest value and adjust the width accordingly. This works for any column and
helps keep your worksheet tidy.
33) If you want to adjust multiple columns simultaneously, select the columns you want to resize. Then, move the
cursor between any two of the selected column headers until the two-headed arrow appears. Left-click and drag to
adjust the width. Although it looks like you are adjusting only one column, releasing the mouse button will resize
all selected columns uniformly. You can also double-click between any two selected column headers to auto-fit all
selected columns to their largest values. This ensures uniform and consistent column widths. The same approach
applies to rows. Select multiple rows, then drag the boundary of one row to adjust the height of all selected rows
simultaneously.
34) To hide a column, select the column header, for example, column C. Right-click on the header and select the
"Hide" option. This will hide the entire column C from view.
Understanding Hidden Columns
When a column is hidden, it is still present in the worksheet but not visible. For example, if column C is hidden,
you will notice that the column headers jump from B to D, and column C is missing. Essentially, the column width
is reduced to zero, making it invisible.
Experienced Excel users can detect hidden columns by observing the gap between column headers. They can
restore the hidden column by dragging the boundary between adjacent columns to increase the width.
Unhiding Columns
To unhide a hidden column, move your cursor between the headers of the columns adjacent to the hidden one.
When the cursor changes to a double-headed arrow with a gap, left-click and drag to the right to reveal the hidden
column. This action restores the column width and makes the data visible again.
35) On the View tab at the top of the Excel window, there are several options with checkboxes. One of these is
"Headings," which controls the visibility of column letters (A, B, C, etc.) and row numbers (1, 2, 3, etc.).
If you uncheck "Headings," these labels disappear, which can further obscure the structure of the worksheet from
users. However, advanced users can easily re-enable this option. Therefore, worksheet protection remains the most
effective method to restrict access.
Hiding and Unhiding Multiple Columns or Rows
You can hide multiple columns or rows simultaneously. For example, if you want to hide all three months of data,
select the range of columns (e.g., B through F), right-click, and choose "Hide." This will hide all selected columns.
To unhide multiple columns or rows, select the columns or rows surrounding the hidden ones, right-click, and
choose "Unhide." This action restores all hidden columns or rows within the selection.
36) I prefer shortcuts, so double-clicking the tabs is a quick and easy way to rename sheets. Alternatively, you can
right-click the worksheet tab and select the Rename option. Then, start typing the new name and press Enter to
complete the renaming.
37) Warning About Deleting Worksheets

Be very careful when deleting sheets from an Excel workbook. Once you delete a worksheet, such as the monthly
budget sheet, there is no undo option available. Typically, if something goes wrong in Excel, you can press Control
+ Z or click the undo button on the quick access toolbar. However, this does not apply when deleting worksheets.
If you delete a sheet and then realize it was a mistake, you cannot use undo to recover it. The only way to restore
the deleted sheet is to close Excel without saving the changes and then reopen the file. This will bring back the
deleted sheet as long as you have not saved after deleting it.
38) Moving or Copying Sheets to Another Workbook
What if you want to move or copy a sheet to a different Excel file? For example, you have a workbook named
[Link], and you want to move or copy the monthly budget sheet to another workbook. To do this,
right-click the sheet tab and select the "Move or Copy" option. A dialog box appears with several options:
 Choose the destination workbook: you can keep it in the current workbook, move it to a new workbook, or
select any other open workbook on your system.
 Specify the position where the sheet should be moved or copied within the destination workbook.
 Check the "Create a copy" box if you want to copy rather than move the sheet.
This method allows you to move or copy sheets both within the same workbook and across different workbooks.
39) Moving Sheets
First, let's look at moving sheets. Suppose you have only two sheets, but you add a few more by clicking the plus
sign several times. Now you have sheets numbered 5, 6, 7, and 4. Let's reorder these sheets so that sheet number 4
comes before sheet number 5. Each time you hit the plus sign, it adds a new sheet to the left of the current sheet.
To move a sheet, click on the sheet tab to activate it, then left-click and drag it. You will see a small diamond or
upside-down triangle following your cursor, indicating the position where the sheet will be placed. Drag sheet 4 to
the left of sheet 5 and release the mouse button. The sheets are now reordered. This method is simple: left-click the
sheet tab and drag it to the desired position.

40) Copying Sheets

Moving a sheet changes its position, but what if you want to copy a sheet? For example, if the monthly budget
sheet is for 2019, and you want to create a copy for 2020, dragging alone will move the sheet. To copy, hold down
the Control key on your keyboard while dragging the sheet tab. You will notice the cursor changes to include a
small piece of paper with a plus sign, indicating a copy operation. When you release the mouse button, a copy of
the sheet is created with the name "monthly budget 2" because sheet names must be unique. This method is quick:
hold Control, drag, and drop to create a copy.

Chapter 5 Formatting Data in an Excel Worksheet

41) To apply a border, first select the cells. For example, select cells from B2 to G2, where you want to place your
border. Alternatively, you might want to put a border down in another group of cells, such as just a bottom border
to separate the top data from the rest of the data below. With the cells highlighted, navigate to the Home tab, then
into the border section. Here, you will find all the pre-configured border options available for selection. What if
you want something different? a different style such as a dotted or dashed line instead of a solid line. Maybe you
want to change the color from the default black to green, orange, or another color. At the bottom of the border
options, there are additional choices including a palette of colors and various styles like dashed and dotted lines.
For more customization, select the "More Borders" option. This opens the Format Cells dialog on the Border tab,
allowing complete customization of your borders.
42) This window may seem confusing at first because it involves a couple of steps. On the right side, there is a
preview window showing what your border will look like. For example, if you want a thicker border, select a
thicker line style from the options below. However, the preview does not update immediately after selection. After
selecting the desired style, you must click on the preview diagram where you want to apply that style—such as the
left, right, top, or bottom border. Similarly, if you select a color, such as orange, you must then click on the
preview to apply that color to the border. Once you have made your changes, including style, thickness, placement,
and color, click OK. The selected cells will then display the customized border. For example, an orange border can
now be seen around the selected cells.
43) navigate to the Home tab, and under the Number section, you will find the Currency Style option. Clicking the
dropdown reveals several currency [Link] selected, the data now uses the accounting style. The dollar
symbol appears inside the cells, thousands are separated by commas, and decimals are shown with dots. This
makes it clear these are currency [Link] preference is to format the first and last rows with the currency
style, including the currency this, I highlight cells C6 to F9, excluding the first and last rows. Then, back in the
Home tab under the Number section, I select the Comma Style option. This removes the currency symbol but
retains the decimal and thousand separators.
44) f you decide to apply the same formatting to another area on the same worksheet or even on a different
worksheet within the workbook, the Format Painter is your tool of choice. With cell B12 selected, navigate to the
Home tab and locate the Format Painter in the Clipboard section. A single click on the Format Painter activates it
for one use. Once activated, your cursor will display a paintbrush icon, indicating that the formatting is ready to be
applied. Click on the target cell, such as "count," to apply the copied formatting. This action copies all the
formatting attributes from cell B12 to the target cell, saving you from manually replicating each formatting step.
45) Keeping the Format Painter Active for Multiple Uses
If you double-click the Format Painter instead of single-clicking, it remains active, allowing you to apply the
copied formatting to multiple cells consecutively. You can click on cells such as "max," "average," or any other
cells across the same or different worksheets to apply the formatting repeatedly. To deactivate the Format Painter,
either click the Format Painter button again or press the Escape key on your keyboard
46) Introduction to Cell Styles
Cell Styles are saved formatting configurations that you can reuse throughout your document. Using Cell Styles
saves you significant time because you apply the formatting once, save it as a style, and then reuse it wherever
needed. Moreover, if you update the saved formatting, it updates all instances in your document automatically. For
example, I want all headers in my worksheet to have consistent formatting in terms of colors, font size, and style.
Although the data types vary between text and dates, I want the overall look and feel to remain consistent. I format
the headers, save that formatting as a style, and later if I decide to change the background color, I simply update
the style, which then updates all cells using that style.
47) Accessing and Using Cell Styles in ExcelTo create and manage Cell Styles, go to the Home tab in Excel. There
are multiple ways to create styles, but here I navigate to the Styles section and select Cell Styles. Excel comes with
a set of default styles pre-installed, and newer versions include even more options. Hovering over each style
provides a live preview of how the selected cell will look with that style applied. To create a new style, click on
"New Cell Style," which opens the Style window where you can name your style and specify which formatting
elements it includes.
48) Defining Style Components
In the Style window, you can choose to include fonts, font color, background color, borders, alignment, and
protection settings. However, it is important to exclude number formatting if your data includes a mix of text and
dates or numeric values. This prevents unwanted changes such as dates turning into text or numbers losing their
formatting. After selecting the desired components, click on Format to open the Format Cells window. Here, you
can customize the fill color, font style and size, text color, and borders. For example, I choose a green fill, bold
white font at size 14, and a thick black bottom border. Avoid changing the Number tab to preserve data formatting.
Once you finish customizing, click OK to save the style. The new style is created but not yet applied to any cells.
To apply it, select the cells you want to format, go to the Home tab, open Cell Styles, and click on your new style.
The formatting is applied instantly.
49) Modifying Existing Styles
If you later decide to change the style, such as adjusting the font size or background color, you can modify the
style itself. Right-click on the style in the Cell Styles menu and select Modify. Then, click Format to adjust the
settings. For example, reduce the font size from 14 to 12 and change the fill color to a grayish-blue. Click OK
twice to save the changes. All cells using that style will update automatically with the new formatting. This feature
is especially useful when you have applied the style across multiple worksheets and want to ensure consistent
updates quickly.
50) First, highlight the cells you want to merge together. With them selected, go to the Home tab, and under the
Alignment section, locate the "Merge and Center" button. Click it, and it will merge those six individual cells into
one and center the text inside. Now, instead of managing six individual cells separately, you have one merged cell
to select and edit.
51) Introduction to Conditional Formatting
Conditional formatting applies formatting based on a condition. For example, consider a monthly budget
spreadsheet with dollar amounts and percentages. You might want to highlight certain cells if they meet specific
[Link] you want to highlight monthly bill amounts in cells C5 through E9 if their values exceed a certain
threshold, such as 250. This helps quickly identify amounts above that value. You may have encountered situations
where you need to highlight specific records based on criteria, such as dates exceeding today's date or falling
within a particular month like August. Conditional formatting enables such dynamic highlighting based on these
conditions.
52) Applying Conditional Formatting in Excel
First, select the cells you want to format. In this example, select cells C5 through E9. Then, navigate to the Home
tab, and under the Styles section, click on Conditional Formatting. Excel offers various conditional formatting
options, including data bars, color scales, and icon sets. For this example, we will use the "Highlight Cell Rules"
and select the "Greater Than" option. In the "Greater Than" dialog, enter the value 250 to highlight cells with
values greater than 250. You can choose from default formatting options such as light red fill with dark red text or
customize the format. For instance, you might want only the cell fill to turn red. After applying the formatting, the
selected cells with values above 250 will be highlighted accordingly. This process can be repeated for other
conditions and formatting styles as needed.
53) in summary, to edit conditional formatting rules:
 Go to the Home tab
 Select Conditional Formatting
 Choose Manage Rules
 Adjust the dropdown to view the desired scope (current selection or entire worksheet)
 Select the rule and click Edit Rule
 Modify the rule criteria or formatting
 Confirm changes by clicking OK
You can also create new rules or delete existing ones from this window.
Try opening the Rules Manager window even if you do not make changes
54) To summarize, inserting an image is straightforward:
1. Go to the Insert tab.
2. Click Picture under the Illustrations section.
3. Choose the source: This Device, Stock Images, or Online Pictures.
4. Select your image and click Insert.

Chapter 6 Inserting Images and Shapes into an Excel Worksheet

Inserting a shape is very simple. First, go to the Insert tab in the Excel ribbon. Then, navigate to the Illustration
section where you will find the Shapes option. Clicking on Shapes reveals a variety of shapes to choose from,
including basic shapes, block arrows, equation shapes, and more.
55) The little white markers allow you to resize the shape. Whether you're changing just the height or the width, or
constraining it and changing proportionally both the height and the width depends on where you grab it [Link]
little orange markers, such as the one on the tail end of an arrow, allow you to change the thickness of the arrow
shaft. You can make it really skinny or really wide. It can look like a little tab, one of those sticky note tabs you put
inside of a binder. The arrow itself can be adjusted in size with another orange marker. For example, you can make
it a little bit thicker
55a) To insert SmartArt, go to the Insert tab, then to the Illustrations section, where you find options like Pictures
and Shapes. Towards the right, there is an option for SmartArt. Click it to open the Choose a SmartArt Graphic
interface. There are various categories of SmartArt graphics. Currently, the view is set to All, allowing you to
scroll through all the different types of diagrams you can create. For example, there are list diagrams for creating
dynamic and bold lists, with or without headers.
55b) For this demonstration, I will select a Process SmartArt. Once you know how to work with one, the others are
similar, differing mainly in look and feel. I will choose the Continuous Block Process, which is used to show
progression or sequential steps in a task, process, or workflow. After clicking OK, the SmartArt graphic appears on
the worksheet. To make it easier to see, I will adjust its position by dragging its edge downward so it is not covered
by my head. With the SmartArt graphic selected, two new tabs appear: SmartArt Design and Format. The Design
tab offers operations such as changing layouts, styles, adding or deleting shapes, and promoting or demoting areas
within the graphic. The Format tab allows detailed formatting, including changing colors and text. To facilitate
editing text, open the Text Pane from the SmartArt Design tab on the left side. This pane allows easier text editing
compared to editing directly on the graphic.
55c) One more feature: adding sub-elements within SmartArt. For example, under "Make Money," I want sub-
bullets such as "Make money by working" or "Make money by asking my mom." To do this, click to the right of
the bullet and press Enter to create a new shape. Then press Tab to indent it as a sub-element.

Chapter 7 Creating Basic Charts in Excel

56) With the data selected (B4 to E9), navigate to the Insert tab. Within the Insert tab, there is a section dedicated
to charts. Depending on your version of Microsoft Excel, you will find many options here. Newer versions have
introduced more chart types over time. I will select the first option, which is the column charts. Clicking the button
reveals various column chart styles. I will choose a standard 2D column chart, which is one of the most common
chart types in Excel. After clicking it, the chart appears immediately. The process is straightforward: select your
data, go to the Insert tab, choose Charts, and pick your desired chart type. This quick method allows you to create
charts efficiently.
57) When I select my chart, three small icons appear to the right, offering many features. The first icon allows
adding or removing chart elements. The second is for formatting, and the last one, which looks like a small funnel,
is the filter command. You can filter charts inside Excel using this filter command. With the chart selected, you can
also access the Chart Design and Format tabs at the top of the screen. These tabs contain all the same features
except for the filter command, which is specific to newer Excel releases. On the Chart Design tab, you can add or
remove chart elements, change the quick layout by selecting from predefined layouts, choose from various chart
styles, and perform several other operations. The Format tab focuses on formatting aspects such as changing the
colors of the chart, bars, and text elements. Anything you want to format can be found on this tab.
58) There are a few ways to modify the data. One method is to select the chart, then go to the Chart Design tab. In
the Design tab, there is a section called Data. Here, you'll find options such as "Switch Row/Column" and "Select
Data." The "Select Data" option is essentially the chart's data source. Clicking on "Select Data" opens the Select
Data Source dialog. Currently, the chart data range is set to B4:E9, which matches the selection from the bills
down to the last value in March for candy. If you want to modify this range, you have a couple of options. For
instance, you can highlight the chart data range field and delete its contents. , the chart would become
blank .Suppose you want to exclude February and only include January and March. You can select B4:C9, which
covers bills in January. Then, hold down the Control key on your keyboard and select the range E4:E9 for March.
This way, the chart data source now includes two separate, non-adjacent ranges, effectively leaving out February.
59) However, sometimes a quirk or bug in Excel charts may cause unexpected behavior. For example, even after
excluding February from the data source, the chart might still display February labels on the axis. To address this,
select the X-axis labels at the bottom of the chart, right-click, and choose "Format Axis." This opens a panel on the
right side. In the Axis Options, you'll see the Axis Type setting, which is usually set to "Automatically select based
on data." Try toggling this setting to "Date" and then to "Text." Switching to "Text" often resolves the issue by
removing the unwanted February label.
60) Accessing Chart Formatting Options
Once your chart is selected, two new tabs appear at the top of the Excel window: Chart Design and Format.
These tabs provide various tools to customize your chart's appearance. On the Chart Design tab, you will find
several predefined styles. Clicking the dropdown arrow reveals a selection of styles. On Windows systems,
hovering over these styles provides a live preview, allowing you to see how each style affects your chart before
applying it. For example, a black background style makes colored bars stand out prominently. Some styles include
borders or data labels, such as dollar amounts on bills, offering a variety of visual options.
61) Adjusting Font Styles
To change font properties such as size, color, or weight, use the Home tab's font commands. For example, you can
increase the font size to 11 and make text bold for better visibility. This applies to axis labels, chart titles, and
legends alike. The chart title appears by default as "Chart Title." You can click into the title box to edit the text,
such as changing it to "Monthly Budget."

62) Linking Chart Title to Worksheet Cells

An advanced feature allows you to link the chart title dynamically to a cell in your worksheet. Select the chart title
box (without clicking inside the text), then click the formula bar at the top of Excel. Type an equal sign (=) and
then click the desired cell, such as a merged cell spanning B2 to G2. Press Enter to confirm.
Now, the chart title reflects the content of that cell and updates automatically when the cell's content changes.
63) Adding or Removing Chart Titles
If the chart title is missing or was deleted, you can add it back by selecting the chart, navigating to the Chart
Design tab, and clicking Add Chart Element. From there, choose Chart Title and select the desired
[Link] Format tab offers further customization such as adding borders around chart elements, changing
colors, and adjusting font sizes. For example, you can increase the font size of the legend via the Home tab and
reposition the legend to the top of the chart using Add Chart Element > Legend > Top.
64) With the chart selected, go to the Chart Design tab. Towards the right-hand side, there is an option for "Move
Chart." Click on it. There are two options: the default is "Object in," where you pick the worksheet. Currently, the
chart is on the same worksheet as the data. If you have other worksheets, a dropdown will appear allowing you to
pick which worksheet to place the chart on. If you only have one worksheet, only that will show. The other option
is "New Sheet." This will create a brand-new sheet. It is not a typical worksheet with columns and rows like our
Excel worksheets. Instead, this is a chart worksheet, and it will only contain the chart. You can rename this new
chart sheet. For example, name it "Chart Monthly Budgets" and click OK. Now, you have a second sheet named
"Chart Monthly Budgets" that contains just the chart. There are no columns or rows, no other data. The chart
remains connected to the original data.
65) The first chart we created was a standard 2D column chart. Now, let's look at another common chart type in
Excel: the pie chart. Pie charts are better suited for a single axis of data, meaning a single row or a single column,
such as a single bill or a single month. To create a pie chart, start by selecting the block of data, for example, from
B4 down to E9, the same data used for the column chart. Then, go to the Insert tab, find the Charts group, select
the pie chart option, and choose the default 2D pie chart. This quickly generates a pie chart based on your
selection. By default, the pie chart displays data for a single bill, such as the rent bill, across multiple months. It
does not include other bills like phone, credit card, or food. This illustrates that pie charts typically display a single
row of data with multiple labels representing columns, such as months.
66) To add data labels, select the chart, go to Chart Design, click "Add Chart Element," then "Data Labels," and
choose "Outside End." This adds dollar amounts to the chart slices. You can customize these labels by right-
clicking a data label and selecting "Format Data Labels." Here, you can choose to display values, percentages, or
both. For example, you can turn off the value display and enable percentage display to show only percentages on
the chart. You can also drag data labels to reposition them for better visibility, with connecting lines to their
corresponding slices.

Chapter 8 Printing an Excel Worksheet

67) Anytime you go File, Print, and then go back out to the worksheet, you then get these little dashed lines that
show up on your worksheet. That dashed line represents the page break, where it's going to print from one page to
the next. Take a look at this. If I scroll out here, zoom out here, there's a line right there. There's a line right there
and right there and right there. These are all page breaks, so this would essentially be a page right up to those lines,
and then this would be a page right up to those lines. If you have the chart selected, and you go File, Print, you
only print the chart. Let me get back out. I'm going to click into just a cell. The chart's no longer selected. I'll go
back to File, Print, and now I've got my two pages. That chart is no longer split onto page one.
68) I'm going to go down to No Scaling, currently meaning it's just at 100. I'm going to go down to Custom
Scaling Options. Inside this window, this is the Page Setup window. We've got Scaling, and you really got two
ways to scale a page. You can do it by a percentage, Adjust to, or you can do it by a fit, meaning I can say I want
you to fit all the content.
Maybe it spans 10 pages. Let's put it all on one page, and I can use that here. The scaling, you can adjust smaller or
larger. The Fit to, it will only shrink. It will not increase the amount of pages. It will only shrink.
69) The margins on the left and right, as well as the top and bottom, take up significant space. For example, the
arrow graphic is not quite aligned yet, but it roughly mimics the margin on the opposite side. There is some
additional space available, but the margins still consume a considerable portion of the page. To adjust margins,
navigate to the print settings, then select Page Setup. Within Page Setup, go to the Margins tab where you can
increase or decrease the margin sizes on the page. The goal is to make the spreadsheet, chart, and smart art as large
and bold as possible to take up the page effectively. For this purpose, setting the margins to a quarter of an inch
(0.25) is a good choice. It is important not to set margins too narrow because some desktop printers, such as the
one next to the computer, cannot print close to the edge of the page and may cut off content. Higher-end printers or
professional printing services can handle narrower margins better.
70) Earlier, we saw that by going to File > Print and then returning to the worksheet, we can see little gray dashed
lines that represent where page breaks occur and what individual pages will look like with your content.
The page layout view takes this even a step further.
If I go back to my normal worksheet view and then go up to the View tab at the top of the screen, towards the left-
hand side I have workbook views. Your workbook views might be on the far left or pushed over a bit depending on
your version of Excel. I am currently looking at Normal view. This is the default standard view inside of Excel. It
is just a large grid of columns and rows like we have been working in. Just to the right, there are a couple of
options, including Page Layout view. This is Page Layout view. I am going to zoom out a little bit, and now we
literally get to see the individual pages. The nice thing about this view, compared to the little gray dashed lines we
saw earlier, is that you get to see the margins—where the margins fall on the page as well.
We did not see the margins with just the little gray lines, but here we see literal individual pages.
71) If I click into that box, I get a new tab at the top of the screen called "Header & Footer." Clicking on that
reveals various elements that you can put into the header or footer, such as page number. When I click page
number, it inserts "&[Page]." Clicking out of the box now shows "Page 1," then "Page 2," "Page 3," and so on.
Other options in the Header & Footer tab include current date, current time, file path, file name, sheet name, and
even inserting pictures such as a logo into the header or footer.
72) The first and simplest method is to highlight the area you want to print, then go to File and select Print. Inside
the print settings, you can specify what to print. By default, it prints the active sheet, which includes all content. In
the print settings, click the dropdown menu where it says Print Active Sheets. From the options, select Print
Selection. This will restrict printing to only the area you have highlighted. This way, no charts, SmartArt, extra
formulas, or titles will be printed—just the selected content.
73) The second option is ideal if you regularly print the same section of a document. For example, if every week
you receive a document and only want to print a particular section that updates weekly, you can set up a Print
Area. To set a print area, first return to the normal layout view. Then, go to the Page Layout tab and find the Print
Area option. Select the data you want to designate as the print area, then click Set Print Area. Once the print area
is set, you can select any cell or range outside of it, but when you go to File > Print, only the designated print area
will be printed. The print settings will still show Print Active Sheets, but it will print only the print area you
defined. If you want to temporarily disable the print area without deleting it, you can use the Ignore Print
Area option. This will revert the print settings back to the default, printing the entire active sheet. You can toggle
this option on or off as needed to switch between printing the print area and the full sheet.
75) The workbook is now ready to be saved as a template. To do this, go to the File tab and select Save As. Click
the Browse button to open the Save As dialog. By default, Excel saves files as .xlsx workbooks, but to save as a
template, change the file type to "Excel Template (*.xltx)" from the dropdown menu.
When you select Excel Template, the save location changes to the Custom Office Templates directory. Saving your
template here is important because Excel will look in this folder when you want to create new documents based on
your template. If you save it elsewhere, you will have to manually search for it each time.

Chapter 9 Working with Excel Templates

76) The workbook is now ready to be saved as a template. To do this, go to the File tab and select Save As. Click
the Browse button to open the Save As dialog. By default, Excel saves files as .xlsx workbooks, but to save as a
template, change the file type to "Excel Template (*.xltx)" from the dropdown menu .
When you select Excel Template, the save location changes to the Custom Office Templates directory. Saving your
template here is important because Excel will look in this folder when you want to create new documents based on
your template. If you save it elsewhere, you will have to manually search for it each time.

Chapter 10 Working with an Excel List

77) How to Perform a Quick Sort in Excel
Here is a simple method to sort a list by a single column in Excel:
1. Click anywhere in the column you want to sort by, for example, the Last Name column.
2. Go to the Data tab on the ribbon.
3. Locate the Sort & Filter group.
4. Use the first two buttons for quick sorts: Sort Ascending (A to Z) or Sort Descending (Z to A).
5. Click the Sort Ascending button to sort the list by last name in ascending order.
78) Accessing the List and Sorting Options
First, click into your list. Since you are sorting by multiple columns, it does not matter which column you click
into, as long as you are within the list itself. Once inside the list, go to the Data tab and open the sorting and
filtering area. This time, instead of the quick sort options, click the big Sort button. This opens the Custom Sort
window.
Using the Custom Sort Window
In the Custom Sort window, you will see your current sort order. For example, you might be sorting the column
"Last Name" by values in ascending order. This represents one level of sorting. To add a secondary level of
sorting, click the Add Level button in the top left corner. This adds a second sort level. For this second level, select
the "First Name" column, continue sorting on values, and choose ascending order. Then click OK.
79) Performing a Custom Sort by Month
To sort the months chronologically, you need to perform a custom sort:
1. Click anywhere in your list.
2. Go to the Data tab and click the large Sort button to open the Custom Sort dialog.
3. In the Sort dialog, set "Sort by" to the Month column.
4. Ensure "Sort On" is set to Values.
5. Change the Order dropdown to "Custom List...".
 6. In the Custom Lists dialog, select the list of months spelled out (e.g., January, February, March, etc.).
7. Click OK to confirm the custom list.
8. Choose ascending or descending order as desired.
9. Click OK to apply the sort
80) To use AutoFilter, click anywhere in your list. It does not matter where. Then go to the Data tab, back to Sort
and Filter, and click the big Filter button. Unlike the Sort button, the Filter button does not open an additional
window. Instead, it changes a property of your list. You will see little dropdown arrows appear next to each column
header. These dropdown arrows are important. They allow you to filter the data in each column. For example, to
filter for July records, click the dropdown arrow next to the Month column. In the dropdown menu, you can sort
the data or apply text filters such as equals, begins with, or contains specific characters. You can also use the
search filter to select specific values. To filter for July, first deselect all values, then select July only, and hit OK.
The list will now display only the July records. I click the dropdown, select what you need, and hit OK. Now you
have only the July records visible, making it super easy to find those specific records.
81) To clear the filter and return to viewing all records, go to the Data tab in the Sort and Filter area and click the
Clear button. This will restore the full list of records.
82) First, click anywhere inside the list. Then, go to the Data tab and click on the Subtotal button on the far right. This opens
the Subtotal window.

In the Subtotal window, specify the following:

 At each change in: select the column to subtotal by, which is Product in this case.
 Use function: select the type of calculation, such as Sum.
 Add subtotal to: select the column to subtotal, which is Sales.
After filling in these options, click OK to apply the subtotals
83) Exploring the Grouping Feature
On the left side of the screen, you will notice three numbered buttons with vertical bars. These represent grouping
levels:
 Clicking number 2 shows only the subtotals.
 Clicking number 1 shows the grand total.
 Clicking number 3 shows all the detailed records.
You can collapse or expand groups individually by clicking the minus or plus signs next to each group. For
example, you can collapse the chocolate chip and strawberry details to focus only on fudge brownie or vanilla.

84) However, when you sort the list, the manually applied formatting moves with the data, causing the alternating
row colors to become inconsistent. Some rows that were previously formatted may end up adjacent to each other,
breaking the pattern. For example, if you sort by last name using the Data tab's ascending sort, the highlighted
rows shuffle and the alternating pattern is lost. This happens because the formatting is part of the cell content and
moves with the data during sorting. Let me undo that sorting and return to the default list. I will also turn off the
filters. Now, if I perform a simple count of the records using the COUNT function, I see there are 51 records in
this list. What happens if I filter the list? For example, I filter the Department column to show only departments
"A" and "C." The list now shows fewer records, but the count calculation still shows 51, which is the total number
of records, not the filtered count. This is because the COUNT function counts all records, regardless of filtering.
The calculation does not update dynamically with the filter, which can be confusing when working with filtered
data. I will undo the filter and delete the count calculation. These issues with manual formatting and calculations
are common challenges when working with lists in Excel.
85) This is where the "Format as Table" feature becomes useful. Click anywhere inside your list, then go to the
Home tab. Near the right side, you will find the Styles group, which contains the "Format as Table" option. At this
point, you are just applying formatting, so you can choose any style you like. After formatting the list as a table,
Excel automatically applies alternating row colours, so you do not have to do this manually. It also adds filter
dropdown arrows to the headers, integrating sorting and filtering features directly into the table. Additionally, a
new "Table Tools" tab appears at the top of the screen with a Design tab. Here, you can customize table styles,
remove duplicates, export data, and access other features.
86) imagine your list grows over time and you want to add more records. Instead of adding rows below the table,
you can drag the small handle at the lower-right corner of the table to extend it. This adds new rows to the table,
and any formatting and formulas automatically apply to the new rows.
87) First, I want to identify which IDs are duplicated. I will highlight the IDs by clicking on cell A2. Then, I will
use a keyboard shortcut to select the rest of the IDs: Ctrl + Shift + Down Arrow. This shortcut selects all values in
the column until it reaches an empty cell. Now that the IDs are highlighted, I will use the feature called
Conditional Formatting with a specific condition to find duplicates. To do this, I go to the Home tab, then to
Styles, and select Conditional Formatting. Next, I choose Highlight Cell Rules, and at the bottom, I select
Duplicate Values. In the Duplicate Values dialog, I choose to find duplicates and select a formatting style, such as
Light Red Fill with Dark Red Text. After clicking OK, the duplicate IDs are highlighted.
88) If you did not format your list as a table, then you would need to go to your Data tab. On the Data tab, you will
find "Remove Duplicates". So it just depends: if you have not formatted your list as a table, go to the Data tab; if
you have, you can go to the Design tab. Either method is exactly the same. For me, because I have formatted my
list as a table, I will go ahead and select "Remove Duplicates". This opens the Remove Duplicates window, where
it wants me to identify what I consider a duplicate by telling it which columns it should compare to identify
duplicates.

Chapter 11 Excel List Functions

89) This is where the DSUM function, or database sum, comes in. This function treats the list inside Excel as a
database, a table of data with column headers along the top and rows of records below.
By utilizing the DSUM function, we can add criteria to the sum. We can tell Excel to sum the Total Sales only
where the Category is equal to Rent, or Telemarketing, or any other criteria we specify.
90) For DSUM to work, the first step is to set up the criteria section of the function. We need to identify which
column we want to search for a value within.
For example, we will tell Excel to go to the Category column and find the value Rent, then give us the sum of that
record. Off to the right, I will put in a couple of pieces of information to assist with this function. Starting inside
cell I2, I will type Category. This text must match exactly the column header in the list, including spelling and no
extra spaces. If I go into that cell and look at the formula bar, it is just "Category" with nothing else. It is spelled
exactly as it is in the list. This is very important because Excel uses this to identify the column to search for our
criteria. Right below that, in the next adjacent cell down, I will put the criteria I want Excel to search for. In this
case, I will type Rent.

91) Setting Up the DSUM Function

I will move to cell J2, where I will place the header for the value we are about to calculate. I will label this "Total
Sales." Then, I will move down one cell to build the calculation that sums the sales where the category column
equals "Rent."
Chapter 12 Entering the DSUM Formula
In cell J3, I start typing the formula by entering =D. A list of functions appears, and I select DSUM. The DSUM
function adds the numbers in the specified field column of records in the database that match the conditions you
specify. This is exactly what we need: to sum the sales where the category equals "Rent."
After typing =DSUM(, the function requires three arguments:
1. Database: The range of the list.
2. Field: The column to sum.
3. Criteria: The conditions to apply.
To make this easier, I click the fx button to open the argument window

92) Selecting the Database Range

For the database argument, I select cell A1, the top of the list. Then, I use shortcut keys: Control + Shift + Down
Arrow to highlight the entire column down to the bottom, and Control + Shift + Right Arrow to include all
columns. This selects the range from A1 to F59.
If shortcut keys do not work, you can type the range manually or click and drag to select it. The important part is
to include the entire list in the database argument. Next, the field argument requires the column to sum, which is
the "Total Sales" column. Since we have already selected the entire list, we can simply click on cell F1, which
contains the "Total Sales" header. Alternatively, you can enter the column label in double quotation marks or the
column number representing its position in the list. Using the cell reference F1 is convenient and reduces the
chance of misspelling.
93) Defining the Criteria Range
Finally, the criteria argument is the range that includes the header and the adjacent cell containing the criteria
value. In this case, the range is I2 to I3, which includes the header and the cell with the value "Rent." This tells the
DSUM function to sum only those records where the category equals "Rent."
After entering all three arguments, I click OK, and the DSUM function returns the total sales for the category
"Rent." This function sums a range of sales based on specified criteria without needing to filter or alter the original
list. The DSUM function does all the work for you; you only need to set up the header and the criteria correctly.
94) Extending DSUM to Multiple Criteria (OR Condition)
What if you want to sum the sales for multiple categories, such as "rent" or "software"? This is known as an OR
statement, where multiple criteria exist in a single column. The goal is to sum values where the category is either
"rent" or "software."
Setting Up Multiple Criteria
To implement this, place the criteria vertically under the column header in the criteria range. For example, list
"rent" in one cell and "software" directly below it. This setup allows DSUM to recognize multiple criteria for the
same column. In the DSUM formula, update the criteria range to include both criteria cells. For instance, if the
original criteria range was from I2 to I3, change it to I2 to I4 to include both "rent" and "software." This ensures
the function sums values matching either criterion.
95) Adding a Second Criterion for AND Logic
Next, I want to add another criterion, but not for the category column. Instead, I want to add it for the division
column. To do this, I need to add another column header. I will go to cell H2 and type "Division" exactly as it
appears in the data list to ensure the function recognizes it. Now, I want to add the division criterion and specify
"North" as the division. This creates an AND statement where total sales are summed only if the category equals
Rent and the division equals North. To update the formula, I will change the criteria range from I2:I3 to H2:I3,
including both the Division and Category columns. After applying this, the total sales reflect the sum where both
conditions are true
96) Similarly, DAVERAGE calculates the average. You set up the header and criteria in the same way for both
[Link] database range is from A1 to F59. The field is specified as F1, which corresponds to the "total sales"
column. The criteria range is I2 to I3, where the criteria specify the category as "rent." This setup will calculate the
average sales for the category "rent." The formula looks like this:=DAVERAGE(A1:F59, F1, I2:I3)
97) In cell J9, we will enter the DCOUNT formula to count the number of sales in the Supplies category. Begin
typing =DCOUNT to start the function. It is important to note that Excel provides both DCOUNT and
DCOUNTA functions. DCOUNT counts only numeric values in the specified field, while DCOUNTA counts all
non-blank values, including text and numbers. Since we want to count numeric sales values, we will use
DCOUNT. The syntax requires three arguments: the database range, the field to count, and the criteria range. For
the database argument, select the range A1 to F59, which contains the full dataset. For the field argument, select
cell F1, which is the header of the Sales column. For the criteria argument, select the range I8 to I9, which
contains the Category header and the criteria "Supplies". Close the parentheses and press Enter. The result will
show the count of sales entries where the category is Supplies. This count is achieved without any filtering or
sorting
98) Introducing the SUBTOTAL Function
We proceed to cell F1, adjacent to the SUM function, to demonstrate the SUBTOTAL function for comparison.
We begin typing =SUBTOTAL and select it. The function requires two arguments: Function_num and Ref1.
Function_num specifies the type of subtotal calculation desired, such as sum, average, or count. Ref1 is the range
of cells to subtotal. To assist with argument selection, we use the function's argument window by clicking the fx
button. The Function_num argument accepts numbers from 1 to 11, and also from 101 to 111, each representing a
specific summary function. For example, 1 corresponds to AVERAGE, 2 to COUNT, and 9 to SUM. The numbers
1 to 11 include hidden rows in calculations, whereas 101 to 111 ignore hidden rows. This distinction is important
when working with filtered or hidden data.
99) Advantages of SUBTOTAL with Filtering
While the SUM function calculates the total regardless of filters, the SUBTOTAL function dynamically updates
when filters are applied. For instance, when filtering the list by a specific division, such as East, the SUBTOTAL
function recalculates to show the sum for only the visible records. In contrast, the SUM function remains
unchanged, still showing the total for all records, including those filtered out. This dynamic behavior makes
SUBTOTAL particularly useful for filtered datasets. Although SUBTOTAL and DSUM functions yield similar
results, their implementation differs significantly. SUBTOTAL requires filtering the list to perform its calculations
effectively, whereas DSUM uses criteria specified in cells without needing to filter the list. This
difference affects how and when each function is best applied.

Chapter 13 Excel Data Validation

100) Purpose and Benefits of Data Validation
Data validation helps ensure that when data is collected or entered by users, it adheres to specific formatting and
correctness standards. This includes:
 Ensuring correct spelling.
 Enforcing consistent phone number formats.
 Restricting numeric values to a defined range.
 Preventing extraneous or invalid data entries.
By controlling how users input data, data validation maintains data integrity and consistency

101) The first step to applying data validation is to select the cells where the validation will be applied. I will click
inside cell B4, then hold the Shift key and press the B30 cell to select the range from B4 to B30. This is the range I
want to apply data validation to. We could select the entire column if we expect the list to grow to hundreds or
thousands of records, but for demonstration, I will use B4 to B30.
With the range highlighted, I will go to the Data tab at the top of the screen. On the right side, under Data Tools,
there is a Data Validation option. I will click the lower half of the Data Validation button to see more options and
select Data Validation from the dropdown.
102) Inside the Data Validation window, there are three tabs: Settings, Input Message, and Error Alert. We will
focus on the Settings tab for now and ignore the other two. The current validation criteria allow any value,
meaning you can type anything into cells B4 to B30, including numbers or misspelled items. Excel does not
restrict entries at this point, but we want to control this.
Instead of allowing any value, I will click the dropdown under Allow and select List. This option creates a combo
box, a dropdown menu. Once List is selected, the Source field becomes available, where we create the list of
allowed values.
In the Source field, I will type the makes that users can pick from, separating each value with a comma. For
example: Ford, Chevy, Pontiac, Oldsmobile, Dodge. It is important to spell each value correctly here because this
list will be used by everyone selecting from the dropdown

103) For example, if I enter a new record with ID 50, I can select Ford from the dropdown in the make field and
continue filling out the record. This dropdown assists users in entering consistent and accurate data.
One important note: in some regions, the list separator is not a comma but a semicolon. If you use a comma and it
is not correct for your region, the dropdown will show one long list instead of individual selectable items. In that
case, reselect the cells, go back to Data Validation, and change the separator from commas to semicolons
104) Selecting Cells for Validation
First, select the cells where you want to apply the validation. For instance, select from cell I4 down to I30 by
clicking I4, holding the Shift key, and clicking I30. After selecting the cells, go to the Data tab on the ribbon, then
click Data Validation to open the Data Validation dialog box.
105) Setting Decimal Range
For the rate column, select the decimal option. Then set the data operator to "between" and enter 14.95 as the
minimum and 39.95 as the maximum value. Once the decimal range is set, click OK to apply the validation.
Unlike list validation, this will not create a dropdown menu, but any value entered into the selected cells will be
validated against the criteria.
106) To improve user experience, we can customize the error message displayed when invalid data is entered in a
validated column. Let's proceed to modify this [Link], cancel the current invalid entry to clear the cell.
Then, select the range of cells where the data validation applies. In this case, select cells from B4 to B30, which is
the same range previously set up. Navigate to the Data tab on the ribbon and click on Data Validation. Previously,
we adjusted settings under the "Settings" tab to define validation criteria. Now, switch to the "Error Alert" tab to
customize the error message.
107) The first option here is the "Style" dropdown, which controls the icon displayed in the error message box and
the buttons available to the user. There are three styles: Stop, Warning, and Information. The default style is
"Stop," which shows a red X icon and provides "Retry" and "Cancel" buttons. This style requires the user to enter
a valid value before proceeding; they cannot bypass the validation. Alternatively, selecting the "Warning" style
displays a yellow triangle with an exclamation mark. This style allows users to choose whether to correct the entry
or continue with the invalid value by clicking "Yes"
108) To make this process more intuitive and interactive for users, we can provide a dropdown menu in cell I5
with all valid options to choose from, similar to the "make" dropdown we created earlier.
Previously, for the "make" dropdown, we went to Data > Data Validation, selected "List" under "Allow", and
typed the values directly, such as Ford, Chevy, Pontiac, and so on. This time, instead of typing the list manually,
we will reference a range of cells that contains the list of sales categories. I will move to the right side of the
worksheet, to cell N2, and create a list of categories for the user to select from. For example, in N2 I enter
"technical support", in N3 "telephone", in N4 "copying" (correcting the spelling and clearing formatting), in N5
"overhead", and in N6 "software". With these values entered, I return to cell I5, go to Data > Data Validation, and
change the "Allow" option from "Any Value" to "List". For the source, I select the range N2:N6 where the
categories are listed. After clicking OK, a dropdown menu appears in cell I5, allowing selection from the
predefined categories. This updates the formulas accordingly.
Chapter 14 Importing and Exporting Data

109) On the Windows system, I double-click it, and it automatically opens inside of Notepad Regardless of what it
opens inside of, it's a tab-delimited file. I have column headers along the top, rows of records below, and each
column is separated by a tab on my keyboard. I typed in year, tab, month, tab, type, tab, and so on, or the system
that this got exported from used tabs to separate the data. Ultimately, that tab delimiter will help Excel identify the
data and how it should be structured inside the document, where the column breaks should take place, and so
onThis is tab-delimited, but another very common delimiter is a comma-separated document where each column
or each value is separated by a comma. There are several different types of delimiters, I will go into Get Data from
File and then From Text/CSV. This will ask me to navigate to the file location. I will find the file, sales data text
import, and double-click it to import.

110) After a moment, this opens the Import Wizard. If you are working on an earlier version of Excel, the
beginning step here will be a bit different. They take you through what is called the Text Import Wizard. The first
option on File Origin is Western European, Windows. It is a tab-delimited document. You will notice it is nicely
formatted below. You have other delimiters if your file calls for them. Tab is very common, and comma is another
common delimiter. I will keep it set to tab. Excel does a good job identifying that for us, typically based on the
first 200 rows. In the lower right corner, I have a few options. I can use the Load or Load To operation where I can
create a table and dump it into my Excel worksheet. I am working inside a brand-new blank workbook with a
single sheet. Alternatively, I can go into Transform Data, which opens Excel Power Query where I can start to
transform and clean up the data with various operations. The neat thing about importing data through these
commands is that the table we create is still connected to the original source—the Notepad document I opened
earlier. If someone updates that text file, I can refresh my table in Excel to get the updated data.

111) The database file is zipped because systems often block files with the .ACCDB or .MDB
extensions. Before using it, you need to unzip it. You can do this by right-clicking the zip file and
selecting an extraction option such as "Extract All" or "Unzip," depending on your tool. After
extracting, the database file will appear in the same folder. You can open it to see the
Microsoft Access interface. Upon opening, you may need to enable content to access the
[Link] left-hand side shows the tables contained in the database. A database stores
data that you can query, filter, sort, and report on. For example, opening the "Orders" table
reveals data similar to an Excel spreadsheet, with headers such as Order ID, Customer,
Employee, and so on, along with approximately 830 records. There are multiple tables in
the database, including Categories, Customers, Employees, and others. In this video, we
will focus on importing the Orders table into Excel.
112) To import the data into Excel, close the Access database and return to Excel. Add a
new worksheet tab where you want to import the data.
Go to the Data tab, then select "Get Data" from Database, and choose "From Microsoft
Access Database." You also have options to import from other databases such as SQL
Server, which is a larger database system typically hosted on a server.
Navigate to the folder where you extracted the Northwind database file and select it. Excel
will connect to the database and display a list of all queries and tables available
To import, click "Load To," choose "Table" and "Existing Worksheet," then click OK. Excel
will import the data into the selected worksheet.

113) Accessing Legacy Wizards

To access these legacy wizards, navigate to the Data tab at the top of your Excel screen.
Then, select "Get Data." On my system, I have an option labeled "Legacy Wizards."
Clicking on this reveals options such as the Access wizard and the Text legacy wizard, both
of which I have activated. If you do not see the Legacy Wizard options under the Data tab's
Get Data menu, it is because you need to enable them in Excel's settings. To do this, go to
the File tab and select "Options" at the bottom of the menu. In the Options window,
navigate to the Data section. At the bottom of this section, you will find a setting labeled
"Show legacy data import wizards." Using the Legacy Wizards Once activated, these
options will appear under the Data tab's Get Data menu as Legacy Wizards. This provides
an alternative to the Power Query interface, which some users find more complex or prefer
not to use. The legacy wizards offer more direct control over the initial import process.
114) Exporting the Worksheet to a Text File
Let's proceed with exporting. On the EXPORT EMP RECS worksheet, go to the File menu. There are multiple
ways to export your data:
 Under the Export command, select Change File Type. Here, you can choose from several file formats
to export your worksheet.
 Alternatively, use File > Save As and then browse to select the desired file type.
Both methods ultimately produce the same result, though the interface may differ slightly.
For this demonstration, I will select CSV (Comma delimited) as the file type to show a different format from
the tab-delimited file we worked with earlier. I will rename the file to EmpDataExport01 and click Save.
A prompt appears informing us that the selected file type does not support workbooks with multiple sheets.
A prompt appears informing us that the selected file type does not support workbooks with multiple sheets.
Therefore, only the active worksheet will be exported, not the entire workbook. I will confirm by clicking OK.
Another warning may appear stating that some features might be lost if you save the workbook as a CSV file. This
is expected because CSV files do not support Excel-specific features such as formatting or formulas. Since we
only want the raw data, we can safely ignore this warning and close it

Chapter 15 Excel PivotTables

115) The most time-consuming part of building a pivot table is deciding what you want to summarize within that
pivot table. Do you want to see the sales by year or by month? Do you want to break it out by salesperson or by
type? How do you want it to look so you can present that data? That is the most time-consuming part.
Once you have decided what you actually want to report and display, it is really simple to do, and we are going to
take a look at that right now. Before creating the pivot table, a really good piece of advice is to understand there
are two ways to reference the data you want to include inside the pivot table.

116) One way is to reference the range of cells directly. For example, you can specify the pivot table to be based
on cells from A4 down to H448, which covers a couple of hundred records. Although this works, the issue is that
if the data updates—meaning new records are added or records are deleted—the pivot table still looks at the fixed
range A4 to H448. This can cause data to be left out or included incorrectly. Another way to handle this is to turn
the list into a table by formatting it as a table, and then use the table name inside the pivot table reference. To do
this, click anywhere inside the list, then go to the Home tab. Under the Styles section, select Format as Table.
Choose a style you prefer; it does not matter which one you pick. For example, I like blue, so I select Medium
Blue.
117) After formatting, you can find the table name in the Table Design tab. By default, it might be Table3. You
can rename it to something meaningful like YearlySales, ensuring there are no spaces in the name.
When you create the pivot table, you can use this table name. If the table size changes—records added or deleted
—the pivot table will still know where to find the data. Once the data is formatted as a table, click into the list,
then go to the Insert tab and select Pivot Table. The first step in creating the pivot table is to select where the data
is located. Since you clicked inside the table, the table name, such as YearlySales, will be automatically selected.
You also have the option to pull data from an external data source or a data model. For this example, we will pull
from the table range.
select your table, choose to place the pivot table on a new worksheet, and click OK. Excel will create a new
worksheet for the pivot table.
You will see the beginning portions of your pivot table, which may not look like much initially. You should have
the Pivot Table Fields list on the right-hand side and a couple of new tabs at the top of the screen.
118) The beauty of a pivot table is that we can take those column headers and place them into one of the four
sections that make up our pivot table. In the earlier example, I dropped Month into the Rows section, Type into the
Columns, and Sales into the Values section.
Filters allow you to filter the results of your pivot table. For example, if you do not want to see all products, years,
or months, you can filter the summary data through the pivot table. Building a pivot table is a simple process: just
drag the fields into these four sections.
119) Creating Manual Groups in PivotTables
To create a group, highlight the months you want to group, such as January through March. Then, go to the
PivotTable Analyze tab and find the Group section. Here, select Group Selection to build a group from your
selection. Excel will create two groups: one for the selected items and another for the non-selected items. You can
also use Ungroup to remove groups if needed. Note that the Group Field option is grayed out because the months
are not recognized as complete date values by Excel. If you had full date values, this option would allow grouping
by date ranges such as months, quarters, or years. After selecting January through March, click Group Selection.
Excel creates Group1 for these months. You can collapse this group using the minus sign. Repeat this process for
April to May and November to December to create additional groups. Excel assigns default group names like
Group1, Group2, and Group3, which you can rename.
120) Grouping Using Drag and Drop Fields
Another way to create groups is by using drag and drop with existing fields. Remove the grouped months and add
the month field back. Then, drag the Region field into the rows section to break down sales by region. Next, drag
the Salesperson field below region in the rows section to create a hierarchical group showing salespeople within
each region.
121) There are a couple of different ways to apply formatting. In this video, the focus will be on formatting
numeric values. These numeric values represent sales amounts. Currently, they appear as a block of numbers
without thousands of separators or currency symbols. Since these are sales amounts, they should be formatted as
currency. There are two ways to do this. One method is the manual style of formatting cells: highlight a range of
cells, go to the Home tab, and use the Numbers section to apply the proper format, such as [Link] the
PivotTable or its source data is modified, such as adding more salespeople which updates the PivotTable with
more rows, you want to ensure that those additional rows receive the proper formatting as well. Excel is smart and
attempts to apply the same formatting to new rows based on the formatting of above cells, but this does not always
work reliably.
122) Once the layout is set, you could highlight the data and format it via the Home tab, but instead, let's use the
Value Field Settings for better [Link] on the Sales field in the Values section to access the Value Field
Settings [Link] the Value Field Settings window, you can change the field name and how the values are
summarized, such as Sum, Count, or Average.
Below that, there is a Number Format button. Clicking it opens a familiar window where you can select Currency
formatting.
Within the Number Format options, you can adjust decimal places, choose the currency symbol, and set how
negative values are displayed. For this demonstration, defaults are kept.
123) if we want to see both the sum and the average sales for each month, To change the second sales field to
show the average instead of the sum, click on the field's dropdown, select "Value Field Settings," and change the
calculation from Sum to Average. Then rename it appropriately, for example, "Average of Sales." Click OK. To
change the second sales field to show the average instead of the sum, click on the field's dropdown, select "Value
Field Settings," and change the calculation from Sum to Average. Then rename it appropriately, for example,
"Average of Sales." Click OK.
124) we want to analyze whether sales increased or decreased month over month Click on this new field's
dropdown, select "Value Field Settings," and then go to the "Show Values As" tab.
By default, "Show Values As" is set to "No Calculation," meaning the values are displayed as sums. However, you
can change this to various calculations such as "Difference From" or "% Difference From." To see month-over-
month percentage changes, select "% Difference From." Then specify the base field as "Month," which is how we
want to compare the data. The base item can be set to "Previous," meaning each month is compared to the prior
month. You can also set the base item to a specific month if you want to compare all months to that one. For
example, if June was a stellar month, you could compare every other month against June to see relative
performance.
After setting these options, rename the field to something descriptive like "Monthly Percentage Month Over
Month" and click OK.
125) PivotTables offer a feature called drill down, which allows you to explore the detailed data behind any
summarized value. By double-clicking on a value in the PivotTable, such as the 51 increase or the total sales
amount for April, Excel automatically creates a new worksheet containing the underlying data that comprises that
value. A common scenario involves having a master list containing data about various individuals, such as team
members or salespeople. If you want to distribute personalized data to each individual without sharing the entire
list, you can create a PivotTable grouped by salesperson or individual. Then, by double-clicking on the values for
each person, Excel generates separate worksheets containing only their respective data. This makes it easy to send
each person their relevant information.

126) To create a PivotChart, click anywhere inside your PivotTable. It does not matter where, as long as you are
within the table. Then, navigate to the PivotTable Analyze tab. On the right side, you will find the PivotChart
option. Click it to open the Insert Chart window, which is very similar to the standard chart insertion window.
Here, select a column chart, specifically the default standard 2D clustered column chart, and click OK. This action
inserts a PivotChart based on your PivotTable data. With the chart selected, you have access to the Design,
Format, and PivotChart Analyze tabs. These tabs provide all the formatting options available for standard charts,
such as changing colors, adding or moving elements like legends and titles. The key difference between a standard
chart and a PivotChart is that the PivotChart updates automatically when you modify the PivotTable structure.
127) To filter by year, drag the Year field into the filter section of the pivot table. This adds a new filter area where
you can select which years to display. The chart will also update to reflect the selected years.
By default, all years are selected. Clicking the dropdown allows you to filter the data by specific years. In Excel
versions from 2010 or 2013 onwards, you can select multiple items in the filter.
128) Slicers are essentially interactive, dashboard-like elements designed to filter your PivotTable results
effectively. To insert a slicer, ensure you are clicked into your PivotTable, regardless of the exact location. Then,
navigate to the PivotTable Analyze tab and select Insert Slicer. This action opens the Insert Slicer window, where
you can choose the field by which you want to filter the results. I will select Year and click OK. This creates the
slicer, which serves as your filter. The slicer can be moved anywhere on the worksheet, formatted, and aligned to
match your report's look and feel, enabling you to create a polished, interactive dashboard based on your
PivotTable and charts. Holding down the Control key allows you to select multiple years
simultaneously.

Chapter 16 Working with Excel's PowerPivot Tools

129) power Pivot allows connecting multiple Excel worksheets through common fields like Customer ID. It
enables creating Pivot Tables based on combined data models from different tables. Naming tables appropriately is
important for managing data connections. Power Pivot enhances reporting capabilities beyond single-table Pivot
Tables.
130) The Pivot Table field list currently shows only the Customer Info data. To include Order Info, you might try
adding more tables. However, Excel prompts that to use multiple tables in your analysis, a new Pivot Table must
be created using the data model.
The data model is part of Power Pivot. To include multiple tables in your dataset, you must create a data model
and base the Pivot Table on it. This enables connecting two different worksheets through a common field such as
Customer ID, creating a data model in Power Pivot, and then building a Pivot Table from that model. This
explains the why and what of Power Pivot. Next, we will look at how to activate Power Pivot and go through the
steps to create a Pivot Table based on the data model inside Power Pivot.
131) To activate Power Pivot, go to the File tab at the top of your screen, then select Options. Inside the Excel
Options window, navigate to Add-ins. There are various add-ins you can use within Excel; some are built-in,
while others you must download before they become available. Add-ins are additional tools not installed by
default. For now, focus on the Power Pivot add-in. After opening the Add-ins section, look at the lower portion of
the screen for the Manage option with a dropdown menu. Select COM Add-ins from the dropdown and click Go.
This opens the COM Add-ins window. Here, you can see any add-ins currently active. For example, I have a PDF
Maker add-in, which integrates Adobe Acrobat with Office applications. Among others, you should see Microsoft
Power Pivot for Excel listed. If you see Microsoft Power Pivot for Excel, check the box next to it to enable the
add-in. Then click OK. This action activates the Power Pivot tab along the top ribbon in Excel.
132) The next step is to create the relationship between these [Link] the Home tab, select the Diagram View.
Currently, we are in the Data View, which resembles an Excel worksheet with row headers and column headers,
though without the A, B, C column labels. The Diagram View presents the structure of the tables and their headers
visually.
In Diagram View, the Customer Info table appears on the left, and the Order Info table on the right. To create a
relationship, click on the Customer ID in the Customer Info table, drag it over to the Customer ID in the Order
Info table, and release. After a moment, a relationship line appears between these two tables.
133) Understanding Table Relationships
The Customer Info table is the parent table, and the Order Info table is the child table. The reason for these
terms is as follows:
 In the Customer Info table, each customer has a unique Customer ID. For example, the value "ALFKI"
appears only once.
 In the Order Info table, the Customer ID can appear multiple times, representing multiple orders placed by
the same customer.
This setup allows multiple orders per customer, which is desirable.
Looking closely at the relationship line, a "1" appears near the Customer Info table, indicating that each Customer
ID is unique there. An asterisk (*) appears near the Order Info table, indicating that the Customer ID can appear
many times. This is known as a one-to-many relationship.
134) In Data View, right-click on the header of the Customer Info table and select Create Relationship. In the
dialog, select Order Info as the related table, and ensure Customer ID is selected in both tables. Click OK to
create the relationship. Both methods result in the same relationship being established. After creating the
relationship, you can return to Data View, where the two tables remain as separate tabs but are now related within
the data model.
135) We have established a relationship between two data sets: Customer Info and Order Info. These are now
connected through the common field of Customer [Link] from the Customer Info tab, navigate to the Home
tab within Power Pivot. About halfway down, there is an option for PivotTable. Clicking the lower half of that
button reveals several options; select PivotTable to proceed. We will create a new PivotTable and place it on a
new blank worksheet by clicking OK. At this point, the process feels familiar, similar to previous PivotTable
creation experiences involving drag and drop.
136) Excel Power Pivot KPIs
The first step is to manage or edit the data model. Navigate to the Power Pivot tab on the far left and click on
Manage. This opens the Power Pivot window where you can work with your data model.
Creating a Calculation for Average Freight To calculate the average freight across all records, click on the freight
column header. Then, in the Home tab of Power Pivot, locate the Calculations group on the right side. The
AutoSum button is active by default, but below it is the "Create KPI" option, which is currently inactive. Before
creating a KPI, we need a calculation or measure to base it on.

Click the dropdown arrow next to AutoSum and select "Average" to create a new row at the bottom showing the
average freight value, which is approximately 78.24.

137) Creating a KPI Based on the Average Freight

With the average freight calculation created, we can now use it to build a KPI. Go back to the Home tab under
Calculations and click "Create KPI." The KPI is based on the average freight measure we just created.
In the KPI setup, you can define the target value either by a measure or an absolute value. Since we do not have a
target measure, we choose an absolute value. The default target is 100, with a color gradient from red (0) to yellow
(40) to green (80 to 100).

138) Alternative Methods Without Power Pivot

If you do not have Power Pivot, you can still create KPI-like visuals using Excel's conditional formatting.
Calculate the average freight in a cell, then use Home > Conditional Formatting > Icon Sets to apply icons based
on value thresholds.

Chapter 17 Working with Large Sets of Excel Data

139) the column headers appear at the top of the list, which means if I scroll down to get to the bottom of the list
or to a specific record, I lose sight of those column headers as they scroll off the top of the screen.
This is very easy to do. First, click in cell A2. The reason for this is that when you freeze panes, everything above
the selected cell will be frozen. Since the headers are in row 1, selecting cell A2 will freeze the first row.
Next, go to the View tab at the top of the screen. In the View tab, find the Window section, and click on Freeze
Panes. Then select Freeze Panes from the dropdown menu. Now, when you scroll up and down, the headers in row
1 remain visible and frozen in place.

140) Group and Outline Feature

Alongside the subtotal tool, there is an additional feature called Group and Outline. This feature allows you to
collapse or expand your subtotals, providing a cleaner view of your data.

Manual Hiding of Columns

You could manually hide columns by right-clicking on a column such as January (column C) and selecting hide.
You can then unhide it when needed. However, this process is manual and not very accessible for frequent use.

141) Using Group to Hide Columns Temporarily

Instead of manual hiding, you can create a group for columns to easily hide or show them. For example, to hide
January temporarily, select column C, go to the Data tab, and click Group in the Outline section. This creates a
group with a horizontal line and a button to collapse or expand the column.
When you collapse the group, the January column is hidden. Clicking the plus sign expands it back out.
Grouping Multiple Columns
You can extend this by grouping multiple columns. For example, select columns C and D (January and February),
then group them. Collapsing the first group hides January, collapsing the second hides January and February.
Similarly, grouping columns C, D, and E (January, February, March) allows collapsing all three months at once.
142) I need to ensure that the column headers repeat on every printed page and that the records continue logically
from one page to the next. This issue arises because the large data set spans multiple pages both vertically and
horizontally.
First, I will set the column headers to repeat on every page. To do this, I go to the Page Layout tab at the top of the
screen, then to the Page Setup section, and click on "Print Titles." This opens the Page Setup window.
In the Page Setup window, under the Sheet tab, there is a "Print Titles" section where I can specify rows to repeat
at the top and columns to repeat on the left. I do not need to repeat columns, but I want to repeat the top row, so I
select row 4 to repeat on every page.
After applying this setting, I return to Print Preview. Now, the headers appear on every page, including pages two,
three, four, and so on. This makes it easier to identify the data on each page.
143) However, there is still an issue:. Some columns that should continue on the next page do not appear until
page 10, which is confusing and disrupts the logical flow of the data.
To understand this better, I switch to the View tab and select "Page Break Preview." This view shows how Excel
divides the data into pages. The first set of data is page 1, the next is page 2, and so on. However, the content from
page 1 spills over into page 10, which is not the desired behavior.
Currently, the pages are ordered to print down the columns first and then move over to the next column, creating a
zigzag or backwards "N" pattern. Instead, I want the pages to print across the rows first and then down, following
a "Z" pattern. To fix the page order, I return to the Page Layout tab and open the "Print Titles" dialog again to
access the Page Setup window. At the bottom of the Sheet tab, there is a "Page order" option. I change it from
"Down, then over" to "Over, then down."

144) Larger Excel documents do not just contain data on single worksheets but can contain data across multiple
worksheets open simultaneously. in addition—while referencing cells in different worksheets. This is known as
a 3-D reference or 3-D [Link] technique creates a formula that references cells across multiple worksheets.

145) Creating a 3-D Formula

. To create the calculation, select the cell where you want the sum to appear. For example, select cell B4, which
corresponds to the town of Beverly. To start the formula, type an equals sign (=).
Next, click on the worksheet named 2013. In the formula bar, you will see the formula beginning with =2013!.
Now, select the cell corresponding to Beverly's circulation in the 2013 sheet
To add the circulation numbers from other years, type a plus sign (+) to indicate addition. Then, click on
the 2014 worksheet and select the same cell for Beverly's circulation. Repeat this process by typing another plus
sign and selecting the corresponding cell in the 2015 worksheet.
The complete formula will look like this
=2013!B4 + 2014!B4 + 2015!B4
After completing the formula, press Enter to return to the Summary sheet. The cell will now display the grand total
circulation for Beverly across all three years.

146) Using the Consolidate Feature

On the Summary sheet, after deleting the towns, I click inside cell A4. Then, I navigate to
the Data tab at the top of the screen, towards the far right, where there is a section called
Data Tools. Here, I select [Link] first step asks what type of consolidation to
perform. There are several functions available, but I will use the standard Sum function.
Next, it needs the references to know where the data is located that we want to sum on
the Summary sheet. I move the Consolidate window aside, click into the Reference box,
then go to the 2013 sheet and select cells A4 to B8 as my first reference. I add this
reference, then repeat the process for the 2014 sheet, selecting the same range and
adding it. Finally, I do the same for the 2015 sheet, ensuring the same range is selected
and added.

147) Consolidate Options

There are three checkboxes available:

 Use labels in: This option helps Excel identify matching labels across sheets, which
is essential if towns have been added or removed over the years.
 Create links to source data: If selected, this creates links from the Summary
sheet to the source sheets, so updates in the source sheets automatically reflect in
the Summary.

I will enable the option to use the left column as labels but will leave the linking option
unchecked. The references we established have two columns: the first column is the town
name, which serves as the label for the circulation numbers. By using the left column as
labels, Excel matches towns like Beverly with Beverly and Cherry Dale with Cherry Dale
correctly during consolidation

148) The Table must be converted to a RANGE before the SUBTOTAL command will work. (Click into the
table -- go to the TABLE DESIGN TAB and use CONVERT TO RANGE) When your data is in a normal range,
Excel just sees it as plain cells with rows and columns.
When your data is in an Excel Table (created by Insert → Table), it has extra features like:

 Automatic filtering and sorting buttons

 Structured references (column names in formulas instead of cell addresses)
 Automatic formatting and expansion when you add new data

Converting a table to a range means you remove those extra “table” features and go back to plain cells, but the
values and formatting stay the same. Then functions like SUBTOTAL will work the way they do in normal
ranges.

149) Up to this point, whenever we have created formulas or performed mathematical

operations using functions, we have referenced cells by their column-row addresses. To
reference something inside an Excel worksheet, we typically use the column and row
[Link] the bottom, in row 10, there is a weekly total row where we want to sum up
each week's data. To do this for week one, if we click into cell B10, we can use the shortcut
key Alt + Equals to bring in the AutoSum feature. This will sum the cell range B5 through
B9, which is the typical way to reference cells in Excel by their column-row addresses.
Another method Excel provides is called named ranges. Named ranges are a powerful
feature that I use frequently. They offer significant advantages but also have some
disadvantages compared to standard column-row addressing.
150) To perform the summation for week one using a named range instead of cell
addresses, the first step is to name the range of cells. To do this, select the range B5
through B9, which contains all the numeric sales data for week one. Then, go to the name
box at the top of the screen, which currently shows the active cell (B5). Click into the name
box and replace the cell address with a name, such as week_1. Note that named ranges
cannot contain spaces, so either combine words or use an underscore to simulate a space.
After typing the name, press the Enter key to accept it. pressing Enter is necessary to
confirm the name. Now, go back to cell B10 and type the formula manually =SUM(. Instead
of entering the cell range B5 through B9, type the named range week_1. Excel will show a
dropdown menu with the named range, which has a distinct icon indicating it is a named
range. Complete the formula by closing the parentheses an Enter. This will sum the range
of cells referenced by the name week_1.

Chapter 18 Working with Excel's Conditional Functions

151) Advantages of Using Name Ranges
 Provides Context: By naming a range, such as "Week 1," the formula becomes more understandable.
Instead of seeing an abstract cell reference, users know exactly what data is being referenced.
 Formula Usage: Named ranges can be used inside formulas, which is especially beneficial when working
with large data sets. For example, when using functions like VLOOKUP or HLOOKUP, naming a whole
table of data simplifies formula construction.
  Navigation Aid: Named ranges help navigate large or complex worksheets. Using the name box in the
top-left corner of the Excel window, you can quickly jump to the named data, even if it is on a different
worksheet.
 Printing Selection: Once navigated to a named range, you can easily print just that selection by choosing
"Print Selection" in the print options, which is useful for focusing on specific data areas.

152) Disadvantages of Using Name Ranges

 Absolute Reference Behavior: Named ranges behave like absolute references. Unlike relative references
(e.g., B5), which adjust when copied across cells, named ranges always refer to the exact same range.
 Copying Formulas Limitation: For example, if you have a formula summing "Week 1" and you copy it
to adjacent columns to calculate "Week 2," "Week 3," etc., the formula will continue to sum "Week 1"
because the named range does not change.
 Reduced Flexibility: This behavior can be a disadvantage when you want to create a formula once and
copy it across multiple cells to reference different data ranges dynamically.
153) To edit a name range, we do not go back to the name box where name ranges are created. Instead, navigate to
the Formulas tab at the top of the Excel window. Within the Formulas tab, there is a section called Defined
Names, which contains several operations, including the Name Manager.
Clicking on Name Manager opens the Name Manager window. Here, you can see all the name ranges in the
workbook. Some name ranges may be broken, indicated by a REF error, which occurs if a worksheet or range was
deleted or is no longer valid. After deleting unnecessary name ranges, only the "week one" name range remains.
Selecting it allows you to click the Edit button. Below, you can view the current cell reference for this name range
and make any necessary changes directly within this interface.
The Name Manager interface enables you to interact with name ranges by editing, deleting, or even creating new
ones. This centralized management makes handling name ranges more efficient.
154) First, click into cell H5, corresponding to H. James. Then, navigate to the Formula tab. Within the Function
Library, select Logical, and then choose IF. This action opens the argument window for the IF function.
The IF function requires three arguments:
1. Logical test: The condition we want to evaluate.
2. Value if true: The result if the condition is true.
3. Value if false: The result if the condition is false.
For the logical test, we want to check if H. James's total sales in cell F5 are greater than or equal to the goal in cell
I2. We enter the condition as F5 >= I2.
Next, specify the value if true. If the condition is met, we want the function to return "yes". For the value if false,
we want it to return "no". Note that Excel automatically places quotation marks around these text values,
indicating they are string literals. Thus, the IF function reads: If F5 is greater than or equal to I2, then return "yes";
otherwise, return "no". The function evaluates the condition and returns the appropriate result. This function is
powerful because it allows you to specify a condition and define what to do if the condition is true or false. Once
set up, you can copy this formula down the column to evaluate all salespeople efficiently. After entering the
formula, click OK. The cell will display "yes" if the goal is met or "no" if it is not. Try creating this formula in cell
H5 by going to Formulas > Logical > IF and setting up your condition accordingly.
155) IF function contains two cell references: F5 and I2. From the location of cell H5, F5 is one or two cells to the
left, and I2 is one cell up and one cell to the right. These are relative references.
When we copy that formula down, it adjusts accordingly. For example, the first formula correctly compares
136,245 to 34,000 in I2. However, the next formula, which is for K9, references F6 and I3. This is because the
relative references move down as the formula is copied. This causes errors because the reference to I3 is not the
intended fixed cell.
To fix this, we can use an absolute reference by adding dollar signs to the cell reference. For example, changing I2
to II2 locks the reference so that when the formula is copied down, it always points to cell I2. This ensures the
formula compares each total against the fixed goal value in I2.

156) Using Named Ranges

Another option is to name the cell I2. We can assign a name to that cell and then use the name in formulas instead
of the cell reference. To do this, select cell I2, click into the name box, and type a name such as "Goal". After
pressing enter, the cell is named "Goal".
Now, in the formula, replace the reference to I2 with the name "Goal". The formula will highlight the referenced
cell with a color corresponding to the name. When you copy the formula down, each formula references the named
range "Goal", which acts as an absolute reference but with added clarity.
Using named ranges provides context to the formula, making it easier to understand. For example, instead of
seeing II2, you see "Goal", which indicates the formula is comparing totals against a goal value. Named ranges can
also be reused across different worksheets and formulas.

157) The goal was to use the IF function to test a condition and return results accordingly. To remind you, the IF
function syntax involves a test, a TRUE result, and a FALSE result. This is the standard IF function structure. I
frequently use IF functions because they allow you to specify a condition to test and define what Excel should do
if the test is TRUE or FALSE. what if you have multiple tests? For example, you need to perform several tests,
and all must be TRUE for the salesperson to receive the TRUE result of the IF function. If any test fails, the result
should be FALSE. The default IF function only allows one test. For instance, in the "Goal" column, we specified a
single test: if cell F5 is greater than or equal to the goal in I2. But for the bonus status, multiple conditions must be
met: the salesperson must have met the goal and each week they must have made 8,000 or more. This means
potentially five tests to determine bonus eligibility.

158) To handle multiple conditions, we introduce another function called the AND function. It tests each condition
and returns TRUE only if all conditions are TRUE; otherwise, it returns FALSE. The IF function can then use this
result to determine the final output.
Let's examine the AND function. Typing "=AND(" and opening the function arguments window reveals that it
checks whether all arguments are TRUE. You can input up to 255 arguments. If all are TRUE, AND returns
TRUE; otherwise, it returns FALSE.
For example, the first test is whether the salesperson met the goal. Then, each week is tested to see if sales were
8,000 or more. If all these tests pass, AND returns TRUE. If any test fails, such as for Caden who did not meet the
goal, AND returns FALSE immediately.
159) After entering these tests, the AND function returns TRUE if all conditions are met. This result can then be
used inside an IF function to return "Yes" or "No" for bonus eligibility. However, writing each test individually is
inefficient, especially if you have 52 weeks of data. To simplify, we can nest the MIN function inside the AND
function. The MIN function finds the minimum value in a range, such as sales from B5 to E5. We then check if
this minimum value is greater than or equal to 8,[Link] nesting MIN inside the logical test, we avoid writing
multiple comparisons. This approach is much cleaner and more efficient, especially for large datasets. The nested
formula returns the same result but with less complexity. In summary, the AND function tests multiple conditions
and returns TRUE only if all are TRUE. Nesting functions, such as placing MIN inside AND, This concept of
nesting functions enables Excel to perform more complex logical operations.
160) The standard IF function accepts only one logical test, so we will nest the AND function inside the logical
test argument of the IF function to evaluate multiple conditions simultaneously.
To begin, I select cell I5 and go to Formulas > Logical > IF. The first argument of the IF function is the logical
test. We have five tests: whether the goal was met and the weekly values. However, to simplify, we will narrow it
down to two tests by nesting the MIN function as well.
For the logical test, I type AND( to open the AND function. To be more precise with the arguments, I click on the
AND function name in the formula bar, which opens the AND arguments dialog. (On Windows, clicking the
function name changes the formula bar; on Mac, you click AND and then the FX button to open the formula
builder.)
161) Within the AND arguments, the first test is cell H5, which we check to see if it equals "yes" (using H5 =
"yes"). The second test nests the MIN function: MIN(B5:E5) >= 8000. This checks if the minimum value in the
range B5 to E5 is greater than or equal to 8,000. These two tests complete the AND function, which will return
TRUE only if both conditions are met.
After completing the AND function, I proceed to the IF function's "value if true" argument and enter "bonus" (with
quotes). For the "value if false" argument, I enter "no bonus". This means if both conditions in the AND function
are true, the cell will display "bonus"; otherwise, it will display "no bonus".
". Copying the formula down, the next two people who did not meet the goal receive "no bonus". The following
person who met both conditions receives a "bonus", while the last person, who met the goal but had some weeks
below 8,000, receives "no bonus".
=IF(AND(H8="YES",MIN(B8:E8)>=8000),"BONUS","NO BONUS")
162) Using the COUNTIF Function
To use COUNTIF, select cell F12 and type =COUNTIF(. This function requires two arguments:
 The range to check.
 The condition to count.
For example, we select the range H5:H9 and specify the condition "yes" in double quotes.
In newer versions of Excel, starting with 2013, there is also the COUNTIFS function, which allows specifying
multiple conditions. However, for this example, we use the simpler COUNTIF function.
After entering =COUNTIF(H5:H9, "yes") and pressing Enter, the result is 3, indicating three salespeople have
reached their goal in the specified range.
This function is very useful for quickly counting values that meet a condition, especially when working with large
datasets.
163) Constructing the SUMIF Formula
Let's build the SUMIF formula for total units in cell H3. The SUMIF function requires three
arguments:
1. The range to search for the criteria (in this case, the Store column).
2. The criteria to match (store number 3000).
3. The range to sum (the Units column).
We start by typing =SUMIF( and then specify these three arguments.
Selecting the Range to Search
For the first argument, the range to search, we select the Store column from B3 down to
B272 by clicking B3 and pressing Control + Shift + Down Arrow.
Specifying the Criteria
Next, we specify the criteria, which is the store number 3000. Since the formula covers cell
G3, we cannot click it directly, but we can type G3 to reference the cell containing 3000.
This makes the formula dynamic, allowing us to change the store number in G3 and update
the result automatically.
Selecting the Range to Sum
Finally, we select the range to sum, which is the Units column from E3 down to E272.
164) Let's go to cell C3, which contains the VLOOKUP for Howard. In the formula bar, just after the equal sign, I
will insert the IFERROR function and nest the existing VLOOKUP inside it.
excel Code Sample
=IFERROR(VLOOKUP(A3, MasterEmployeeList, 2, FALSE), "Employee ID not found")
The IFERROR function has two arguments:
 The first is the value to check, which is the VLOOKUP function.
 The second is the value to return if the first argument results in an error, in this case, the text "Employee ID
not found".
After entering this formula and pressing Enter, if the employee ID exists, the first name is returned as usual. If the
employee ID does not exist, instead of an error, the message "Employee ID not found" is displayed.
Copying this formula down the column, we see that for IDs that do not exist, the custom message appears instead
of error codes. For example, entering 100 again results in "Employee ID not found".

Chapter 19 Working with Excel's Lookup Functions

165) Using the VLOOKUP Function
In cell D3, where the last name should appear, start typing the formula by
entering =VLOOKUP(. The function requires four arguments:
1. Lookup Value: The value to search for (e.g., employee ID).
2. Table Array: The range containing the data.
3. Column Index Number: The column number in the table array from which to
retrieve the value.
4. Range Lookup: Optional; specifies whether to find an exact or approximate match.
After typing the function, click the "fx" button to open the Function Arguments window for
a more visual input.
166) Setting the Lookup Value
For the lookup value, select cell B3, which contains the employee ID to search for. To ensure the formula works
correctly when copied down and across, make this a partial absolute reference by locking the column but allowing
the row to change. Press the F4 key three times to lock the column B, resulting in $B3. If the F4 key does not
work, manually type $B3.
Defining the Table Array
Next, specify the table array where the data is located. Switch to the "Master Employee List" worksheet and select
the entire data range including headers, from cell A1 to I38. Use the shortcut Ctrl + Shift + Down Arrow and Ctrl
+ Shift + Right Arrow to select the full range. Make this range absolute by pressing F4 once, which adds dollar
signs to lock the range as $A$1:$I$38. If F4 is unavailable, manually add the dollar signs.
Important Note on Lookup Value Position
It is crucial that the lookup value (employee ID) is in the first column of the table array. In
this case, column A of the master employee list contains the employee IDs, satisfying this
requirement for VLOOKUP to function properly.

167) Setting the Column Index Number

The column index number tells VLOOKUP which column's data to return once it finds the
matching lookup value. Since the last name is in the second column of the master
employee list, enter 2 for this argument.
Specifying Range Lookup
The range lookup argument is optional and determines whether to find an exact or
approximate match. For exact matches, enter FALSE. This is recommended in most cases to
avoid incorrect matches. If the lookup value is not found, VLOOKUP will return
an #N/A error, which is visible in cells where the employee ID does not exist in the master
list.

168) Creating the HLOOKUP Function

We will create the formula to find Warehouse Two Inventory for the product XP 200.

Starting in cell B6, we enter the formula by typing =HLOOKUP(.

1. Lookup Value: The value to search for.

2. Table Array: The range of cells to search within.
3. Row Index Number: The row number in the table array from which to return a value.
4. Range Lookup: TRUE for approximate match or FALSE for exact match.

169) Filling arguments

 Lookup Value: We select cell B3, which contains the product code XP 200. To ensure this reference
remains constant when copying the formula, we make it an absolute reference by pressing F4, resulting in
$B$3.
 Table Array: We select the entire Master Inventory List range from A2 to G5. This range is also made
absolute by pressing F4, resulting in $A$2:$G$5.
 Row Index Number: Since the first row contains product codes, and Warehouse Two Inventory is in the
third row of the table array, we enter 3.
 Range Lookup: We want an exact match, so we enter FALSE.

170) The main limitation of VLOOKUP is that it searches for the lookup value only in the
first column of the table array. For example, if the employee ID is not in the first column,
VLOOKUP cannot find it properly.
If you insert a new column at the beginning of the list, the lookup range shifts, and
VLOOKUP may look in the wrong column. Moreover, VLOOKUP cannot look to the left; the
column index number must be a positive number to the right of the lookup column.
This means you must keep the employee ID as the first column for VLOOKUP to work
correctly, which might require restructuring your data and potentially disrupting other
processes.
Additionally, VLOOKUP is resource intensive and can be slow when used extensively in a
workbook, causing performance issues.
To overcome these limitations, we will explore the INDEX and MATCH functions. Together,
they perform similar tasks to VLOOKUP and HLOOKUP but without those constraints.
171) The INDEX function allows you to specify a row and column number to retrieve the value at
that position
example, to find the value at column 3, row 10, you would use INDEX to return the value in
that position. In the example, that value is "Linda". Let's try using the INDEX function in
Excel. In cell C4, enter the formula =INDEX(. The function has two main forms; we will use
the one with three arguments: array, row number, and column number. The array is the
range of data you want to search. It can be a single column, a single row, or multiple rows
and columns. Select the range from the master employee list worksheet, for example,
A1:I38 Next, specify the row number and column number. For instance, row 10 and column
3 will return the "Linda" from the selected range.
Lock the range with absolute references (e.g., AA1:II38) so that when you copy the formula
down, the range remains constant This is the basic usage of the INDEX function: it returns
a value based on a position within your array.

172) The MATCH function performs the opposite operation. It takes a value, such as an
employee ID, and returns its position within the dataset or array. Essentially, it gives back
the numeric position of the value you provide.

Using the MATCH Function

Let's demonstrate this in cell D4. We will have the MATCH function search for employee IDs
within a master list and return their positions.

In cell D4, type =MATCH( and open the parentheses. To assist visually, you can click the
FX button to open the function arguments dialog.

The first argument is the lookup value, which is the value you want to find. Here, it is the
employee ID in cell B4, which corresponds to employee ID 1054.

The second argument is the lookup array, which is the range where the function will search
for the lookup value. In this case, it is the master employee list from cells A2 to A38. This
range should be locked by pressing F4 to make it an absolute reference.

The third argument is optional and specifies the match type. Unlike VLOOKUP or HLOOKUP,
which use TRUE or FALSE or leave this blank, MATCH uses numeric values: 1, 0, or -1.

 If the match type is 1 or omitted, MATCH finds the largest value less than or equal to
the lookup value.
 If the match type is 0, MATCH finds the first value exactly equal to the lookup value.
 If the match type is -1, MATCH finds the smallest value greater than or equal to the
lookup value.

=MATCH(B4,'INDEX MATCH Master Emp List'!A2:A38,0)

173) Using INDEX and MATCH Together

Despite Employee ID no longer being the first column, we will use INDEX and MATCH to
retrieve the department based on these IDs. We start by entering the INDEX function
because INDEX returns a value at a specific position within an array. At first write index
(then enter the array from which value that is looked for, here we selected a single
department column then use match for finding row num then we entered the value looked
for and selected the array from which row no. will be selected. With MATCH nested inside
INDEX, we do not specify a column number because our array has only one column. After
confirming the formula, it returns the department for employee ID 1054 as "AT". Dragging
the formula down populates departments for all employee IDs correctly

=INDEX('INDEX MATCH Master Emp List'!D2:D38,MATCH(B4,'INDEX MATCH Master Emp

List'!A2:A38,0))
174) TO creating one formula that you can drag down and across to fill the entire table automatically without any
edits. This is where combining INDEX and MATCH functions becomes very useful
We begin with the INDEX function: =INDEX(. To make this more visual, press the function key (F) to select the
INDEX function from the list.
The first argument for INDEX is the array where we want to search. We select the entire master employee list
from A1 to I38 using Ctrl+Shift+Down and Ctrl+Shift+Right. Then, lock this range with F4 to make it an absolute
reference, so it does not change when copying the formula.
The next argument is the row number. We do not know the row number directly, so we use the MATCH function
to find it. The MATCH function will find the row number of the employee ID in the master list.
We enter MATCH( and for the lookup value, select the employee ID in cell B3. We lock the column with a dollar
sign to $B3 so that when dragging down, the row changes but the column remains fixed.

175) For the lookup array, select the employee IDs in the master list from A1 to A38 and
lock this range with F4. Set the match type to 0 for an exact match. Returning to the INDEX
function, the next argument is the column number. Since we want to retrieve different
columns (first name, last name, department, pay rate), we use another MATCH function to
find the correct column number dynamically. For this MATCH, the lookup value is the
header in row 2 (e.g., "First Name" in cell C2). We lock the row with a dollar sign to C$2 so
that when dragging across columns, the column changes but the row remains fixed. The
lookup array for this MATCH is the headers in the master employee list from A1 to I1,
locked with F4. The match type is again 0 for an exact match. Close all parentheses and
press Enter. The formula in C3 now returns the first name corresponding to the employee
ID in B3.

=INDEX('Master Emp List'!$A$2:$I$38,MATCH($B3,'MasterEmpList'!

$A$2:$A$38,0),MATCH(C$2,'Master Emp List'!$A$1:$I$1,0))

176) We will combine the HLOOKUP and MATCH functions to create a dynamic formula. The
MATCH function takes a lookup value and searches for it within a range, returning the
numeric position of the match. This numeric position will serve as the row index number
for HLOOKUP. Let's construct the formula step-by-step. First, remove the static row index
number (2) from the HLOOKUP formula. Then, insert the MATCH function in its place. The
lookup value for MATCH will be the contents of cell A5, but since A5 contains the word
"Inventory," which is not present in the master list, we will use the LEFT function to extract
the first 11 characters from A5, effectively removing " Inventory". The LEFT function syntax
is: LEFT(text, num_chars). Here, text is cell A5, and num_chars is 11, to extract the
first 11 characters. This gives us the warehouse name without the "Inventory" suffix. Next,
specify the lookup array for the MATCH function. This is the range A2 to A5 on the "Master
Inventory List" sheet. Make this range absolute by pressing F4, which adds dollar signs to
fix the reference when copying the formula. Finally, specify the match type as 0 to find an
exact match. Close the parentheses to complete the MATCH function. This MATCH function
now dynamically returns the correct row index number for HLOOKUP based on the
warehouse name.

=HLOOKUP($B$3,'Master Inventory List'!$A$2:$G$5,MATCH(LEFT($A7,11),'Master

Inventory List'!$A$2:$A$5,0),FALSE)

Chapter 20 Working with Excel's Text Based Functions

177) Using the LEFT() Function
To extract the supplier ID (the first three characters) from the SKU, select the cell where
you want the result, go to the Formulas tab, then Text functions, and choose LEFT(). The
function requires two arguments:
 The text string to extract from (e.g., the SKU cell).
 The number of characters to extract from the left side.
For example, to get the first three characters, specify 3 as the number of characters.
Using the RIGHT() Function
To extract the product code (the last two characters), use the RIGHT() function similarly. It
also takes two arguments:
 The text string to extract from.
 The number of characters to extract from the right side.
For example, specify 2 to get the last two characters of the SKU.
178) Using the MID() Function
To extract the part number (the middle three characters), use the MID() function. This
function requires three arguments:
 The text string to extract from.
 The starting position (counting from the left, starting at 1).
 The number of characters to extract.
For example, if the part number starts at the fourth character and is three characters
specify 4 as the start position and 3 as the number of characters.
The MID() function extracts characters starting from the specified position moving
rightwards. For instance, if the SKU is "ACM11111WW", the MID() function with start
position 4 and length 3 extracts "111" as the part number.
Instead of retyping values based on parts of a string, Excel can automatically extract these
pieces for you, enabling easier sorting, filtering, and reporting.
Try practicing with LEFT(), RIGHT(), and MID() functions to become comfortable with
extracting text based on position in Excel.

179) The LEN function is very simple but powerful. It returns the number of characters in a
cell.

This can be used for validation purposes, such as ensuring phone numbers or Social
Security numbers have the correct number of [Link] sole purpose of the LEN
function is to tell us how many characters are in a cell. write len(cell) then enter

180) There are multiple ways to split such data. One common method is using the "Text to
Columns" feature found on the Data tab. This tool allows you to select a range of data and
split it based on a delimiter, such as a space character. Since every name here has a space
separating the first and last names, Text to Columns could be a good candidate. However,
it is not always effective.
We will explore using the SEARCH function, possibly combined with other functions, to
create a more robust solution that can handle a variety of situations.
Let's start with extracting the first name from each full name. For example, from "Patrick
Marleau" or "Joe Brint Joe Martin," we want to isolate the first name.

181) For example, the formula =LEFT(A2, 7) would extract the first seven characters from
cell A2. For "Patrick," which has seven characters, this works well.

However, copying this formula down results in incorrect outputs for other names. For
example, it returns "Joe foe" or "Brint B" instead of just the first names. This happens
because the number of characters is hardcoded as 7, which does not adapt to different
name [Link] adjusting the character count for each name is tedious and
inefficient. Instead, we can use the SEARCH function to dynamically find the position of the
space character, which separates the first and last names.

The SEARCH function returns the position of a specified character or substring within a text
string. For example, =SEARCH(" ", A2) finds the position of the first space in cell A2.

In the case of "Patrick Marleau," the space is at position 8. This means the first name
occupies the first seven characters.

182) We can combine this with the LEFT function to extract the first name
dynamically: =LEFT(A2, SEARCH(" ", A2)). This formula extracts characters from the left
up to the character before the space.
Copying this formula down the column correctly extracts the first names for all entries,
regardless of their length.
Next, let's extract the last names. This is more involved because last names are on the
right side of the string, and their lengths vary.
We can use the RIGHT function to extract characters from the right side of the string.
However, we need to determine the number of characters dynamically.
The LEN function returns the total number of characters in a cell. For
example, =LEN(A2) gives the length of the full name.
To find the length of the last name, subtract the position of the space from the total
length: =LEN(A2) - SEARCH(" ", A2).
Combining this with the RIGHT function, the formula to extract the last name is:
=RIGHT(A2, LEN(A2) - SEARCH(" ", A2))

183) Using the CONCATENATE Function

There are multiple ways to combine values in Excel, but the CONCATENATE function is a
very common feature for this purpose. Let me demonstrate how simple it is to use. I will
click in the cell right below "Howard Smet," which is cell E4. Then, I go to the Formulas tab,
select Text functions, and find CONCATENATE. The CONCATENATE function requires at
least two arguments, but it can accept up to 255 text values. Essentially, it wants to know
the text you want to combine. For example, I want to combine "Joe" and "Gonzales." For
the first text argument, I select the cell containing "Joe." For the second text argument, I
do not select "Gonzales" yet because I want to insert a space between the two names. To
add a space, I type a quoted space character " ". This combines "Joe" with a space. Then,
for the third text argument, I select the cell containing "Gonzales." This tells the function to
combine the various pieces: "Joe" with a space and then "Gonzales."

184) The FILTER() function takes three arguments:

1. Array: The range of data to filter.
2. Include: The criteria to determine which rows to include.
3. If empty: The value to return if no results match the criteria.

For the Array argument, select the data range on the "customer data" worksheet from all
relevant columns including the country column. Excel will recognize this as the Customer
Data table.
Next, for the Include argument, specify the criteria. For example, to filter by country,
select the country column on the "customer data" worksheet (E2 down to the last row).
select the entire column. Then specify the condition where the country is equal to "France"
by typing = "France" inside the argument box. For the If empty argument, you can enter
a message such as "no records found" to display if the filter returns no results After
entering these arguments, click OK. The filtered results will appear, showing only the
records where the country is France
185) Here is the SORT function. After the equals sign, I will add SORT and open
parentheses. Immediately, a screen tip appears showing what the SORT function expects.
The first argument is the array, which is the collection of data to sort. Our array comes
from the FILTER results, so that part is already done. After typing SORT and opening
parentheses, I press the FX button to open the argument window. Currently, it shows the
FILTER function, which we do not want to change since it is already set. Clicking back into
the formula bar within the SORT function arguments, the array argument is already filled
with the FILTER function. The next argument is the sort index, which specifies the column
to sort on. Since the array is pulled from the FILTER function, we may not know the exact
column letter of the contact name. Microsoft simplifies this by using column numbers
starting from the left: customer ID is 1, company name is 2, and contact name is 3.
Therefore, I will enter 3 as the sort index.
The final argument is the sort order, which indicates ascending or descending order. The
description states that 1 is for ascending order (default), and -1 is for descending order. I
will use ascending order by either leaving it blank or entering 1.

186) To summarize, the three arguments for the SORT function are:

 Array: the data to sort, which is the FILTER function result.

 Sort index: the column number to sort by, which is 3 for contact name.
 Sort order: 1 for ascending order.

After entering these, I click OK.

187) I type =UNIQUE( and then select the range of cells containing the customer names. In
this case, it is from B2 down to B831, which corresponds to the customer column in the
"Order Data" table. I close the parentheses and press Enter.
The result is a unique list of all customer names. Although the original list contains over
800 records, the unique list only extends to row 89, reflecting the distinct customers. This
demonstrates the power of the UNIQUE function.
188) Nesting UNIQUE() with SORT()
One of my favorite features in Excel is the ability to nest functions. To make the unique list
easier to search through, I will sort the results returned by the UNIQUE function.
I return to cell J1 and modify the formula by wrapping the UNIQUE function inside the SORT
function. The formula becomes:
=SORT(UNIQUE(B2:B831), 1, 1)
Here, the first argument to SORT is the array returned by UNIQUE. The second argument,
1, specifies the sort index (the first column), and the third argument, 1, indicates
ascending order.
After pressing Enter, the list of unique customer names is now sorted alphabetically from A
to Z. This demonstrates how nesting functions can enhance data manipulation in Excel.

189) I want to get a filtered result of the Order Data worksheet. Remember that worksheet
with Order ID, Customer, Employee, and so on? I want to pull out a specific customer. We
can do that using the FILTER function. but if I want to change the customer, I have to go to
that formula cell B6, then go to the formula bar and change the customer name there.
While that works and updates the formula, I want a more dynamic and intuitive way to do
[Link], instead of typing the customer name manually in the formula, we are going to
create a drop-down list using data validation that references our unique list of customers.

190) So, instead of typing the customer name manually in the formula, we are going to
create a drop-down list using data validation that references our unique list of customers.
Remember, we created that unique list of customers earlier. We will reference that list for
the drop-down Back in the Dynamic Filter worksheet, I will go to cell C2, then go to the
Data tab and select Data Validation. In the Data Validation dialog, I will change the settings
under Validation Criteria to allow a List. For the source of the list, I will go to the Order Data
worksheet and select the unique list of customers from J1 down to J89. hit OK. Now, back in
the Dynamic Filter worksheet, I have a drop-down in cell C2 with all of my customers. The
formula we created in cell B6 is currently referencing a hard-coded value. I will replace
that. Instead of North/South, I will delete it all, including the quotes, making sure the cursor
is flashing just to the right of the equal sign. I will now select cell C2 and hit Enter.

191) Here, we want to input an employee number, such as employee number one, and have the XLOOKUP
function retrieve the last name, first name, title, extension, and reports to information based on that [Link]
implement XLOOKUP, select cell C4 and enter =XLOOKUP(. To simplify the process, use the FX button to open
the argument window. The first argument is the lookup value, which in this case is the employee number one
located in cell B4. Next, specify the lookup array, which is where the function will search for the lookup value.
For this example, the lookup array is on the Employee Data worksheet, specifically the Employee ID column from
cells A2 to A10. Then, specify the return array, which is the range of cells containing the data you want to retrieve.
For example, to get the last name, select cells B2 to B10 on the Employee Data worksheet. Optionally, you can
specify the if_not_found argument to display a message such as "No Employee Found" if the lookup value is not
present. The match_mode argument specifies how to match the lookup value against the lookup array [Link]
entering these arguments, press OK. The function will return the last name of the employee with ID number one.
For example, if employee number one is Nancy, the function will return Nancy's last name.

192) While XLOOKUP and VLOOKUP produce similar results, their argument structures differ
significantly. VLOOKUP uses a single master list for both lookup and return arrays, whereas
XLOOKUP uses two separate ranges: one for the lookup array and one for the return array.
The match_mode argument controls how the lookup value is matched against the lookup
array. Commonly, zero is used for an exact match. options include:
 1: Exact match or next smaller item
 -1: Exact match or next larger item
Typically, zero is used to ensure an exact match. press Enter to confirm.

193) Inside cell E2, I type =SWITCH( and open the parenthesis. To make this cleaner and more intuitive, I click
the fx button to open the argument window. The first argument is the expression, which is what we are looking
for. In this case, I select cell D2, which contains the payment method value. This expression is the value we want
to evaluate. Next, for Value 1, I specify the first value to compare against the expression. If D2 equals "Credit
Card", then the Result 1 is "Electronic".

For Value 2, if D2 equals "Bank Transfer", the result is also "Electronic".

For Value 3, if D2 equals "Cash", the result is "Other".

For Value 4, if D2 equals "PayPal", the result is "Electronic".

For Value 5, if D2 equals "Check", the result is "Other".

This setup is similar to an IF statement but more concise because the expression is specified once and compared
against multiple values. After entering these value-result pairs, I click OK. The first payment method, "Credit
Card", is categorized as "Electronic". I then fill the formula down the column. The SWITCH function correctly
categorizes "Bank Transfer" as "Electronic", "Cash" as "Other", "PayPal" as "Electronic", and so forth. This
demonstrates the SWITCH function's ability to evaluate an expression and return corresponding results based on
matching values.

194) The SWITCH function in Excel can be used with a TRUE expression to evaluate multiple conditions.

 By using TRUE as the expression, each condition is evaluated as a logical test returning TRUE or
[Link] implement this, I will enter cell F2 and insert the SWITCH function again:

=SWITCH()

The expression argument will differ from previous examples. Instead of typing a cell reference, I will use the
keyword TRUE. This means all subsequent values will be evaluated against the logical TRUE expression.

The structure will be:

 Value 1: If the cell equals zero, return "paid."

 Value 2: If the cell value is less than or equal to 10, return "overdue."
 Value 3: If the cell value is less than or equal to 30, return "seriously overdue."
 Default: For any value above 30, return "critical."

195) Here is the formula constructed step-by-step:

 Start with =SWITCH(TRUE,

 Add condition C2=0, "paid",
 Add condition C2<=10, "overdue",
 Add condition C2<=30, "seriously overdue",
 Add default value "critical")

196) Using TEXTJOIN to Combine Names

Instead of using CONCAT or CONCATENATE, Microsoft introduced TEXTJOIN, which
streamlines the process of combining multiple text values into one string more efficiently.
To use TEXTJOIN, select the cell where you want the combined text to appear. For
example, in cell D2, enter the formula =TEXTJOIN( and press Tab to open the function.
The first argument TEXTJOIN requires is the delimiter, which specifies how to separate the
combined values. Since we want to combine first and last names with a space in between,
we enter a space enclosed in quotes: " ". Next, specify whether to ignore empty cells.
Setting this argument to TRUE instructs TEXTJOIN to skip any empty cells during
concatenation.
Finally, list the text values to combine. For example, select cells B2 (first name) and C2
(last name). Close the parentheses and press Enter to complete The result will be the full
name, such as "Alex Johnson". You can then autofill this formula down for all applicants.
197) Constructing the TEXTJOIN Formula
I begin by typing =TEXTJOIN(. The first argument is the delimiter, which specifies how to
separate the joined entries. I use a comma followed by a space, so I enter ", ". This means
each skill will be separated by a comma and a space.
Next, I set the second argument to TRUE to ignore any empty cells. Then, for the third
argument, I nest the FILTER function to select the skills for the specific applicant.
The FILTER function's array argument is the range of skills, which is from E2 to E49. The
include argument specifies which skills to include. I set this to the range D2 to D49 equal to
the value in H2, which is the applicant's name.
The complete formula looks like this:
=TEXTJOIN(", ", TRUE, FILTER(E2:E49, D2:D49=H2))
198) Using the TEXTSPLIT Function
To begin, select cell C2 and enter the formula:
=TEXTSPLIT(
The first argument is the text to split. For this, select cell A2.
The second argument is the column delimiter, which tells Excel how to split the text across
columns.
In this case, enter a comma followed by a space inside quotes: ", ".
This specifies that the text should be split wherever a comma and a space appear.
Close the parentheses and press Enter. The result will be the four values—name, email,
phone number, and position—each separated into its own column.

Chapter 21 Auditing an Excel Worksheet

199) The idea is that after selecting a cell containing a formula, you want to know which
cells contribute to that formula. Clicking the Trace Precedents button draws arrows from
the cells that the formula relies on. For example, you can see that the total and the
monthly goal are included.
If you click on the total cell, such as the one showing 176,211, and then click Trace
Precedents again, Excel draws arrows to the individual cells that make up this total. In this
case, it draws a thick arrow through five cells that contribute to the total.

200) To trace the dependents of cell C6, I select the cell, then go back to the Formulas tab, Formula Auditing
group, and click the Trace Dependents button. This action draws arrows from cell C6 to the cells that depend on it.

The arrows show that the total for week two, the row total for K, and the "No Bonus" cell all rely on the value in
cell C6. This tracing clearly illustrates which cells depend on [Link], these cells are dependent on the monthly
goal cell. With a simple button press, you can trace dependents just as easily as tracing precedents.

To remove the arrows, simply go back to the Formulas tab and click Remove Arrows. This will clear all the
arrows from the worksheet.

201) Using the Watch Window

The Watch Window allows you to open a separate window where you can mark specific
cells to watch as you work in other worksheets. For example, you can select cell B2 on the
"Watch Window" worksheet, which currently holds the value $176,311, and add it to the
Watch Window.
To do this, select the cell you want to watch, then click the Watch Window icon, which
looks like a small box with a pair of glasses. This opens the Watch Window. If it is empty,
you can add a watch by confirming the selected cell and clicking "Add." The Watch Window
will then display the workbook name, worksheet name, cell reference, current value, and
the formula that defines the cell's value.
The Watch Window remains open as you work. For example, if you switch to the "Formula
Auditing" worksheet and change the value from 7,000 back to 6,900, the Watch Window
updates immediately to reflect the new value without needing to switch worksheets.
This feature eliminates the need to manually jump back and forth between worksheets to
monitor important cells, making it easier to audit and manage complex Excel workbooks.
202) Using Show Formulas for Efficient Auditing
Here is a quick way to assist in managing and auditing formulas visually. On the Formula tab at the top of your
screen, under the Formula Auditing group, there is a button called Show Formulas. This button is super quick
and simple to use. By clicking this button, all formulas in the worksheet expand and become visible. This allows
you to see all formulas at a glance as you scroll through the worksheet. You can easily spot larger formulas, such
as IF statements, and review them efficiently.
The Show Formulas button acts as a toggle. Clicking it again will collapse the formulas, hiding them and
returning the worksheet to its normal view. This makes it easy to switch between viewing formulas and viewing
the results.

Printing Worksheets with Formulas Displayed

An additional benefit of the Show Formulas feature is that you can print the worksheet while formulas are
displayed. To do this, after enabling Show Formulas, go to the File tab, select Print, and the printout will show
all formulas instead of the calculated results.

Chapter 22 Protecting Excel Worksheets and Workbooks

203) By default, every single cell in an Excel worksheet, from A1 down to wherever the
sheet ends, has a property called "Locked" turned on. This might sound odd because you
can type inside any cell by default. The reason is that although the "Locked" property is
enabled, the worksheet is not yet protected. The lock only takes effect when the sheet
protection is enabled.
To visualize this, imagine a chain-link fence with a gate that has a padlock on it. The
padlock is present on every cell, but it is not snapped shut yet. If you protect the sheet
now, the padlock snaps shut on every cell, preventing any changes. However, since the
sheet is not protected, the padlock is effectively open, allowing edits.
204) Selecting Cells to Unlock
Since I only want people to change the weekly data, I need to remove the lock from those
specific cells before protecting the sheet. First, select the cells you want people to be able
to change. For example, select cells B5 through B9. Next, go to the Home tab, and in the
Font section, click the small arrow in the bottom right corner to open the Format Cells
window. Navigate to the Protection tab and uncheck the "Locked" property. Then click OK.
Now, the selected cells from B5 to B9 are no longer locked, effectively removing the
padlock from those cells.
205) Protecting the Worksheet
After unlocking the desired cells, go to the Review tab and click on "Protect Sheet." This
opens the Protect Sheet window where you can enter an optional password. It is
recommended to use a password to prevent others from unprotecting the sheet without
authorization. Below the password field, there are several checkboxes that control what
users can do once the sheet is protected. If a box is checked, users can perform that
action; if unchecked, they cannot. For example, users will not be able to insert or delete
columns or rows if those options are unchecked. After setting your preferences, click OK
and confirm your password. Remember not to lose this password, as it cannot be retrieved
if forgotten. Now the worksheet is protected. You can see the option "Unprotect Sheet" at
the top. If you try to change a locked cell, such as a formula, Excel will display a message
saying "The cell or chart you are trying to change is protected."

206) Let's explore the "Protect Workbook" feature, which is quite straightforward.
Navigate to the Review tab. In the Changes section, just to the right of "Protect Sheet," you will find "Protect
Workbook." Click on it. In Excel 2013 and 2016, you have a single option called "Structure." In Excel 2010 or
earlier, there is also a "Windows" option, which is less commonly used. Since I am using Excel 2013, the
"Windows" option is not available. Keep the "Structure" option enabled. For demonstration, I will enter
"password" as the password and click OK. You will be asked to confirm the password. Remember, if you lose or
forget this password, it cannot be recovered. If you want to reverse this protection to add new worksheets, rename
existing ones, or modify formulas, simply go back to the Review tab and click "Unprotect Workbook." You will
need to enter the password to proceed. Once unprotected, you regain full control over the workbook structure and
can perform all standard operations.

207) Setting a Workbook Password

To add a password, open your Excel document. It can be a new document or an existing one. Then, go to the File
tab located at the top left corner of the window. In the Info section, select "Protect Workbook" and click on
"Encrypt with Password." You will be prompted to enter a password. For demonstration, the password "password"
is used here. Make sure not to lose this password because if you do, Excel cannot recover it for you. After entering
the password, confirm it by typing it again to ensure [Link] the password is set, save the workbook. Then
close the file by going to File and selecting Close. When you reopen the workbook, Excel will prompt you to enter
the password before allowing access.
Opening a Password-Protected Workbook
Upon reopening the file, you will be asked to enter the password. Once the correct password is entered, you will
have full access to the workbook.
Removing the Workbook Password
If you want to remove the password, go back to File > Info > Protect Workbook > Encrypt with Password. Delete
the existing password from the field and save the document. The workbook will no longer require a password to
open.