Come creare una dashboard su Excel

Le dashboard sono uno strumento molto potente per l'analisi dei dati su Excel. Le dashboard sono onnipresenti nei controlli di gestione aziendali e possono originarsi da numerosi strumenti, incluso Excel.

La definizione che preferisco è quella, tradotta sommariamente, offerta da John Walkenbach, Michael Alexander e Richard Kusleika nel libro Excel Bible 2019:

Un dashboard è un'interfaccia visiva che fornisce una visione a colpo d'occhio delle misure chiave relative a un particolare obiettivo o processo aziendale. Le dashboard hanno tre caratteristiche principali:

• Le dashboard (anche dette cruscotti) sono tipicamente di natura grafica e forniscono visualizzazioni che aiutano a focalizzare l'attenzione su tendenze, confronti ed eccezioni.
• Le dashboard spesso visualizzano dati rilevanti solo per l'obiettivo della dashboard stessa.
• Poiché le dashboard sono progettate con uno scopo o un obiettivo specifico, esse contengono intrinsecamente conclusioni predefinite che esonerano gli utenti finali dall'effettuare le proprie analisi.

In sostanza, le dashboard aggregano diversi dati in una singola vista per poi trarre delle evidenze utili per l'azienda. Possono essere aggiornate velocemente e hanno tipi di visualizzazione che possono variare, integrando tabelle, grafici e commenti testuali - tutti calcolati in automatico.

Excel si presta molto bene a creare dashboard, anche se chiaramente esistono molti tool sul mercato dedicati esclusivamente alla creazione di dashboard: parliamo di soluzioni di BI (Business Intelligence) quali Power BI o Tableau, che sono più bloccate lato utente ma hanno performance eccellenti quando si tratta di riassumere moli di dati imponenti.

In questa guida, vediamo come creare una dashboard esclusivamente su Excel utilizzando un database con 7.500 righe. La nostra intenzione è creare una dashboard che riassuma il margine totale di vendita suddiviso per film, regione e tipo (Blu-Ray, DVD, Digitale), filtrabile per la censura degli stessi film e anche per diversi periodi di vendita.

Qui puoi scaricare sia il file iniziale per seguire la guida passo passo oppure il file già completato con la dashboard finale:

Come definire i dati di partenza per creare una dashboard su Excel

Il processo ideale per ottenere i dati necessari per creare una dashboard su Excel sarebbe il seguente:

• I dati sono pescati dalle origini dati con Power Query. In questo modo, un semplice aggiornamento delle query ti permette di ottenere anche gli ultimi risultati, garantendo l'aggiornamento costante dei dati con pochi, semplici passaggi. Power Query ha il vantaggio di essere a step, quindi puoi studiare i singoli passaggi di pulizia e agire se qualche dato sia cambiato mentre ti occupi della parte di analisi
• Da Power Query, i dati sono inseriti in una tabella su Excel. Se i dati sono troppo grandi, ovvero oltre 1M di righe, utilizzeremo Power Pivot direttamente senza questo passaggio intermedio. Possiamo anche andare direttamente sulla tabella pivot, se siamo sicuri che i dati siano corretti. Le tabelle possono essere comode per verificare che i dati siano corretti prima di buttarci sull'analisi: nascendo da Power Query, ogni aggiornamento delle query ci garantirà che questi dati siano correttamente aggiornati anche in settimane o mesi successivi rispetto alla creazione del file
• Dalla tabella, passiamo a una o più tabelle pivot. Queste pivot sono il modo più semplice per generare l'origine delle dashboard, dato che ci permetteranno di collegare fra di loro diverse informazioni senza utilizzare alcun codice o formula
• Dalle tabelle pivot, passiamo ai grafici. Questo è l'ultimo livello che ci serve per aggiungere le informazioni sulla dashboard, che generalmente saranno composte da diversi grafici tutti con uno scopo ben preciso

Va da sè che le dashboard su Excel possono essere create in molti modi diversi. Io utilizzo spesso la combinazione di Power Query + tabelle per poi creare delle strutture personalizzate che si basano sulle formule di Excel 365, dato che ne apprezzo la flessibilità: potendo agire sull'origine dati come preferisco, sono più libero quando devo creare dashboard particolarmente complesse. Ciò non toglie che anche io utilizzi molto spesso il procedimento di questa guida, dato che per analisi rapide le pivot rimangono uno strumento imprescindibile per lavorare su Excel.

