List of Tables
1 Chapter 5TABLE 5-1 Common Date and Time Format Codes
List of Illustrations
1 Chapter 1FIGURE 1-1: Reports present data for viewing but don’t lead readers to conclusi...FIGURE 1-2: Dashboards provide at-a-glance views into key measures relevant to ...FIGURE 1-3: Each box in this dashboard layout mockup represents a component and...FIGURE 1-4: Studies show that users pay particular attention to the upper left ...
2 Chapter 2FIGURE 2-1: Avoid hard-coded tables that fuse data, analysis, and presentation.FIGURE 2-2: An effective data model separates data, analysis, and presentation.FIGURE 2-3: A spreadsheet report.FIGURE 2-4: A flat data file.FIGURE 2-5: A tabular data set.FIGURE 2-6: A model map allows you to document how each range interacts with yo...FIGURE 2-7: In this example, the VLOOKUP function helps to look up the appropri...FIGURE 2-8: Using the VLOOKUP function to extract and shape data.FIGURE 2-9: You can use data validation to create a predefined list of valid va...FIGURE 2-10: HLOOKUP formulas help to find March and June numbers from the look...FIGURE 2-11: In this example, HLOOKUP formulas pull and reshape data without di...FIGURE 2-12: Without the SUMPRODUCT function, getting the total sales involves ...FIGURE 2-13: The SUMPRODUCT function allows you to perform the same analysis wi...FIGURE 2-14: The SUMPRODUCT function can be used to filter data based on criter...FIGURE 2-15: The SUMPRODUCT function can be used to pull summarized numbers fro...FIGURE 2-16: The CHOOSE function allows you to find values from a defined set o...FIGURE 2-17: The CHOOSE formulas ensure that the appropriate data is synchronou...FIGURE 2-18: The date in both the table and chart ends in June.FIGURE 2-19: Converting a range of data to an Excel table.FIGURE 2-20: Excel tables can be used as the source for charts, pivot tables, n...FIGURE 2-21: Excel tables automatically expand when new data is added.FIGURE 2-22: To remove Excel table functionality, convert the table back to a r...FIGURE 2-23: A simple formula that references a range.FIGURE 2-24: Excel automatically spills the results into the surrounding cells.FIGURE 2-25: Dynamic arrays work with any traditional Excel function that accep...FIGURE 2-26: Spill ranges will visually show a line around them.FIGURE 2-27: A spill error caused by an obstruction in the spill range.FIGURE 2-28: Referencing a single cell in a spill range won't allow you to capt...FIGURE 2-29: Using the spill range operator to apply a function to the entire s...FIGURE 2-30: Using the spill range operator to count all values in the referenc...FIGURE 2-31: Using the SORT function to sort students by the change in test sco...FIGURE 2-32: Using the SORT function to sort by columns.FIGURE 2-33: Using the SORTBY function to apply a multi-column sort.FIGURE 2-34: Adding the Exactly_once argument extracts only values that appear ...FIGURE 2-35: Filtering records where the Change value is greater than 10.FIGURE 2-36: Getting the FILTER criteria from cell F1.FIGURE 2-37: Combining SORT with FILTER to sort results.FIGURE 2-38: Using multiple filter conditions.FIGURE 2-39: Criteria evaluation behind the scenes.FIGURE 2-40: Using the + operator to return results if the first condition is T...FIGURE 2-41: A basic XLOOKUP function to find Customer Type based on revenue.FIGURE 2-42: Enter a comma to see choices for the next argument.FIGURE 2-43: XLOOKUP results using approximate matching.FIGURE 2-44: Using wildcard characters to perform complex searches.
3 Chapter 3FIGURE 3-1: The values area of a pivot table calculates and counts data.FIGURE 3-2: The row area of a pivot table gives you a row-oriented perspective.FIGURE 3-3: The column area of a pivot table gives you a column-oriented perspe...FIGURE 3-4: The filter area allows you to easily apply filters to a pivot table...FIGURE 3-5: Start a pivot table via the Insert tab.FIGURE 3-6: The Create PivotTable dialog box.FIGURE 3-7: The PivotTable Fields task pane.FIGURE 3-8: Select the Market check box.FIGURE 3-9: Add the Sales Amount field by selecting its check box.FIGURE 3-10: Adding a layer of analysis is as easy as bringing in another field...FIGURE 3-11: Your business segments are now column-oriented.FIGURE 3-12: Using pivot tables to analyze regions.FIGURE 3-13: Refreshing your pivot table captures changes made to your data.FIGURE 3-14: Changing the range that feeds the pivot table.FIGURE 3-15: Select the new range that feeds the pivot table.FIGURE 3-16: The three layouts for a pivot table report.FIGURE 3-17: Changing the layout of the pivot table.FIGURE 3-18: Right-click any value in the target field to select the Value Fiel...FIGURE 3-19: Use the Custom Name input box to change the name of the field.FIGURE 3-20: Changing the type of summary calculation used in a field.FIGURE 3-21: Subtotals sometimes muddle the data you’re trying to show.FIGURE 3-22: Use the Do Not Show Subtotals option to remove all subtotals at on...FIGURE 3-23: The report shown in Figure 3-21, without subtotals.FIGURE 3-24: Choose the None option to remove subtotals for one field.FIGURE 3-25: To remove Bikes from this analysis …FIGURE 3-26: … deselect the Bikes check box.FIGURE 3-27: The analysis from Figure 3-25, without the Bikes segment.FIGURE 3-28: Clicking the Select All check box forces all data items in that fi...FIGURE 3-29: All sales periods are showing.FIGURE 3-30: Filtering for the Europe region causes some sales periods to disap...FIGURE 3-31: Click the Show Items with No Data option to force Excel to display...FIGURE 3-32: All sales periods are now displayed, even if there is no data to b...FIGURE 3-33: Applying a sort to a pivot table field.FIGURE 3-34: Build this pivot table to start.FIGURE 3-35: Select the Top 10 filter option.FIGURE 3-36: Specify the filter you want to apply.FIGURE 3-37: Filter your pivot table report to show Accessories.FIGURE 3-38: The final report.FIGURE 3-39: You can easily adapt this report to produce any combination of vie...FIGURE 3-40: Select Clear Filters to clear the applied filters in a field.FIGURE 3-41: Adding date fields will automatically group data by Years and Quar...FIGURE 3-42: Click the plus and minus icons to expand or collapse time periods.FIGURE 3-43: Ungrouping a date field to show individual dates.FIGURE 3-44: The ungrouped Order Date field.FIGURE 3-45: This view shows percent of total for the row.FIGURE 3-46: Showing the percent of total for the column.FIGURE 3-47: This view shows a running total of sales for each month.FIGURE 3-48: Applying a running total based on the Order Date field.FIGURE 3-49: Build a pivot table that contains the Sum of Sales Amount twice.FIGURE 3-50: Comparing each Order Date month with the previous item.FIGURE 3-51: The second Sum of Sales Amount field now displays the month-over-m...
4 Chapter 4FIGURE 4-1: Starting a Power Query web query.FIGURE 4-2: Enter the target URL containing the data you need.FIGURE 4-3: Select the correct data source and then click the Transform Data bu...FIGURE 4-4: The Query Editor window allows you to shape, clean, and transform d...FIGURE 4-5: Change the data type of the High, Low, and Close fields to currency...FIGURE 4-6: Select unneeded columns, and then select Remove Other Columns to ge...FIGURE 4-7: Removing errors caused by text values that couldn’t be converted to...FIGURE 4-8: You can use the Power Query Editor to apply transformation actions ...FIGURE 4-9: The Load To dialog box gives you more control over how the results ...FIGURE 4-10: Your final query pulled from the Internet: transformed, put into a...FIGURE 4-11: Query steps can be viewed and managed in the Applied Steps section...FIGURE 4-12: Right-click any query step to edit, rename, delete, or move the st...FIGURE 4-13: Select the data sources you want to work with, and then click the ...FIGURE 4-14: Preview the data and use the option drop-down menus to tell Power ...FIGURE 4-15: Select the view you want imported, and then click the Load button.FIGURE 4-16: The final imported database data.FIGURE 4-17: Edit a data source by selecting it and clicking the Edit Permissio...FIGURE 4-18: Click the Edit button in the Edit Permissions dialog box to change...
5 Chapter 5FIGURE 5-1: A poorly designed table.FIGURE 5-2: Remove unnecessary cell coloring.FIGURE 5-3: Use the No Fill option to clear cell colors.FIGURE 5-4: Minimize the use of borders and use the