Excel Dashboards & Reports for Dummies E-Book

Michael Alexander

• Herausgeber: John Wiley & Sons
• Kategorie: Wissenschaft und neue Technologien
• Sprache: Englisch
• Veröffentlichungsjahr: 2015

Seitenzahl: 361

Beschreibung

Make the most of your data using the power of Excel When you think of data, do you think of endless rows and columns in spreadsheets? Excel Dashboards and Reports For Dummies, 3rd Edition shows you how to make the most of your data--and puts an end to mind-numbing spreadsheets by exploring new ways to conceptualize and present key information. There's often a gap between handling data and synthesizing it into meaningful reports, and this approachable text bridges this gap with quick and accessible information that answers key questions, like how to meaningfully capture data trends, how to show relationships in data, and when it's better to show variances than actual data values. As a leading spreadsheet application, Microsoft Excel is the go-to data software. This tool allows you to use dashboard reports that leverage gauges, maps, charts, sliders, and other visual elements to present complex data in a manner that's easy to understand. Using Excel dashboards effectively can improve your professional capabilities by leaps and bounds. * Analyze and report on large amounts of data in a meaningful way * Look at data from different perspectives, and better visualize the information you're presenting by quickly slicing data on the fly * Automate redundant reporting and analysis functions, making your data analysis and reporting routine more efficient * Create visualizations, dashboards, and what-if analyses that are as visually appealing as they are substantial Excel Dashboards and Reports For Dummies, 3rd Edition is a fantastic resource if you're looking to spice up your reporting!

Leseprobe

Excel® Dashboards & Reports For Dummies®, 3rd Edition

Published by: John Wiley & Sons, Inc., 111 River Street, Hoboken, NJ 07030-5774, www.wiley.com

Copyright © 2016 by John Wiley & Sons, Inc., Hoboken, New Jersey

Published simultaneously in Canada

No part of this publication may be reproduced, stored in a retrieval system or transmitted in any form or by any means, electronic, mechanical, photocopying, recording, scanning or otherwise, except as permitted under Sections 107 or 108 of the 1976 United States Copyright Act, without the prior written permission of the Publisher. Requests to the Publisher for permission should be addressed to the Permissions Department, John Wiley & Sons, Inc., 111 River Street, Hoboken, NJ 07030, [phone], fax [phone], or online at http://www.wiley.com/go/permissions.

Trademarks: Wiley, For Dummies, the Dummies Man logo, Dummies.com, Making Everything Easier, and related trade dress are trademarks or registered trademarks of John Wiley & Sons, Inc. and may not be used without written permission. Excel is a registered trademark of Microsoft Corporation. All other trademarks are the property of their respective owners. John Wiley & Sons, Inc. is not associated with any product or vendor mentioned in this book.

LIMIT OF LIABILITY/DISCLAIMER OF WARRANTY: THE PUBLISHER AND THE AUTHOR MAKE NO REPRESENTATIONS OR WARRANTIES WITH RESPECT TO THE ACCURACY OR COMPLETENESS OF THE CONTENTS OF THIS WORK AND SPECIFICALLY DISCLAIM ALL WARRANTIES, INCLUDING WITHOUT LIMITATION WARRANTIES OF FITNESS FOR A PARTICULAR PURPOSE. NO WARRANTY MAY BE CREATED OR EXTENDED BY SALES OR PROMOTIONAL MATERIALS. THE ADVICE AND STRATEGIES CONTAINED HEREIN MAY NOT BE SUITABLE FOR EVERY SITUATION. THIS WORK IS SOLD WITH THE UNDERSTANDING THAT THE PUBLISHER IS NOT ENGAGED IN RENDERING LEGAL, ACCOUNTING, OR OTHER PROFESSIONAL SERVICES. IF PROFESSIONAL ASSISTANCE IS REQUIRED, THE SERVICES OF A COMPETENT PROFESSIONAL PERSON SHOULD BE SOUGHT. NEITHER THE PUBLISHER NOR THE AUTHOR SHALL BE LIABLE FOR DAMAGES ARISING HEREFROM. THE FACT THAT AN ORGANIZATION OR WEBSITE IS REFERRED TO IN THIS WORK AS A CITATION AND/OR A POTENTIAL SOURCE OF FURTHER INFORMATION DOES NOT MEAN THAT THE AUTHOR OR THE PUBLISHER ENDORSES THE INFORMATION THE ORGANIZATION OR WEBSITE MAY PROVIDE OR RECOMMENDATIONS IT MAY MAKE. FURTHER, READERS SHOULD BE AWARE THAT INTERNET WEBSITES LISTED IN THIS WORK MAY HAVE CHANGED OR DISAPPEARED BETWEEN WHEN THIS WORK WAS WRITTEN AND WHEN IT IS READ.