Se non hai mai utilizzato Power Query e pensi che il procedimento precedente sia troppo complesso per le tue necessità, non ti preoccupare. Idealmente, però, i tuoi dati di partenza per la dashboard Excel sono inseriti in un formato di tabella. Se così non è, ti sarà sufficiente cliccare all'interno del tuo intervallo dati > Inserisci > Tabella. L'alternativa è cliccare all'interno dell'intervallo e cliccare su CTRL+T, il risultato sarà lo stesso. Excel ti chiederà quale siano le celle da considerare per la tua nuova tabella e se ci siano delle intestazioni: mi auguro che sia così, dato che una tabella senza intestazioni renderebbe molto complesso il nostro lavoro in fase di analisi. Il suggerimento di utilizzare le tabelle è valido a prescindere da questa guida, dato che è molto più semplice utilizzarli come base per le formule, si aggiornano in automatico e semplificano moltissimo la gestione dei nostri file aziendali.

Come creare una dashboard su Excel utilizzando le tabelle pivot

Le tabelle pivot sono un valido alleato per la creazione delle dashboard su Excel. In questo caso, le utilizzeremo perchè collegheremo fra di loro tre diverse tabelle pivot con uno scopo specifico: stiamo discutendo del margine ottenuto negli scorsi anni e vogliamo avere una visione complessiva di alcune dimensioni che pensiamo siano utili per l'analisi.

Qui c'è una guida dedicata su come creare, aggiornare e modificare le tabelle pivot su Excel, nel caso ti interessasse.

Creeremo quindi tre diverse tabelle pivot, che collegheremo poi con un filtro dati (slicer) e una sequenza temporale (timeline). Il procedimento è assolutamente lo stesso anche se stai utilizzando dei tuoi file di lavoro, quindi scegli pure se seguire la guida con il file offerto qui oppure con i tuoi file aziendali.

Prima di tutto, creiamo la nostra prima pivot: vogliamo calcolare il margine per singolo film negli anni di vendita considerati. Nel file allegato, la troverai già sul foglio chiamato PIVOT.

A questo punto, creiamo la nostra seconda pivot. A differenza del caso standard, dove la pivot viene generata su un nuovo foglio, selezioniamo Foglio di lavoro esistente nella cella I6 del foglio PIVOT. Questo ci permetterà di avere una visione d'insieme del nostro margine, evitando di utilizzare diversi fogli per rivedere i risultati: l'obiettivo della dashboard è proprio quello di offrirci una visione d'insieme, quindi va benissimo aggregare diverse pivot nello stesso foglio.

Puoi anche fare copia incolla della pivot precedente e poi modificarla, ma penso sia più logico ripartire da zero dato che stiamo guardando dei dati leggermente diversi per la nostra dashboard.

A questo punto avrai una visualizzazione tipo questa. Inserisci Tipo nella sezione Righe e Margine nella sezione Valori.

Avremo ora le nostre due pivot. Sulla stessa schermata, possiamo vedere sia il totale per i singoli tipi di supporto sia per i film.

Ti suggerisco caldamente di cambiare almeno i colori delle singole tabelle, in modo da semplificare la leggibilità. Questo è possibile dal menu Progettazione contestuale alla singola pivot, nella parte chiamata Stili veloci tabella pivot. Puoi quindi cambiare il colore come preferisci, sempre prediligendo stili leggeri come colore. Gli stili troppo colorati rischiano infatti di confondere chi poi questi dati deve utilizzarli, quindi rimani su colori tenui.

Ci serve la terza pivot, quindi ripetiamo la procedura vista in precedenza. Inseriamo la pivot sempre sul nostro foglio esistente ma stavolta nella cella I13.

A livello di impostazione, stai sempre in guardia quando una pivot è posizionata sotto un'altra. Se infatti avessimo numerosi nuovi supporti, questo potrebbe creare dei problemi in fase di aggiornamento. Nel nostro caso, sappiamo che non è così e quindi possiamo permetterci di impilarle sulla stessa colonna.

I passaggi sono uguali ai precedenti, ma stavolta inseriamo Regione nella sezione Righe e Margine nella sezione Valori. Modifichiamo anche alcune delle etichette per renderle più comprensibili, semplicemente cliccando all'interno della singola cella e scegliendo in autonomia quale sia il testo che preferiamo, passando da Etichette di riga a TIPO o REGIONE.

