Excel Dashboards & Reports For Dummies

Excel Dashboards & Reports For Dummies
Автор книги: id книги: 2280460     Оценка: 0.0     Голосов: 0     Отзывы, комментарии: 0 3219,36 руб.     (33$) Читать книгу Купить и скачать книгу Электронная книга Жанр: Программы Правообладатель и/или издательство: John Wiley & Sons Limited Дата добавления в каталог КнигаЛит: ISBN: 9781119844419 Скачать фрагмент в формате   fb2   fb2.zip Возрастное ограничение: 0+ Оглавление Отрывок из книги

Реклама. ООО «ЛитРес», ИНН: 7719571260.

Описание книги

It’s time for some truly “Excel-lent” spreadsheet reporting Beneath the seemingly endless rows and columns of cells, the latest version of Microsoft Excel boasts an astonishing variety of features and capabilities. But how do you go about tapping into some of that power without spending all of your days becoming a spreadsheet guru? It’s easy. You grab a copy of the newest edition of Excel Dashboards & Reports For Dummies and get ready to blow the pants off your next presentation audience! With this book, you’ll learn how to transform those rows and columns of data into dynamic reports, dashboards, and visualizations. You’ll draw powerful new insights from your company’s numbers to share with your colleagues – and seem like the smartest person in the room while you’re doing it. Excel Dashboards & Reports For Dummies offers: Complete coverage of the latest version of Microsoft Excel provided in the Microsoft 365 subscription Strategies to automate your reporting so you don’t have to manually crunch the numbers every week, month, quarter, or year Ways to get new perspectives on old data, visualizing it so you can find solutions no one else has seen before If you’re ready to make your company’s numbers and spreadsheets dance, it’s time to get the book that’ll have them moving to your tune in no time. Get Excel Dashboards & Reports For Dummies today.

Оглавление

Michael Alexander. Excel Dashboards & Reports For Dummies

Microsoft® Excel® Dashboards & Reports For Dummies® To view this book's Cheat Sheet, simply go to www.dummies.com and search for “Excel Dashboards Reports For Dummies Cheat Sheet” in the Search box. Table of Contents

List of Tables

List of Illustrations

Guide

Pages

Introduction

About This Book

Foolish Assumptions

Icons Used in This Book

Beyond the Book

Where to Go from Here

Getting Started with Excel Dashboards and Reports

Getting in the Dashboard State of Mind

Defining Dashboards and Reports

Defining reports

Defining dashboards

Preparing for Greatness

Establish the audience for, and purpose of, the dashboard

Delineate the measures for the dashboard

Catalog the required data sources

Define the dimensions and filters for the dashboard

Determine the need for drill-down features

Establish the refresh schedule

A Quick Look at Dashboard Design Principles

Rule number 1: Keep it simple

Don’t turn your dashboard into a data repository

Avoid the fancy formatting

Limit each dashboard to one printable page

Use layout and placement to draw focus

Format numbers effectively

Use titles and labels effectively

Building a Super Model

Data Modeling Best Practices

Separating data, analysis, and presentation

Starting with appropriately structured data

Spreadsheet reports make for ineffective data models

Flat data files lend themselves nicely to data models

Tabular data sets are perfect for pivot table–driven data models

Avoiding turning your data model into a database

Using tabs to document and organize your data model

SPEAKING OF DOCUMENTING YOUR DATA MODEL …

Testing your data model before building reporting components on top of it

Excel Functions That Really Deliver

The VLOOKUP function

VLOOKUP basics

Applying VLOOKUP formulas in a data model

Using data validation drop-down lists in the data model

The HLOOKUP function

HLOOKUP basics

Applying HLOOKUP formulas in a data model

The SUMPRODUCT function

SUMPRODUCT basics

A twist on the SUMPRODUCT function

Applying SUMPRODUCT formulas in a data model

The CHOOSE function

CHOOSE basics

Applying CHOOSE formulas in a data model

Using Smart Tables That Expand with Data

Converting a range to an Excel table

Converting an Excel table back to a range

Introducing Dynamic Arrays

Getting the basics of dynamic arrays

DYNAMIC ARRAYS AND COMPATIBILITY

Understanding spill ranges

Referencing spill ranges

WHY DO I SEE AN AT SIGN (@) IN MY FORMULAS?

Exploring Dynamic Array Functions

The SORT function

The SORTBY function

The UNIQUE function

The FILTER function

The XLOOKUP function

The Pivotal Pivot Table

An Introduction to the Pivot Table

The Four Areas of a Pivot Table

Values area

Row area

Column area

Filter area

Creating Your First Pivot Table

Changing and rearranging your pivot table

Adding a report filter

Keeping your pivot table fresh

PIVOT TABLES AND SPREADSHEET BLOAT

Customizing Pivot Table Reports

Changing the pivot table layout

Customizing field names

Applying numeric formats to data fields

Changing summary calculations

Suppressing subtotals

Removing all subtotals at one time

Removing the subtotals for only one field

Removing grand totals

Showing and hiding data items

Hiding or showing items without data

Sorting your pivot table

Creating Useful Pivot-Driven Views

Producing top and bottom views

Creating views by month, quarter, and year

Creating a percent distribution view

Creating a month-over-month variance view

Using External Data for Your Dashboards and Reports

Leveraging Power Query to Extract and Transform Data

Reviewing Power Query basics

MANAGING EXTERNAL DATA PROPERTIES

Understanding query steps

Importing Data from Files

Getting data from Excel workbooks

Getting data from CSV and text files

Importing Data from Database Systems

Importing data from Microsoft Access

Managing data source settings

Building Basic Dashboard Components

Dressing Up Your Data Tables

Table Design Principles

Use colors sparingly

De-emphasize borders

Use effective number formatting