For general information on our other products and services, please contact our Customer Care Department within the U.S. at 877-762-2974, outside the U.S. at 317-572-3993, or fax [phone]. For technical support, please visit www.wiley.com/techsupport.

Wiley publishes in a variety of print and electronic formats and by print-on-demand. Some material included with standard print versions of this book may not be included in e-books or in print-on-demand. If this book refers to media such as a CD or DVD that is not included in the version you purchased, you may download this material at http://booksupport.wiley.com. For more information about Wiley products, visit www.wiley.com.

Library of Congress Control Number: 2015958876

ISBN: 978-1-119-07676-6; 978-1-119-08881-3 (ebk); 978-1-119-07677-3 (ebk)

Excel® Dashboards & Reports For Dummies®

Visit www.dummies.com/cheatsheet/exceldashboardsreports to view this book's cheat sheet.

Table of Contents

Cover

Introduction

About This Book

Foolish Assumptions

How This Book Is Organized

Icons Used In This Book

Beyond the Book

Where to Go from Here

Part I: Getting Started with Excel Dashboards & Reports

Chapter 1: Getting In the Dashboard State of Mind

Defining Dashboards and Reports

Preparing for Greatness

A Quick Look at Dashboard Design Principles

Key Questions to Ask Before Distributing Your Dashboard

Chapter 2: Building a Super Model

Data Modeling Best Practices

Excel Functions That Really Deliver

Using Smart Tables That Expand with Data

Part II: Building Basic Dashboard Components

Chapter 3: Dressing Up Your Data Tables

Table Design Principles

Getting Fancy with Custom Number Formatting

Chapter 4: Sparking Inspiration with Sparklines

Introducing Sparklines

Understanding Sparklines

Customizing Sparklines

Chapter 5: Formatting Your Way to Visualizations

Enhancing Reports with Conditional Formatting

Using Symbols to Enhance Reporting

Wielding the Magical Camera Tool

Making Waffles with Conditional Formatting and the Camera Tool

Chapter 6: The Pivotal Pivot Table

An Introduction to the Pivot Table

The Four Areas of a Pivot Table

Creating Your First Pivot Table

Customizing Pivot Table Reports

Creating Useful Pivot-Driven Views

Part III: Building Advanced Dashboard Components

Chapter 7: Charts That Show Trending

Trending Dos and Don’ts

Comparative Trending

Emphasizing Periods of Time

Other Trending Techniques

Chapter 8: Grouping and Bucketing Data

Creating Top and Bottom Displays

Top Values in Charts

Using Histograms to Track Relationships and Frequency

Chapter 9: Displaying Performance against a Target

Showing Performance with Variances

Showing Performance against Organizational Trends

Using a Thermometer-Style Chart

Using a Bullet Graph

Showing Performance against a Target Range

Part IV: Advanced Reporting Techniques

Chapter 10: Macro-Charged Dashboarding

Why Use a Macro?

Recording Your First Macro

Running Your Macros

Enabling and Trusting Macros

Examining Some Macro Examples

Chapter 11: Giving Users an Interactive Interface

Getting Started with Form Controls

Using the Button Control

Using the Check Box Control

Toggling a Chart Series On and Off

Using the Option Button Control

Showing Many Views through One Chart

Using the Combo Box Control

