Adding rich data labels to charts in Excel 2013

AppId is over the quota
AppId is over the quota

Storytelling is a powerful communication tool, and data is essential for many decision-making tasks. Together, they can be data visualization at its best: the science and art of transforming your data so that the most important points shine through. Sometimes a basic chart will do the trick.  But to make your visual message really pop, it's often handy to add data and text to your chart. The rich data label capabilities in Excel 2013 give you tools to create visuals that tell the story behind the data with maximum impact. 

To illustrate some of the features and uses of data labels, let's first look at simple chart.

This clustered column chart shows the sales (revenue) of drinks and snacks from a neighborhood lemonade stand during one week. If I want to turn on basic data labels on the blue data series (Drinks), there are a few ways to do that.  One familiar and simple way is just single click on any data value (or column, in this example) to select the entire data series that it belongs to.

Above, I have clicked all of the blue columns. Once the series is selected, I can right-click any column to pull up the context menu, then click the Add Data Labels entry.    

When I click Add Data Labels, I get the following result. 

To reposition any single data label, all I have to do is double-click the data label I want to move, then drag it to the desired position on the chart. Here, I have selected only the Tue value of the blue Drinks series.

Once selected, I can drag that label wherever I want it on the chart. If I drag the label far from its default location, a leader line appears by default to show what data point the data label is associated with.

Basic formatting of data labels is simple to achieve by using the Font section of the Home tab on the Excel ribbon.

If you wish to go beyond basic text formatting and text box fills, many more formatting options are available on the Formatting Task pane. Though there are several ways to open the Formatting Task pane, the easiest is to double-click the data labels themselves. Here, I have double-clicked one of the data labels for the blue Drinks series.

In the Formatting Task Pane, you can customize the way the data labels appear, change their size and alignment, change their text properties, and even add another data series for them to include. See Format and customize Excel 2013 charts quickly with the new Formatting Task pane for more discussion about the Formatting Task Pane in general.  

Often, the real story doesn't lie in all the numbers in the chart, but it's hidden in a few key data points. Let's reapproach our example with that in mind.  

First, I'll delete the data labels that I already put in place. To delete all the data labels for a given series, click once on any data label in the series, and this will select them all.  If you press the delete key on your keyboard, all the data labels from that series will disappear from your chart. To delete any single data label, follow the same procedure, except click twice (and not too fast) on the individual data label you wish to delete.

Below, I have inserted just one data label and moved it to a roomy place in the chart. Next, I want to type custom text into the data label box to help tell the story behind the data.

To make it easier to place an insertion point in the data label, I have found that it helps to zoom in on the chart. You can do this by adjusting the zoom control on the bottom right corner of Excel's chrome.

Then, select the value in the data label and hit the right-arrow key on your keyboard.

The story behind the data in our example is that the temperature increased significantly on Wednesday and that appeared to help drive up business at the lemonade stand. So I type some text to emphasize that point while still leaving the data label intact.

Excel 2013 also lets you put numbers from your spreadsheet into your data labels - that is, numbers that are not directly associated with the data point.  Here is a quick example. Let's say that I want to add a further annotation about the temperature on Wednesday and I want to include a data value with that annotation. Here's how I would do it: 

First, I select my data label and I type some additional text to give context to the new number I'm about to add to the data label. Then, I right-click the data label to pull up the context menu. Note the Insert Data Label Field menu item. 

When I click Insert Data Label Field, Excel 2013 opens a dialog that gives me a few options to choose from. I want to pull in a data value that is calculated on my worksheet, so I select Choose Cell.

The Choose Cell option opens a familiar type of dialog that allows me to go back into my worksheet and select the cell with the value that will be shown in my data label. In this example, I select a cell that contains the value that shows how many days it has been since the temperature was this warm (26 days, in this case).  

When I click OK, the value from the cell I selected (D39, here) appears in my data label. To finish it off, I type the rest of my statement and end up with a very rich data label.    

The data labels up to this point have used numbers and text for emphasis. Putting a data label into a shape can add another type of visual emphasis. To add a data label in a shape, select the data point of interest, then right-click it to pull up the context menu. Click Add Data Label, then click Add Data Callout.  The result is that your data label will appear in a graphical callout.  In this case, the category Thr for the particular data label is automatically added to the callout too.

In the image below, I clicked inside the data callout, backspaced over the Thr entry, and then typed a bit of information that explains what is behind this anomalous data point.    