Subdue your labels and headers

Getting Fancy with Custom Number Formatting

Number formatting basics

Formatting numbers in thousands and millions

Hiding and suppressing zeroes

Applying custom format colors

Formatting dates and times

Sparking Inspiration with Sparklines

Introducing Sparklines

Understanding Sparklines

Creating sparklines

Understanding sparkline groups

Customizing Sparklines

Sizing and merging sparkline cells

Handling hidden or missing data

Changing the sparkline type

Changing sparkline colors and line width

Using color to emphasize key data points

Adjusting sparkline axis scaling

Faking a reference line

Specifying a date axis

Autoupdating sparkline ranges

Formatting Your Way to Visualizations

Enhancing Reports with Conditional Formatting

Applying basic conditional formatting

Using the Highlight Cells Rules

Applying Top/Bottom Rules

Creating Data Bars

Applying Color Scales

Using Icon Sets

Adding your own formatting rules manually

Showing only one icon

Showing Data Bars and icons outside of cells

Representing trends with Icon Sets

Using Symbols to Enhance Reporting

Wielding the Magical Camera Tool

Finding the Camera tool

Using the Camera tool

Enhancing a dashboard with the Camera tool

Enhancing Excel Reports with Shapes

Creating visually appealing containers with shapes

Layering shapes to save space

Constructing your own infographic widgets with shapes

Adding Charts to Your Dashboards

Charts That Show Trending

Trending Dos and Don’ts

Using chart types appropriate for trending

Using line charts

Using area charts

Using column charts

Starting the vertical scale at zero

Leveraging Excel’s logarithmic scale

Applying creative label management

Abbreviating instead of changing alignment

Implying labels to reduce clutter

Going vertical when you have too many data points for horizontal

Nesting labels for clarity

Comparative Trending

Creating side-by-side time comparisons

Creating stacked time comparisons

Trending with a secondary axis

Emphasizing Periods of Time

Formatting specific periods

Using dividers to mark significant events

Representing forecasts in your trending components

Other Trending Techniques

Avoiding overload with directional trending

Smoothing data

Smoothing with Excel’s moving average functionality

Creating your own smoothing calculation

Grouping and Bucketing Data

Creating Top and Bottom Displays

Incorporating top and bottom displays into dashboards

Using pivot tables to get top and bottom views

Top Values in Charts

Using Histograms to Track Relationships and Frequency

Using Excel's Histogram statistical chart

Creating a formula-driven histogram

Adding a cumulative percent

Using a pivot table to create a histogram

Displaying Performance against a Target

Showing Performance with Variances

Showing Performance against Organizational Trends

Using a Thermometer-Style Chart

Using a Bullet Graph

Creating a bullet graph

Adding data to your bullet graph

Final thoughts on formatting bullet graphs

Creating qualitative bands

Creating horizontal bullet graphs

Showing Performance against a Target Range

Advanced Reporting Techniques

Giving Users an Interactive Interface

Introducing Macros

Why use a macro?

Recording your first macro

Running your macros

Enabling and trusting macros

Understanding macro-enabled file extensions

Enabling macro content

Setting up trusted locations

Examining some macro examples

Building navigation buttons

Dynamically rearranging pivot table data

Offering one-touch reporting options

Getting Started with Form Controls

Finding Form controls

Adding a control to a worksheet

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

Adding Interactivity with Pivot Slicers

Understanding Slicers

Creating a Standard Slicer

Getting Fancy with Slicer Customizations

Size and placement

Data item columns

Other slicer settings

Creating your own slicer style

Controlling Multiple Pivot Tables with One Slicer

Creating a Timeline Slicer

Using Slicers as Form Controls

Using Slicers on Excel Table Objects

Sharing Your Workbook with the Outside World

Protecting Your Dashboards and Reports

Securing access to the entire workbook

Permitting read-only access unless a password is given

Requiring a password to open an Excel file

Removing workbook-level protection

Limiting access to specific worksheet ranges

Unlocking editable ranges

Applying worksheet protection

Protecting sheet elements and actions

Removing worksheet protection

Protecting the workbook structure

Linking Your Excel Dashboards to PowerPoint

Creating a link between Excel and PowerPoint

Manually updating links to capture updates

Turning off automatic updating of links

Distributing Your Dashboards via a PDF

Distributing Your Dashboards to OneDrive

Limitations When Publishing to the Web

The Part of Tens

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

Ten Questions to Ask Before Distributing Your Dashboard

Does My Dashboard Present the Right Information?

Does Everything on My Dashboard Have a Purpose?

Does My Dashboard Prominently Display the Key Message?

Can I Maintain This Dashboard?

Does My Dashboard Clearly Display Its Scope and Shelf Life?

Is My Dashboard Well Documented?

Is My Dashboard Overwhelmed with Formatting and Graphics?

Does My Dashboard Overuse Charts When Tables Will Do?

Is My Dashboard User-Friendly?

Is My Dashboard Accurate?

Index. Symbols

A

B

C

D

E

F

G

H

I

J

K

L

M

N

O

P

Q

R

S

T

U

V

W

X

Y

Z

About the Author

Dedication

Author’s Acknowledgments

WILEY END USER LICENSE AGREEMENT

Отрывок из книги

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.

.....

When you have the list of measures that need to be included on the dashboard, it’s important to take a tally of the available systems to determine whether the data required to produce those measures is available. Ask yourself the following questions:

These are all questions you need answered when negotiating dashboard development time, data refresh intervals, and change management.

.....

Добавление нового отзыва

Комментарий Поле, отмеченное звёздочкой  — обязательно к заполнению

Отзывы и комментарии читателей

Нет рецензий. Будьте первым, кто напишет рецензию на книгу Excel Dashboards & Reports For Dummies
Подняться наверх