Changing Chart Data with a Drop-Down Selector

Using the List Box Control

Controlling Multiple Charts with One Selector

Chapter 12: Adding Interactivity with Pivot Slicers

Understanding Slicers

Creating a Standard Slicer

Getting Fancy with Slicer Customizations

Controlling Multiple Pivot Tables with One Slicer

Creating a Timeline Slicer

Using Slicers as Form Controls

Part V: Working with the Outside World

Chapter 13: Using External Data for Your Dashboards and Reports

Importing Data from Microsoft Access

Importing Data from SQL Server

Leveraging Power Query to Extract and Transform Data

Chapter 14: Sharing Your Workbook with the Outside World

Protecting Your Dashboards and Reports

Linking Your Excel Dashboards to PowerPoint

Distributing Your Dashboards via a PDF

Distributing Your Dashboards to OneDrive

Limitations When Publishing to the Web

Part VI: The Part of Tens

Chapter 15: Ten Chart Design Principles

Avoid Fancy Formatting

Skip the Unnecessary Chart Junk

Format Large Numbers Where Possible

Use Data Tables Instead of Data Labels

Make Effective Use of Chart Titles

Sort Your Data before Charting

Limit the Use of Pie Charts

Don’t Be Afraid to Parse Data into Separate Charts

Maintain Appropriate Aspect Ratios

Don’t Be Afraid to Use Something Other Than a Chart

Chapter 16: Ten Excel Chart Types and When to Use Them

Line Chart

Column Chart

Clustered Column Chart

Stacked Column Chart

Pie Chart

Bar Chart

Area Chart

Combination Chart

XY Scatter Plot Chart

Bubble Chart

About the Author

Cheat Sheet

Advertisement Page

Connect with Dummies

End User License Agreement

Guide

Cover

Table of Contents

Begin Reading

Pages

iii

iv

vii

viii

ix

x

xi

xii

xiii

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

34

35

36

37

38

39

40

41

42

43

44

45

46

47

48

49

50

51

52

53

54

55

56

57

58

59

60

61

62

63

64

65

66

67

68

69

70

71

72

73

74

75

76

77

78

79

80

81

82

83

84

85

86

87

88

89

90

91

92

93

94

95

96

97

98

99

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

144

145

146

147

148

149

150

151

152

153

154

155

156

157

158

159

160

161

162

163

164

165

166

167

168

169

170

171

172

173

174

175

176

177

179

180

181

182

183

184

185

186

187

188

189

190

191

192

193

194

195

196

197

198

199

201

202

203

204

205

206

207

208

209

210

211

212

213

214

215

216

217

218

219

220

221

222

223

224

225

226

227

228

229

230

231

232

233

234

235

236

237

238

239

240

241

242

243

244

245

246

247

248

249

250

251

252

253

255

256

257

258

259

260

261

262

263

264

265

266

267

268

269

270

271

272

273

274

275

276

277

278

279

280

281

282

283

284

285

286

287

288

289

290

291

292

293

294

295

296

297

298

299

301

302

303

304

305

306

307

308

309

310

311

312

313

314

315

316

317

318

319

320

321

323

324

325

326

327

328

329

330

331

332

333

334

335

336

337

339

340

341

342

343

344

361

362

363

364

365

366

367

368

369

370

Introduction

The term business intelligence (BI), coined by Howard Dresner of Gartner, Inc., describes the set of concepts and methods to improve business decision-making by using fact-based support systems. Practically speaking, BI is what you get when you analyze raw data and turn that analysis into knowledge. BI can help an organization identify cost-cutting opportunities, uncover new business opportunities, recognize changing business environments, identify data anomalies, and create widely accessible reports.

Over the past few years, the BI concept has overtaken corporate executives who are eager to turn impossible amounts of data into knowledge. As a result of this trend, whole industries have been created. Software vendors that focus on BI and dashboarding are coming out of the woodwork. New consulting firms touting their BI knowledge are popping up virtually every week. And even the traditional enterprise solution providers, like Business Objects and SAP, are offering new BI capabilities.