A questo punto abbiamo le nostre pivot di partenza, ma i dati non sembrano essere troppo collegati fra di loro. Le pivot sono infatti scollegate: se ne filtriamo una, le altre non cambieranno i valori contenuti al loro interno.

Come inserire un filtro dati su una tabella pivot in Excel

I filtri dati sono i nuovi filtri delle tabelle pivot su Excel che permettono di collegare fra loro più tabelle pivot. I filtri dati (o slicer) sono presenti anche sulle tabelle normali e appaiono come dei grandi pulsanti che permettono all'utente di selezionare i dati che preferisce per poi ottenere delle dashboard dinamiche. Utilizzo spesso gli slicer quando devo creare delle dashboard, poichè sono veramente immediati per l'utente finale e permettono di visualizzare velocemente quali siano quelli inclusi nella statistica che stiamo valutando. Se invece utilizziamo i filtri standard, potremmo dimenticarci della loro presenza dato che appaiono sopra rispetto alla pivot e, rimuovendoli, potremmo perderceli per strada.

La procedura per inserire i filtri dati è in realtà molto semplice. Basta cliccare sulla pivot che ci interessa > Analisi tabella pivot > Inserisci filtro dati e potremo inserire tutti i filtri dati che desideriamo. Questo tipo di filtro funziona a prescindere dal tipo di dato presente sul file, quindi funziona sia con numeri sia con testo.

Nel nostro caso, ci interessa la CENSURA quindi clicchiamo sulla spunta e poi su OK.

Ci apparirà una schermata tipo questa: abbiamo creato un nuovo filtro dei dati che impatta esclusivamente sulla prima tabella pivot. Inizialmente avremo tutti i campi selezionati, ma cliccando su VM14 si aggiorneranno soltanto i risultati per la nostra prima tabella pivot. Ce ne accorgiamo perchè il totale complessivo varia dai FILM al TIPO, quindi c'è ancora del lavoro da fare per creare la nostra dashboard. Siamo comunque quasi arrivati al completamento della parte di preparazione del dato.

Se vuoi selezionare più valori, basta cliccare in alto a destra dove trovi la lista con tutte le V - mentre cliccando sul simbolo dell'imbuto, eliminerai la tua selezione e la pivot tornerà a considerare la totalità dei risultati.

Creando il filtro dei dati, noterai che si aprirà anche un nuovo menu contestuale chiamato proprio Filtro dei dati. Vediamolo nel concreto:

• Filtro dati: questa sezione ci permette di inserire alcune impostazioni utili e di modificare il nome da visualizzare in cima al filtro. Nelle impostazioni, ad esempio, possiamo decidere se visualizzare l'intestazione o se ci interessi vedere i valori vuoti
• Connessioni rapporto: questa sezione è l'arma segreta dato che ci permette di collegare fra di loro le diverse pivot. Cliccando su Connessioni rapporto vedremo con quali pivot sia collegato il nostro slicer: se ne aggiungiamo una, vuol dire che il nostro filtro agirà su entrambe
• Stile filtro dati: questa sezione è puramente di layout, come quelle simili per le tabelle o le pivot. Puoi utilizzarla per modificare a tuo piacimento i colori del singolo slicer o creare un tuo stile
• Disponi: questa sezione è molto simile a Powerpoint. Ti può tornare utile per decidere la priorità rispetto ad altri elementi sullo stesso foglio, in modo da creare dei layout personalizzati
• Pulsanti: questa sezione può interessarti per aggiungere delle colonne, in modo da visualizzare meglio le intestazioni, oppure per definire la dimensione esatta dello slicer
• Dimensioni: questa sezione è simile a quella che trovi sui grafici in Excel. Puoi modificare posizione e layout, ad esempio bloccando lo slicer in una certa posizione per tenere una struttura bloccata

Vediamo adesso come sfruttare il nostro filtro dati per collegare le diverse pivot fra di loro.

Come collegare diverse tabelle pivot su Excel in una dashboard

Utilizzeremo il filtro dei dati per collegare fra di loro diverse tabelle pivot su Excel, in modo da creare la nostra dashboard. Vediamo come fare in pochi, semplici passaggi.

