Always wondered how many emails you receive? How many emails you send? Who sends you most of your emails? With Excel 2016 you can use the Email Insights template to gain extensive, and insightful information on how you use your email account.
Using the Email Insights template
To use the Email Insights template, you need Excel 2016 or a subscription to Office 365. To access your data using the template you need an active Microsoft Exchange account.
Open the Email Insights template
To open the Email Insights template, select New > Email Insights.
Note: If you can't see the template, use the search box and type Email Insights, and then select the magnifying glass button to begin the search (or press Enter).
Once the template is open, you see the first workbook tab, titled Welcome. Click Let’s Get Started.
The Import Your Email tab is selected, providing steps about how to connect to your Exchange account and import your data.
When you click the Sign In button, Excel displays a dialog that asks you to login to your Exchange account.
Enter your credentials (note that entering the UPN is not mandatory), and click Connect. You will be promoted to select Privacy Levels. In the Privacy levels dialog box, select Public for both Exchange and Current Workbook, and then click Save. For more information on privacy levels in Excel, see Privacy levels (Power Query).
Excel connects to your Exchange account and imports your data for the last month as a default. However you can easily modify the date range.
Note: The connection process can take a few minutes depending on how many emails you send and receive.
Once Excel gets the data, the Dashboard worksheet appears and displays a populated dashboard that contains a collection of detailed analytics based on your data.
The Dashboard contains all sorts of interesting insights about your email data such as how many emails you send, receive, who sends you most of your emails and much more. The following image shows the worksheet, and below the image is a description of each numbered section.
Dates Filter filters the data brought from your Exchange account. The default is set to the last month but you can select any range and click Refresh All in the Data ribbon.
Reading email AVG time (seconds) is used to calculate how much time you spent reading emails, and how much time you need to spend to read all your unread emails from the selected date range.
Writing email AVG time (seconds) is used to calculate how much time you spent writing emails in the selected date range.
Amount of Received emails in the selected date range.
Time spent reading emails is calculated by the amount of emails you read multiplied by Reading email AVG time.
Amount of Read emails in the selected date range.
Amount of Unread emails in the selected date range.
Time to spend reading all unread emails received in the selected date range. This measure is calculated by the amount of unread emails multiplied by Reading email AVG time.
Amount of Sent emails in the selected date range.
Time spent writing emails in the selected date range. This measure is calculated by the amount of sent emails multiplied by writing email AVG time.
Bar Chart (PivotChart) that displays the amount of emails you send and receive every day during the week in the selected date range.
Bar Chart (PivotChart) that displays the amount of emails you send and receive by hour in the selected date range.
Treemap Chart that displays top 50 contacts who sent you most of your emails.
Treemap Chart that displays top 50 contacts you sent most of your emails.
Can I share this workbook?
Once Excel loads your emails data into Email Insights workbook, your data is in the workbook’s Data Model.
Warning: Your credentials are not stored in the workbook, so if you share the workbook with a coworker and they select the Refresh All button to update the workbook, they will be prompted to enter their credentials, and your data will be removed as part of the refreshing process of the workbook (your data is replaced by your coworker’s data).
How can I create a similar workbook on my own?
You can create workbooks like this using the built-in Data Analysis features in Excel 2016.
To connect to Exchange, extract your data and shape the data for analysis, this template uses Power Query technology which was integrated into Excel 2016, and is available through the Data ribbon in the Get & Transform group. For more information, see Get & Transform in Excel 2016.
To start importing your Exchange data from a new workbook, from the ribbon select Data > New Query > From Other Sources > From Microsoft Exchange.