| Introduction |
|
7 | (1) |
|
|
|
2 | (1) |
|
|
|
3 | (1) |
|
|
|
3 | (1) |
|
|
|
4 | (1) |
|
|
|
5 | (2) |
|
Part I Getting Started with Excel Dashboards and Reports |
|
|
7 | (40) |
|
Chapter 1 Getting in the Dashboard State of Mind |
|
|
9 | (12) |
|
Defining Dashboards and Reports |
|
|
9 | (3) |
|
|
|
10 | (1) |
|
|
|
11 | (1) |
|
|
|
12 | (4) |
|
Establish the audience and purpose for the dashboard |
|
|
12 | (1) |
|
Delineate the measures for the dashboard |
|
|
13 | (1) |
|
Catalog the required data sources |
|
|
14 | (1) |
|
Define the dimensions and filters for the dashboard |
|
|
15 | (1) |
|
Determine the need for drill-down features |
|
|
15 | (1) |
|
Establish the refresh schedule |
|
|
16 | (1) |
|
A Quick Look at Dashboard Design Principles |
|
|
16 | (5) |
|
Rule number 1 Keep it simple |
|
|
17 | (1) |
|
Use layout and placement to draw focus |
|
|
18 | (1) |
|
Format numbers effectively |
|
|
19 | (1) |
|
Use titles and labels effectively |
|
|
20 | (1) |
|
Chapter 2 Building a Super Model |
|
|
21 | (26) |
|
Data Modeling Best Practices |
|
|
22 | (10) |
|
Separating data, analysis, and presentation |
|
|
22 | (3) |
|
Starting with appropriately structured data |
|
|
25 | (3) |
|
Avoiding turning your data model into a database |
|
|
28 | (1) |
|
Using tabs to document and organize your data model |
|
|
29 | (2) |
|
Testing your data model before building reporting components on top of it |
|
|
31 | (1) |
|
Excel Functions That Really Deliver |
|
|
32 | (11) |
|
|
|
32 | (4) |
|
|
|
36 | (1) |
|
|
|
37 | (4) |
|
|
|
41 | (2) |
|
Using Smart Tables That Expand with Data |
|
|
43 | (4) |
|
Converting a range to an Excel table |
|
|
44 | (2) |
|
Converting an Excel table back to a range |
|
|
46 | (1) |
|
Part II Building Basic Dashboard Components |
|
|
47 | (98) |
|
Chapter 3 Dressing Up Your Data Tables |
|
|
49 | (16) |
|
|
|
49 | (8) |
|
|
|
50 | (2) |
|
|
|
52 | (2) |
|
Use effective number formatting |
|
|
54 | (1) |
|
Subdue your labels and headers |
|
|
55 | (2) |
|
Getting Fancy with Custom Number Formatting |
|
|
57 | (8) |
|
|
|
57 | (2) |
|
Formatting numbers in thousands and millions |
|
|
59 | (3) |
|
Hiding and suppressing zeroes |
|
|
62 | (1) |
|
Applying custom format colors |
|
|
62 | (1) |
|
Formatting dates and times |
|
|
63 | (2) |
|
Chapter 4 Sparking Inspiration with Sparklines |
|
|
65 | (14) |
|
|
|
65 | (2) |
|
|
|
67 | (4) |
|
|
|
68 | (2) |
|
Understanding sparkline groups |
|
|
70 | (1) |
|
|
|
71 | (8) |
|
Sizing and merging sparkline cells |
|
|
71 | (1) |
|
Handling hidden or missing data |
|
|
72 | (1) |
|
Changing the sparkline type |
|
|
73 | (1) |
|
Changing sparkline colors and line width |
|
|
73 | (1) |
|
Using color to emphasize key data points |
|
|
73 | (1) |
|
Adjusting sparkline axis scaling |
|
|
74 | (1) |
|
|
|
75 | (2) |
|
|
|
77 | (1) |
|
Autoupdating sparkline ranges |
|
|
78 | (1) |
|
Chapter 5 Formatting Your Way to Visualizations |
|
|
79 | (28) |
|
Enhancing Reports with Conditional Formatting |
|
|
79 | (19) |
|
Applying basic conditional formatting |
|
|
80 | (8) |
|
Adding your own formatting rules manually |
|
|
88 | (3) |
|
|
|
91 | (3) |
|
Show Data Bars and icons outside of cells |
|
|
94 | (2) |
|
Representing trends with Icon Sets |
|
|
96 | (2) |
|
Using Symbols to Enhance Reporting |
|
|
98 | (4) |
|
|
|
102 | (5) |
|
|
|
102 | (1) |
|
|
|
103 | (2) |
|
Enhancing a dashboard with the Camera tool |
|
|
105 | (2) |
|
Chapter 6 The Pivotal Pivot Table |
|
|
107 | (38) |
|
An Introduction to the Pivot Table |
|
|
107 | (1) |
|
The Four Areas of a Pivot Table |
|
|
108 | (3) |
|
|
|
108 | (1) |
|
|
|
109 | (1) |
|
|
|
109 | (1) |
|
|
|
110 | (1) |
|
Creating Your First Pivot Table |
|
|
111 | (8) |
|
Changing and rearranging your pivot table |
|
|
114 | (1) |
|
|
|
115 | (1) |
|
Keeping your pivot table fresh |
|
|
116 | (3) |
|
Customizing Your Pivot Table Reports |
|
|
119 | (14) |
|
Changing the pivot table layout |
|
|
119 | (1) |
|
|
|
120 | (2) |
|
Applying numeric formats to data fields |
|
|
122 | (1) |
|
Changing summary calculations |
|
|
122 | (2) |
|
|
|
124 | (3) |
|
Showing and hiding data items |
|
|
127 | (1) |
|
Hiding or showing items without data |
|
|
128 | (4) |
|
|
|
132 | (1) |
|
Creating Useful Pivot-Driven Views |
|
|
133 | (12) |
|
Producing top and bottom views |
|
|
133 | (4) |
|
Creating views by month, quarter, and year |
|
|
137 | (2) |
|
Creating a percent distribution view |
|
|
139 | (2) |
|
Creating a YTD totals view |
|
|
141 | (1) |
|
Creating a month-over-month variance view |
|
|
142 | (3) |
|
Part III Building Advanced Dashboard Components |
|
|
145 | (62) |
|
Chapter 7 Charts That Show Trending |
|
|
147 | (26) |
|
|
|
147 | (9) |
|
Using chart types appropriate for trending |
|
|
148 | (2) |
|
Starting the vertical scale at zero |
|
|
150 | (1) |
|
Leveraging Excel's logarithmic scale |
|
|
151 | (2) |
|
Applying creative label management |
|
|
153 | (3) |
|
|
|
156 | (7) |
|
Creating side-by-side time comparisons |
|
|
156 | (2) |
|
Creating stacked time comparisons |
|
|
158 | (2) |
|
Trending with a secondary axis |
|
|
160 | (3) |
|
Emphasizing Periods of Time |
|
|
163 | (4) |
|
Formatting specific periods |
|
|
163 | (2) |
|
Using dividers to mark significant events |
|
|
165 | (1) |
|
Representing forecasts in your trending components |
|
|
166 | (1) |
|
Other Trending Techniques |
|
|
167 | (6) |
|
Avoiding overload with directional trending |
|
|
167 | (1) |
|
|
|
168 | (5) |
|
Chapter 8 Grouping and Bucketing Data |
|
|
173 | (18) |
|
Creating Top and Bottom Displays |
|
|
173 | (5) |
|
Incorporating top and bottom displays into dashboards |
|
|
174 | (1) |
|
Using pivot tables to get top and bottom views |
|
|
175 | (3) |
|
Using Histograms to Track Relationships and Frequency |
|
|
178 | (9) |
|
Adding formulas to group data |
|
|
179 | (4) |
|
Adding a cumulative percent |
|
|
183 | (2) |
|
Using a pivot table to create a histogram |
|
|
185 | (2) |
|
Emphasizing Top Values in Charts |
|
|
187 | (4) |
|
Chapter 9 Displaying Performance against a Target |
|
|
191 | (16) |
|
Showing Performance with Variances |
|
|
191 | (2) |
|
Showing Performance against Organizational Trends |
|
|
193 | (1) |
|
Using a Thermometer-Style Chart |
|
|
194 | (1) |
|
|
|
195 | (8) |
|
|
|
196 | (4) |
|
Adding data to your bullet graph |
|
|
200 | (1) |
|
Final thoughts on formatting bullet graphs |
|
|
200 | (3) |
|
Showing Performance against a Target Range |
|
|
203 | (4) |
|
Part IV Advanced Reporting Techniques |
|
|
207 | (54) |
|
Chapter 10 Macro-Charged Dashboarding |
|
|
209 | (14) |
|
|
|
209 | (1) |
|
Recording Your First Macro |
|
|
210 | (4) |
|
|
|
214 | (3) |
|
Enabling and Trusting Macros |
|
|
217 | (2) |
|
Macro-enabled file extensions |
|
|
217 | (1) |
|
|
|
217 | (1) |
|
Setting up trusted locations |
|
|
218 | (1) |
|
|
|
219 | (4) |
|
Building navigation buttons |
|
|
219 | (1) |
|
Dynamically rearranging pivot table data |
|
|
220 | (1) |
|
Offering one-touch reporting options |
|
|
221 | (2) |
|
Chapter 11 Giving Users an Interactive Interface |
|
|
223 | (22) |
|
Getting Started with Form Controls |
|
|
223 | (4) |
|
|
|
224 | (2) |
|
Adding a control to a worksheet |
|
|
226 | (1) |
|
|
|
227 | (1) |
|
Using the Check Box Control |
|
|
228 | (4) |
|
Check box example: Toggling a chart series on and off |
|
|
229 | (3) |
|
Using the Option Button Control |
|
|
232 | (1) |
|
Option Button Example: Showing Many Views through One Chart |
|
|
233 | (3) |
|
Using the Combo Box Control |
|
|
236 | (1) |
|
Combo Box Example: Changing Chart Data with a Drop-Down Selector |
|
|
237 | (2) |
|
Using the List Box Control |
|
|
239 | (2) |
|
List Box Example: Controlling Multiple Charts with One Selector |
|
|
241 | (4) |
|
Chapter 12 Adding Interactivity with Pivot Slicers |
|
|
245 | (16) |
|
|
|
245 | (2) |
|
Creating a Standard Slicer |
|
|
247 | (3) |
|
|
|
250 | (3) |
|
|
|
250 | (1) |
|
|
|
250 | (1) |
|
|
|
251 | (1) |
|
|
|
252 | (1) |
|
Controlling Multiple Pivot Tables with One Slicer |
|
|
253 | (1) |
|
Creating a Timeline Slicer |
|
|
254 | (2) |
|
Using Slicers as Form Controls |
|
|
256 | (5) |
|
Part V Working With the Outside World |
|
|
261 | (34) |
|
Chapter 13 Using External Data for Your Dashboards and Reports |
|
|
263 | (12) |
|
Importing Data from Microsoft Access |
|
|
263 | (8) |
|
|
|
264 | (1) |
|
The Microsoft Access Export wizard |
|
|
265 | (1) |
|
The Get External Data icon |
|
|
266 | (5) |
|
Importing Data from SQL Server |
|
|
271 | (4) |
|
Chapter 14 Sharing Your Workbook with the Outside World |
|
|
275 | (20) |
|
Protecting Your Dashboards and Reports |
|
|
275 | (9) |
|
Securing access to the entire workbook |
|
|
275 | (4) |
|
Limiting access to specific worksheet ranges |
|
|
279 | (4) |
|
Protecting the workbook structure |
|
|
283 | (1) |
|
Linking Your Excel Dashboards to PowerPoint |
|
|
284 | (5) |
|
Creating a link between Excel and PowerPoint |
|
|
284 | (2) |
|
Manually updating links to capture updates |
|
|
286 | (2) |
|
Automatically updating links |
|
|
288 | (1) |
|
Distributing Your Dashboards via a PDF |
|
|
289 | (2) |
|
Distributing Your Dashboards to SkyDrive |
|
|
291 | (3) |
|
Limitations when Publishing to the Web |
|
|
294 | (1) |
|
|
|
295 | (20) |
|
Chapter 15 Ten Chart Design Principles |
|
|
297 | (12) |
|
|
|
297 | (2) |
|
Skip the Unnecessary Chart Junk |
|
|
299 | (2) |
|
Format Large Numbers Where Possible |
|
|
301 | (1) |
|
Use Data Tables instead of Data Labels |
|
|
302 | (2) |
|
Make Effective Use of Chart Titles |
|
|
304 | (1) |
|
Sort Your Data before Charting |
|
|
304 | (1) |
|
Limit the Use of Pie Charts |
|
|
305 | (1) |
|
Don't Be Afraid to Parse Data into Separate Charts |
|
|
306 | (1) |
|
Maintain Appropriate Aspect Ratios |
|
|
307 | (1) |
|
Don't Be Afraid to Use Something Other Than a Chart |
|
|
308 | (1) |
|
Chapter 16 Ten Questions to Ask Before Distributing Your Dashboard |
|
|
309 | (6) |
|
Does My Dashboard Present the Right Information? |
|
|
309 | (1) |
|
Does Everything on My Dashboard Have a Purpose? |
|
|
309 | (1) |
|
Does My Dashboard Prominently Display the Key Message? |
|
|
310 | (1) |
|
Can I Maintain This Dashboard? |
|
|
310 | (1) |
|
Does My Dashboard Clearly Display Its Scope and Shelf Life? |
|
|
311 | (1) |
|
Is My Dashboard Well Documented? |
|
|
311 | (1) |
|
Is My Dashboard Overwhelmed with Formatting and Graphics? |
|
|
312 | (1) |
|
Does My Dashboard Overuse Charts When Tables Will Do? |
|
|
312 | (1) |
|
Is My Dashboard User-Friendly? |
|
|
313 | (1) |
|
Is My Dashboard Accurate? |
|
|
314 | (1) |
| Index |
|
315 | |
Lisainfo e-raamatute kohta