Abbiamo già creato il nostro filtro quindi possiamo cliccare sul filtro e poi su Connessioni rapporto.

Come vedi, in questo caso abbiamo collegato unicamente la prima pivot, quella relativa alle vendite sui film. Questo è un controllo utilissimo che farai di frequente quando creerai delle dashboard da zero.

Per collegare tutte le pivot, non ti resta che cliccare sulle singole tabelle. In questo modo, tutte le tabelle saranno influenzate dallo stesso slicer: quando cambierai la tua selezione, anche i dati delle singole pivot cambieranno. Questo modo di gestire le dashboard è perfetto se sai che il tuo utente di riferimento non è troppo esperto su Excel, poichè potrà solo cliccare sui pulsanti evitando di modificare la struttura della pivot sottostante.

Come vedi, selezionare ora il valore VM14 cambia anche il risultato del TIPO e della REGIONE. Un singolo slicer comanda le tre pivot e ci permette di avere grande flessibilità nella creazione di dashboard dinamiche. Potremmo anche aggiungerne degli altri e, ripetendo gli step precedenti, aggiungere ulteriori dimensioni di analisi.

Come creare una dashboard su Excel

Le nostre tabelle pivot sono collegate e ora possiamo creare una vera e propria dashboard su Excel.

Come prima cosa, iniziamo a creare dei grafici per le singole pivot. Questo è possibile semplicemente cliccando all'interno della pivot > Inserisci > Grafico oppure cliccando all'interno della pivot > Analisi tabella pivot > Grafico pivot. Dato che i nomi dei film sono piuttosto lunghi e numerosi, inseriamo un grafico a barre.

A questo punto facciamo taglia e incolla (CTRL+X e CTRL+V) spostando il nostro grafico in un altro foglio, chiamato Foglio1. Cambieremo a breve il nome in DASHBOARD.

Questa operazione è importante per separare l'origine dei dati, cioè la pivot, dalla parte finale di visualizzazione, ovvero la nostra dashboard finale. L'utente agirà esclusivamente sulla dashboard, evitando di modificare la pivot di partenza.

Un suggerimento è quello di ordinare i dati di partenza, dato che altrimenti il grafico appare un po' confusionario. Cliccando sulla pivot > cliccando sul menu di FILM > Altre opzioni di ordinamento... vedremo che possiamo scegliere l'ordine per le informazioni inserite.

In questo caso selezioniamo di visualizzare, sulla pivot, i FILM in ordine crescente per il MARGINE_TOTALE. Questo succede perchè il grafico a barre inverte i dati iniziali, quindi nel grafico vedremo invece prima i film dal maggiore margine e poi quelli successivi fino a visualizzare quelli con il margine minore. Questa caratteristica è da tenere a mente se vogliamo utilizzare i grafici a barre: lo stesso risultato può essere raggiunto invertendo i dati poi sul grafico, ma ti consiglierei di utilizzare l'ordinamento crescente come descritto.

Ordinato, il grafico dovrebbe venire più o meno così. Chiaramente i dati sono veramente tanti, quindi anche la leggibilità potrebbe essere complessa se non hai uno schermo di grandi dimensioni. In linea di massima, 50 film (come nel nostro caso) sono un po' troppi per avere un'analisi puntuale - tuttavia, filtrando per la censura, il numero si ridurrà considerevolmente e ci permetterà di ottenere informazioni più precise sul nostro andamento aziendale.

Creiamo altri due grafici per le dimensioni considerate. Per il TIPO, ho inserito un grafico a colonne raggruppate mentre per la REGIONE ho nuovamente inserito un grafico a barre, sempre ordinandoli per aiutare la parte di comprensione.

Possiamo inserire l'ultimo elemento della dashboard: il filtro dei dati focalizzato sulla CENSURA. Facciamo anche in questo caso taglia / incolla (CTRL+X e CTRL+V) e lo riportiamo vicino ai nostri grafici.

A questo punto la nostra dashboard è completata! Cliccando sulle varie opzioni dello slicer, modificheremo in tempo reale tutti i risultati dei tre grafici. Possiamo sempre aggiungerne degli altri o cambiare i grafici di partenza, attenzione solo a modificare le pivot dato che i grafici stessi potrebbero cambiare.