If you want to change the shape of a data callout, you can do so by right-clicking the data label to pull up the context menu, or by selecting the data label, then clicking Change Shape in the Format tab in the ribbon.

In this case, let's say that for the Snacks value on Thursday, I don't really want to show the value in the data label, but I'd like to make my point with something a bit more whimsical. For this, I turn to the rich formatting options in the Formatting Task pane we talked about earlier. Below, I have double-clicked the data label to pull up the Formatting Task pane for the data label.

Then, I clicked the Fill and Line symbol:

I then selected Picture or Texture Fill, and clicked on Online.

Let's say that on Thursday the lemonade stand ran out of donuts, which were the main selling item in the snacks section. I can search Office Art on the web for an image of a donut to serve as the background in my data label.

I pick an image from the results, and it's automatically inserted into the background of my data label.

We're almost there. The donut image is good, but it's too small to convey the message. Also, I want to use a text comment instead of showing the data value for this point. So I re-size the data point's bounding box, select the data value, and replace it with some clarifying text.  I can adjust the text color and font size using the Font controls on the Home tab in the ribbon.   

In this article, we used data labels with text, images, and shapes to help reveal the story behind the data. This example only scratches the surface of the many things you can do with data labels in Excel 2013. Have fun exploring and storytelling, and let us know in the comments how you're putting data labels to work for you!

View the original article here

To improve collaboration within teams: adding notifications to your access-2013 Web applications

Er is een fout opgetreden bij het deserialiseren van de tekst in het antwoordbericht voor bewerking Translate. Het quotum voor de maximumlengte van tekenreeksinhoud (8192) is overschreden bij het lezen van XML-gegevens. Dit quotum kan verhoogd worden door de MaxStringContentLength-eigenschap op het XmlDictionaryReaderQuotas-object te wijzigen dat gebruikt wordt bij het maken van de XML-lezer. Regel 1, positie 9476.
Er is een fout opgetreden bij het deserialiseren van de tekst in het antwoordbericht voor bewerking Translate. Het quotum voor de maximumlengte van tekenreeksinhoud (8192) is overschreden bij het lezen van XML-gegevens. Dit quotum kan verhoogd worden door de MaxStringContentLength-eigenschap op het XmlDictionaryReaderQuotas-object te wijzigen dat gebruikt wordt bij het maken van de XML-lezer. Regel 1, positie 9425.

Access 2013 web apps are great for collaborating around a common set of data. When people work together in this way, the changes that one person makes to the data often require the attention of someone else.

For example, if a small team is working together to track and process maintenance requests, an Access app would be an ideal place to centrally track those requests and their status. Further, imagine that I process a maintenance request and assign it to another team member who needs to take action on it. But how does he or she know? Wouldn't it be great to be automatically informed? That's what notifications can do.

In this article, we'll describe a technique to build e-mail notifications into your Access 2013 web app using the power of SQL Server and a third-party service called Zapier.

In the above example of our maintenance request tracker, people need to get an e-mail when a maintenance request is assigned to them, letting them know of a required action. In the Maintenance Requests table, there's an Assigned To field that is a lookup to the Employees table. Whenever that field changes, an e-mail should be sent to the person to whom the Request is now assigned.

Building this notification functionality involves three steps, detailed further below:

Create a Messages table in the Access web app to store notification emails that you want to send.Create a data macro on the "Maintenance Requests" table that adds records to the Messages table whenever the "Assigned To" field changes.Configure Zapier to automatically monitor the "Messages" table and send an email whenever it detects a new record.

By the end, you'll have something that looks like this:

First, you'll need to create a Messages table to store information about the email notifications that you want to send. This table will need to have fields that specify all the details about the email message, including:

Click on Create > Table in the ribbon, then select the add a new blank table link. Add fields in the table designer so that it looks like this:

You want messages to be sent whenever the Assigned To field changes in a record on the Maintenance Requests table. Data Macros are the perfect tool for the job. Data macros are commands that run whenever records on a table change. For a given table, you can write macros that run when records are added, deleted, or updated. In our case, you'll want to write an On Update macro on the Maintenance Requests table.

To do this, first, open the Maintenance Requests table in the table designer, go to the Design tab, and click Events > On Update.

You'll only want to send the notification if the Assigned To field has changed. To do this, use an If statement that compares the old value of this field with its new value:

If that condition is true, you'll want the macro to create a record in the Messages table. Since you'll want this new Messages record to have the users' e-mail address in the To field, you need to first look that up and store it in a variable:

Finally, use this email address to create the message:

Then, this macro will add a new record to the Messages table whenever the Assigned To field of a record in the Maintenance Requests table changes.

Since, in this example, you also want people to get notifications when a new Maintenance Request is created and assigned to them, you'll create an After Insert macro that looks like this:

The only difference here is that we've changed the If expression at the beginning to run the macro if the Assigned To field is not blank.

Zapier is a third-party tool that you can use to build simple connections between one service, like an Access 2013 web app, and another, like email. Zapier calls these connections "Zaps." To get started building our zap, go to zapier.com and sign up for a free trial account, and click Create New Zap

You'll then find the services that you'd like to connect. In our case, we'll choose SQL Server > New Row as the "trigger" > Gmail > Send Email as the "action."

Click Create Zap and you'll be prompted to enter some more information about these connections. Start with the SQL account. You'll need to tell Zapier how to get permissions to look at the SQL database that powers your Access 2013 web app. Zapier will want to know things like server name, database name, username and password. To find out these things for your database, open it in Access, click File > Info > Manage Connections.

From this menu, you'll want to click From Any Location to disable firewall rules that would otherwise prevent a third-party service like Zapier from connecting. Then, click Enable Read-Only Connection which will generate a username and password that Zapier can use. To view these details, click View Read-Only Connection Information, which will show you something that looks like this:

Back in Zapier, you can copy this information into the Zap. Be sure to use port 1433 to connect:

Next, you'll want to tell Zapier which table it should monitor for changes and how it can tell which rows are new. We'll pick the Messages table, and tell it to examine the ID field. Since the ID field is a number that is automatically increased by one as new rows are added, this will be a reliable way for Zapier to tell the new rows from the old ones.

Next, you'll want to enter the credentials to your Gmail account so that Zapier can send the emails. Gmail is just used as a means of sending-the notifications can be sent to any inbox, such as Outlook, Yahoo, etc.

After your Gmail account is configured, you have to tell Zapier how to map the fields in the Messages table in SQL to the fields that Gmail expects. This part is pretty straightforward: the To field in Gmail maps to the To field in SQL, and so on:

That's it! You can easily test out your Zap by choosing "Click to load samples!" This will load the first row that Zapier finds in the Message table, and you can click to send a sample message.

The final step is to make your Zap live. This means that Zapier will run it every 15 minutes to check for new records in the Messages table, sending emails if it finds any.

You can try Zapier out for free for 15 days. Since SQL connections are a premium service for Zapier, you can sign up for a paid plan when the trial expires. See www.zapier for pricing details.

This article has outlined how to use notifications for a specific example, but there are many other situations where this capability will be useful. However, regardless of the scenario, you can follow the three steps above. We can't wait to hear about the cool things you're able to do with this technique!

View the original article here

Adding Atmosphere with Vignettes

Adding Atmosphere with Vignettes « Moving Colors function clearSearch() {document.search_form.s.value = "";} .recentcomments a{display:inline !important;padding:0 !important;margin:0 !important;}adobe.com      Moving Colors / Adding Atmosphere with Vignettesby Eric Philpottcolor correction (13)color grading (13)look design (6)SpeedGrade (12)Tutorials (8)vignettes (1)  

View the original article here

Combining Chart Types, Adding a Second Axis

AppId is over the quota
AppId is over the quota

