Creating A "grouped" Bar Chart From A Table In Excel


Answer :

Excel charts work by plotting rows and columns of data, not just a big long row. So arrange your data like this:



Data layout optimized for plotting



Select this range of data, and on the Insert ribbon tab, click Table. It won't insert anything, but it will convert your ordinary range of data into a special data structure known as a Table. Nothing to be scared of, Tables are pretty powerful. The dialog will ask if your range has headers, which it does (Week, A, B, C, Total).



The Table now has special formatting, with a colorful header row and alternating bands of color. It's a little overformatted, but you can select it and choose a less (or more!) formatted style.



Now select the table, or a cell within the table, and insert a column chart.



Table and Chart



If you don't want the total (it might overwhelm the rest of the data, simply select and delete the total columns in the chart, or select only the first four columns of the table before selecting the chart.



Now comes the magic of Tables. If you have a formula somewhere that relies on the whole column in a table, then if you add or remove rows in the table, the formula will update without any effort on your part. These formulas include the Series formulas in the chart. So add a row to the table, and the chart will automatically include the new row of data.  



Expanded Table and Chart



There is a not-so-simple way to do this with normal charts, but the real skill here is to use a PivotChart.



This would first require you to reformat your data into tabular format, basically so there is only one row of headers - your data would look like this:



WEEK # | LETTER | VALUE
-The pivot table will handle totals for you



First you want to select all data and create a pivot table (insert -> pivot table)



Click 'OK' and you will see a blank PivotTable on a new sheet.



Next, you will want to go to "PivotTable Tools -> Options" on the ribbon (It's purple in Office 2010) and click "PivotChart". You'll select the first Bar Chart option and will be greeted by a blank chart.



On the right-hand side of the screen you'll see a list of all your columns by header and four boxes below. Stack your "groups" so that the groups go from highest to lowest level vertically in this, then put the columns whose values you'd like to measure on the chart in the "Values" box.



As for your bonus question, the tabular format of the data you use makes this super simple. Just make the week # 4 next week :)



enter image description here
enter image description hereenter image description here



Comments

Post a Comment

Popular posts from this blog

Converting A String To Int In Groovy

Answer : Use the toInteger() method to convert a String to an Integer , e.g. int value = "99".toInteger() An alternative, which avoids using a deprecated method (see below) is int value = "66" as Integer If you need to check whether the String can be converted before performing the conversion, use String number = "66" if (number.isInteger()) { int value = number as Integer } Deprecation Update In recent versions of Groovy one of the toInteger() methods has been deprecated. The following is taken from org.codehaus.groovy.runtime.StringGroovyMethods in Groovy 2.4.4 /** * Parse a CharSequence into an Integer * * @param self a CharSequence * @return an Integer * @since 1.8.2 */ public static Integer toInteger(CharSequence self) { return Integer.valueOf(self.toString().trim()); } /** * @deprecated Use the CharSequence version * @see #toInteger(CharSequence) */ @Deprecated public static Integer toInteger(String self) { return toInteg
Read more

"Cannot Create Cache Directory /home//.composer/cache/repo/https---packagist.org/, Or Directory Is Not Writable. Proceeding Without Cache"

Answer : if anyone pass through here, this is shorter solution: sudo chown -R $USER $HOME/.composer it seems to me the group information is missing in your command sudo chown -R <user> /home/<user>/.composer/cache/repo/https---packagist.org Shoud be sudo chown -R <user>:<group> /home/<user>/.composer/cache/repo/https---packagist.org But to avoid other permission issues, I would rather advise: sudo chown -R <user>:<group> /home/<user>/.composer/cache (you'll need access to other folders in there) and sudo chown <user>:<group> /home/<user>/.composer To make sure your user has permissions enough on the global composer folder. Mind the missing recursion so the user don't own keys created by root. If you need to find out the group: groups <user>
Read more

Android How Can I Convert A String To A Editable

Answer : As you probably found out, Editable is an interface so you cannot call new Editable() . However an Editable is nothing more than a String with Spannables, so use SpannableStringBuilder: Editable editable = new SpannableStringBuilder("Pass a string here"); If you only want to change the Spannables, never the text itself, you can use a basic SpannableString. Use Editable.Factory.getInstance().newEditable(str) From the android documentation: Returns a new SpannedStringBuilder from the specified CharSequence. You can override this to provide a different kind of Spanned.
Read more