Questa dashboard può rappresentare l'inizio di una dashboard molto più complessa, magari arricchita con degli elementi di branding aziendale, messaggi per la nostra platea creati in automatico e altri elementi grafici per guidarne la comprensione. Siamo comunque riusciti a collegare tre grafici che possono tornarci assai utili, dato che potremo riutilizzare questa struttura per varie analisi da condurre velocemente semplicemente utilizzando il pulsante dello slicer.

Un elemento di miglioramento potrebbe essere l'inserimento di una sequenza temporale, o timeline. Vediamo come funziona.

Come inserire una sequenza temporale su una tabella pivot in Excel

La sequenza temporale può aggiungere la dimensione temporale sulla nostra dashboard in Excel. Le sequenze temporali (o timeline) sono degli strumenti utili per fare drilldown specifici su periodi specifici senza dover ricorrere a formule complesse e, per fortuna, sono piuttosto semplici da inserire sulle tabelle pivot.

Nel nostro esempio, torniamo sulle tabelle pivot di partenza. Nel menu Analisi tabella pivot selezioniamo Inserisci Sequenza Temporale, che ci permetterà di creare una timeline collegata con la tabella pivot di partenza.

La differenza principale con i filtri dati è che Excel selezionerà esclusivamente le colonne della tabella in formato data. Non potremo utilizzare, comprensibilmente, la sequenza temporale in colonne che non siano date dato che la timeline non avrebbe alcun senso di esistere. Questo è anche un check utile per la tua tabella di partenza, dato che a volte le date potrebbero non essere correttamente interpretate da Excel e quindi potrebbero non apparirti.

Anche la sequenza temporale ha il suo menu dedicato:

• Sequenza temporale: in questa sezione possiamo decidere il nome da far apparire per la nostra timeline e le connessioni rapporto, che funzionano esattamente come quelle per i filtri dei dati. Da qui, potremo associare diverse tabelle pivot
• Stili sequenza temporale: questa sezione ci permette di modificare lo stile della sequenza temporale, così come di creare un nostro stile personalizzato
• Disponi: questa sezione è uguale a quella dei filtri dei dati, permettendo di ordinare e allineare gli elementi sul foglio
• Dimensioni: questa sezione permette di definire la dimensione totale della sequenza temporale e altre piccole modifiche lato layout, come per esempio per bloccare la singola sequenza temporale sul foglio
• Mostra: questa sezione personalizza ulteriormente il layout utilizzato per la nostra timeline. Possiamo ad esempio nascondere l'intestazione o la barra di scorrimento, a seconda di quello che preferiamo

Ogni dashboard ha una sola timeline, dato che non avrebbe senso sfruttarne più di una, parlando di filtri per singole date. Di default, la timeline ti permetterà anche di selezionare dei periodi quali mesi, trimestri e anni. Questo è particolarmente comodo perchè potrai selezionare in maniera dinamica questi intervalli, ad esempio inserendo Q2-Q3 di un certo anno.

Una volta inserita la nostra timeline, possiamo agire nuovamente su Connessioni rapporto, che è disponibile sia nel menu contestuale sia cliccando con il tasto destro sulla nostra timeline.

Come nel caso dei filtri, anche in questo caso dobbiamo accertarci di aver inserito tutte le nostre pivot anche sulla sequenza temporale. Il rischio è quello di non inserire tutte le pivot, generando degli output parziali che potrebbero essere fraintesi.

A questo punto, la nostra dashboard è completata! Possiamo sia filtrare per i diversi tipi di censura sia per i diversi periodi temporali che ci interessano. L'utente finale potrebbe anche non visionare le pivot, dato che anche con il foglio nascosto la dashboard funzionerebbe lo stesso. Come dicevamo, le dashboard sono molto comode proprio nella fase di analisi per permettere di visualizzare velocemente varie informazioni in una sola vista. Questa dashboard può essere resa sicuramente più gradevole e complessa, ma il procedimento non varierà in modo importante anche se dovessi ripetere il processo sui tuoi file: l'importante è avere in mente una domanda di business chiara, in modo da puntare direttamente all'obiettivo e sfruttare Excel a tuo piacimento. Una dashboard come questa richiede una manutenzione minima e genera una struttura scalabile molto comoda sia per chi crea sia per chi deve utilizzare Excel, aiutando la parte di analisi senza dover ricorrere a formule o altri strumenti sicuramente più complessi.