(This post was originally written and published by Katherine Fifer, an intern on the Excel 2007 team. We've updated it for Excel 2010 and added a sample worksheet to help you learn.)

Often it is useful to create charts that compare different types of data. For example, you might want to compare a stock's share price with its trading volume, or overall corporate revenue with the number of units sold.

To quickly and clearly display data of different types, it can be helpful to plot varying data sets either with different chart types or on different axes.  

Suppose I work at a manufacturing company, and I'd like to analyze the number of units we've sold over the last few months and the total revenue generated.   I'm hoping to identify trouble spots, such as high unit sales that result in low revenue, which may in turn indicate that the units are being sold too cheaply.

I could make two different charts - one plotting units sold per month, and another plotting total transaction revenue by month. However, it will be challenging to analyze and compare the two charts separately.  I'd much rather create a single chart that incorporates both data sets such as the one shown below.

Chart Showing Both Units Sold and Total Transactions

 

Daunting though it may seem, it turns out that making this kind of chart isn't very difficult at all. We'll walk you through the two major steps -- combining different chart types and adding a secondary axis.  Then we'll show you how to add some finishing touches to make your chart look polished and professional.

Create a Data Set

To ensure you can follow along, we've created a simple data set, as well as a sample workbook that you can use to walk through the steps yourself:

Sample Data Showing Units Sold and Total Transactions

Select Your Data Set

The first step in building a chart with more than one chart type is to actually set up a chart with just a single chart type. For this scenario, I want a chart with columns and lines, but I'll start with a regular column chart. (Note: It actually doesn't matter which chart type you start with, but if you're working with a multiple data sets, I'd pick the chart type which applies to the majority of your data - it'll mean less work for you later).

Select the data that you would like to use for your chart.
Go to the Insert tab, and then choose a type of column to insert (as shown below). For the sake of this scenario, select one of the 2-D column types.Selecting A Chart Type

 

Now we have a column chart with two data sets (Units Sold and Total Transactions), both charted using the same chart type.

 Chart Tracking Two Different Data Sets

Changing One of the Data Sets to a Line Chart

The next big step is to change the chart type for the Total Transactions data set into a line chart.

1.  Click on the Total Transactions data column in the chart. Don't click on the legend text "Total Transactions." Rather, click on one of the red bars in the chart. You should now see the data set highlighted as follows:

Selecting a Data Set on a Chart

 

2.  Once you have selected the Total Transactions column in the chart, you will see the following button in the Type Group section:  Change Chart Type

3. Click on the Change Chart Type button

4. This will bring up the Change Chart Type dialog box. Select the new chart type you'd like to use. In this case, select the Line chart and hit OK.

Change Chart Type Dialog

 

Voila, you've created a chart with two chart types (column and line)!

In fact, you can combine far more than two chart types by repeating the above process with additional data sets, and selecting a different type from the Change Chart Type dialog box.

Chart Plotting Two Data Sets with Bar and Line Chart

While quite cool, our chart is still difficult to analyze because the scale of the Total Transactions is much larger than the scale of the Units Sold, and so we can't gain much insight from the virtually indistinguishable columns. To make the chart easier to read, the Total Transactions should be converted to a secondary axis, thus allowing for the data sets to be scaled differently.  Here's how you convert a data set to a secondary axis.

1. Click the Total Transactions line chart (do this exactly as described in the "Changing One of the Data Sets to a Line Chart" step above).

2. Navigate to the Layout tab on the main menu.

3. In the Current Selection section on the far left, make sure the dropdown selection reads "Series Total Transactions."

4. Click Format Selection (see the image below) to display the Format Data Series dialog box.

Formatting options on the Layout tab

 

5. Click the Secondary Axis button in the Series Options section, and then click Close.

Format Data Series Dialog

You've now successfully added a secondary axis to your chart!  Your chart should look like this:

You have now created a chart that displays your data in way that allows for easy analysis. Now you can do fit-and-finish work to make your combined chart look more professional.

Make Your Chart Legend More Visible

If you move the chart legend to the bottom of your combined chart, it will separate it from the Total Transactions axis and so better highlight it.

1. Click the Chart Layout tab, and then click Legend.

2. Choose Show Legend at Bottom.

Formatting Options for Chart Legend

 

Legend Appearing at the Bottom of the Chart

Change the Secondary Axis Label

We want the Total Transactions axis to display dollar signs since this is the value we are tracking.

1. Right-click the Total Transactions axis label and select Format Axis.

2. Click the Number tab at the left-hand side of the of the Format Axis dialog box.

3. Click Currency from the Category list. If you don't like a decimal place and subsequent zeros, remove the two right-most zeros and the decimal place from the Format Code box.

4. Click Add, and then click Close.

Format Axis Menu

Add Axes Titles

To know precisely what you're tracking, you can add axes titles (labels) to the chart.

1. Go to the Layout tab, and choose Axis Titles.

2. On the list that appears, choose Primary Vertical Axis Title.

3. Then choose whichever option you'd like- in this case, I'll choose Rotated Title

4. Type in the title you'd like. For this scenario, use the title "Units Sold."

5. Do the same for the Secondary Vertical Axis Title

 Adding an Axis Title

Add a Chart Title

Finally, we want to add a descriptive title so it's clear to everyone what the chart is depicting.

1. Go to the Layout tab, and click the Chart Title button.

2. Select a location for the chart title.

Chart Title Creation

And that's all there is to it!  Now you have professional looking chart that clearly displays data for both chart types

A Completed Chart with Consisting of Two Chart Types

 By repeating the steps outlined in this post with additional data sets, you can create even more complex (yet readable) charts.

View the original article here