tag:blogger.com,1999:blog-37947375777845838192024-03-12T22:06:35.734-07:00Excel EnthusiastsA weekly blog on all things that are cool in Excel. Material ranges from topics for neophytes to advanced subjects. All ranges of classes at http://www.continuingeducationgroup.net/Bob DeLaMartrehttp://www.blogger.com/profile/03761089085420930697noreply@blogger.comBlogger469125tag:blogger.com,1999:blog-3794737577784583819.post-35776603032484795222018-02-05T08:32:00.000-08:002018-02-05T08:32:51.727-08:00Later, Alligator… <div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjBRj2Wd_OSumjhC5_kSFt8vkGtuVo0orlED0s5yD8dstVDYK5Ca5dLSCgWDQFXybto7IobXlW-xkww7pm34z0YVsdrR3ksF1pozgnCWULipmeXhPiilHBxKh5hPrCesSRjvS71DddQBuyp/s1600/Alligators.png" imageanchor="1" style="clear: left; float: left; margin-bottom: 1em; margin-right: 1em;"><img border="0" data-original-height="507" data-original-width="136" height="200" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjBRj2Wd_OSumjhC5_kSFt8vkGtuVo0orlED0s5yD8dstVDYK5Ca5dLSCgWDQFXybto7IobXlW-xkww7pm34z0YVsdrR3ksF1pozgnCWULipmeXhPiilHBxKh5hPrCesSRjvS71DddQBuyp/s200/Alligators.png" width="53" /></a></div>
<div class="MsoNoSpacing">
<span style="font-family: "Trebuchet MS",sans-serif; font-size: 12.0pt; mso-bidi-font-size: 11.0pt;">At over 9 years old, <b><i>Excel
Enthusiasts</i></b> has been one of the longest running blogs on the Internet.
It is time to move on, however, so if you <b><i>Subscribe</i></b> to this blog on <b><i>Amazon
Kindle</i></b>, <b><span style="color: maroon;">Please
CANCEL YOUR SUBSCRIPTION</span></b> this week!<o:p></o:p></span></div>
<div class="MsoNoSpacing">
<br /></div>
<div class="MsoNoSpacing">
<b><span style="font-family: "Trebuchet MS",sans-serif; font-size: 12.0pt; mso-bidi-font-size: 11.0pt;">Thanks
so much for reading this little blog. You Folks have been Terrific!!<o:p></o:p></span></b></div>
<div class="MsoNoSpacing">
<br /></div>
<div class="MsoNoSpacing">
<span style="font-family: "Trebuchet MS",sans-serif; font-size: 12.0pt; mso-bidi-font-size: 11.0pt;">In closing, let’s say that you are
working on a project where you want a <b><i>list of weekdays</i></b>, but you don’t want
to manually enter them in your column or row. The solution is, of course, <b><i>Autofill!</i></b><o:p></o:p></span></div>
<div class="MsoNoSpacing">
<br /></div>
<div class="MsoNoSpacing">
<span style="font-family: "Trebuchet MS",sans-serif; font-size: 12.0pt; mso-bidi-font-size: 11.0pt;">But how do you do this?<b> </b>Well, you may not have noticed that you
can also use the Autofill tool for <b>Auto-filling Weekdays.</b> Here’s what you do:<br />
<!--[if !supportLineBreakNewLine]--><br />
<!--[endif]--><b><o:p></o:p></b></span></div>
<div class="MsoNoSpacing" style="margin-left: .5in; mso-list: l0 level1 lfo1; text-indent: -.25in;">
<!--[if !supportLists]--><span style="font-family: "Trebuchet MS",sans-serif; font-size: 12.0pt; mso-bidi-font-family: "Trebuchet MS"; mso-bidi-font-size: 11.0pt; mso-fareast-font-family: "Trebuchet MS";">1.<span style="font-family: "Times New Roman"; font-size: 7pt; font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal;">
</span></span><!--[endif]--><span style="font-family: "Trebuchet MS",sans-serif; font-size: 12.0pt; mso-bidi-font-size: 11.0pt;">Enter the starting day into a
cell (<b><i>Monday, Tuesday</i></b>, whatever…)<o:p></o:p></span></div>
<div class="MsoNoSpacing" style="margin-left: .5in; mso-list: l0 level1 lfo1; text-indent: -.25in;">
<!--[if !supportLists]--><span style="font-family: "Trebuchet MS",sans-serif; font-size: 12.0pt; mso-bidi-font-family: "Trebuchet MS"; mso-bidi-font-size: 11.0pt; mso-fareast-font-family: "Trebuchet MS";">2.<span style="font-family: "Times New Roman"; font-size: 7pt; font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal;">
</span></span><!--[endif]--><span style="font-family: "Trebuchet MS",sans-serif; font-size: 12.0pt; mso-bidi-font-size: 11.0pt;">Place you mouse pointer over the
lower-right corner of the cell (the small black square is the “<b><i>handle</i></b>”)
until you see the narrow crosshairs appear<o:p></o:p></span></div>
<div class="MsoNoSpacing" style="margin-left: .5in; mso-list: l0 level1 lfo1; text-indent: -.25in;">
<!--[if !supportLists]--><span style="font-family: "Trebuchet MS",sans-serif; font-size: 12.0pt; mso-bidi-font-family: "Trebuchet MS"; mso-bidi-font-size: 11.0pt; mso-fareast-font-family: "Trebuchet MS";">3.<span style="font-family: "Times New Roman"; font-size: 7pt; font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal;">
</span></span><!--[endif]--><b><i><span style="font-family: "Trebuchet MS",sans-serif; font-size: 12.0pt; mso-bidi-font-size: 11.0pt;">Right-click the handle</span></i></b><span style="font-family: "Trebuchet MS",sans-serif; font-size: 12.0pt; mso-bidi-font-size: 11.0pt;"> and
drag to select the range you want to fill with weekdays<o:p></o:p></span></div>
<div class="MsoNoSpacing" style="margin-left: .5in; mso-list: l0 level1 lfo1; text-indent: -.25in;">
<!--[if !supportLists]--><span style="font-family: "Trebuchet MS",sans-serif; font-size: 12.0pt; mso-bidi-font-family: "Trebuchet MS"; mso-bidi-font-size: 11.0pt; mso-fareast-font-family: "Trebuchet MS";">4.<span style="font-family: "Times New Roman"; font-size: 7pt; font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal;">
</span></span><!--[endif]--><span style="font-family: "Trebuchet MS",sans-serif; font-size: 12.0pt; mso-bidi-font-size: 11.0pt;">When you release the mouse
button, a <b><i>dropdown menu</i></b> will appear<o:p></o:p></span></div>
<div class="MsoNoSpacing" style="margin-left: .5in; mso-list: l0 level1 lfo1; text-indent: -.25in;">
<!--[if !supportLists]--><b><i><span style="font-family: "Trebuchet MS",sans-serif; font-size: 12.0pt; mso-bidi-font-family: "Trebuchet MS"; mso-bidi-font-size: 11.0pt; mso-fareast-font-family: "Trebuchet MS";">5.<span style="font-family: "Times New Roman"; font-size: 7pt; font-stretch: normal; font-style: normal; font-variant-east-asian: normal; font-variant-numeric: normal; font-weight: normal; line-height: normal;">
</span></span></i></b><!--[endif]--><span style="font-family: "Trebuchet MS",sans-serif; font-size: 12.0pt; mso-bidi-font-size: 11.0pt;">Select <b><i>Fill
Weekdays<o:p></o:p></i></b></span></div>
<div class="MsoNoSpacing">
<br /></div>
<div class="MsoNoSpacing">
<b><i><span style="font-family: "Trebuchet MS",sans-serif; font-size: 12.0pt; mso-bidi-font-size: 11.0pt;">That’s It!</span></i></b><span style="font-family: "Trebuchet MS",sans-serif; font-size: 12.0pt; mso-bidi-font-size: 11.0pt;"> A
filled series of Weekdays! This tip works on nearly any version of Excel, so
give it a try some time.</span></div>
<div class="MsoNoSpacing">
<span style="font-family: "Trebuchet MS", sans-serif; font-size: 12pt;"><br /></span></div>
<div class="MsoNoSpacing">
<span style="font-family: "Trebuchet MS", sans-serif; font-size: 12pt;">Later, Alligator!</span><span style="font-family: "Trebuchet MS", sans-serif; font-size: 12pt;"> </span><span style="font-family: "Trebuchet MS", sans-serif; font-size: 12pt;">~Bob</span></div>
Bob DeLaMartrehttp://www.blogger.com/profile/03761089085420930697noreply@blogger.com3tag:blogger.com,1999:blog-3794737577784583819.post-90617182015555544862018-01-25T11:33:00.000-08:002018-01-25T11:33:17.084-08:00Definitions<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEikysdQuvNs-DVCpiEtGU-RQnCSWDFY70uNz0m7nrLrEVcVxNH6_k03khfapPGfaBpQUu1hllrzrnoTygJLypWXASrVD8o9V-bOH6TuFp-a2CFfQhVs7yRX_lGKD44pjRJSDZt66t6a0NbE/s1600/Dictionaries.png" imageanchor="1" style="clear: left; float: left; margin-bottom: 1em; margin-right: 1em;"><img border="0" data-original-height="621" data-original-width="187" height="200" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEikysdQuvNs-DVCpiEtGU-RQnCSWDFY70uNz0m7nrLrEVcVxNH6_k03khfapPGfaBpQUu1hllrzrnoTygJLypWXASrVD8o9V-bOH6TuFp-a2CFfQhVs7yRX_lGKD44pjRJSDZt66t6a0NbE/s200/Dictionaries.png" width="60" /></a></div>
<div class="MsoNoSpacing">
<span style="font-family: "Trebuchet MS", sans-serif; font-size: 12pt;">Defining a</span><b style="font-family: "Trebuchet MS", sans-serif; font-size: 12pt;"> Name </b><span style="font-family: "Trebuchet MS", sans-serif; font-size: 12pt;">for a </span><b style="font-family: "Trebuchet MS", sans-serif; font-size: 12pt;"><i>Constant </i></b><span style="font-family: "Trebuchet MS", sans-serif; font-size: 12pt;">or </span><b style="font-family: "Trebuchet MS", sans-serif; font-size: 12pt;"><i>Formula</i> </b><span style="font-family: "Trebuchet MS", sans-serif; font-size: 12pt;">is an excellent
practice in Excel. It can simplify the use of your Excel workbook, (especially
by others who may inherit the maintenance of the workbook), and add a touch of
elegance as a bonus. </span></div>
<div class="MsoNoSpacing">
<span style="font-family: "Trebuchet MS",sans-serif; font-size: 12.0pt; mso-bidi-font-size: 11.0pt;"><br /></span></div>
<div class="MsoNoSpacing">
<span style="font-family: "Trebuchet MS",sans-serif; font-size: 12.0pt; mso-bidi-font-size: 11.0pt;">This “<i>elegance</i>”
is achieved by using descriptive <b><i>Names</i></b>, rather than cold cell
references.<o:p></o:p></span></div>
<div class="MsoNoSpacing">
<br /></div>
<div class="MsoNoSpacing">
<span style="font-family: "Trebuchet MS",sans-serif; font-size: 12.0pt; mso-bidi-font-size: 11.0pt;">Defining a Name Is Very Straightforward:<o:p></o:p></span></div>
<div class="MsoNoSpacing" style="margin-left: .5in; mso-list: l0 level1 lfo1; text-indent: -.25in;">
<!--[if !supportLists]--><span style="font-family: "Trebuchet MS",sans-serif; font-size: 12.0pt; mso-bidi-font-family: "Trebuchet MS"; mso-bidi-font-size: 11.0pt; mso-fareast-font-family: "Trebuchet MS";">1.<span style="font-family: "Times New Roman"; font-size: 7pt; font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal;">
</span></span><!--[endif]--><span style="font-family: "Trebuchet MS",sans-serif; font-size: 12.0pt; mso-bidi-font-size: 11.0pt;">Select the<b> Formulas</b> ribbon and then click on <b>Define Name</b><o:p></o:p></span></div>
<div class="MsoNoSpacing" style="margin-left: .5in; mso-list: l0 level1 lfo1; text-indent: -.25in;">
<!--[if !supportLists]--><span style="font-family: "Trebuchet MS",sans-serif; font-size: 12.0pt; mso-bidi-font-family: "Trebuchet MS"; mso-bidi-font-size: 11.0pt; mso-fareast-font-family: "Trebuchet MS";">2.<span style="font-family: "Times New Roman"; font-size: 7pt; font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal;">
</span></span><!--[endif]--><span style="font-family: "Trebuchet MS",sans-serif; font-size: 12.0pt; mso-bidi-font-size: 11.0pt;">Type the <b>Name </b>and <b>Scope</b>
(typically the workbook…)<o:p></o:p></span></div>
<div class="MsoNoSpacing" style="margin-left: 1.0in; mso-list: l0 level2 lfo1; text-indent: -.25in;">
<!--[if !supportLists]--><span style="font-family: "Trebuchet MS",sans-serif; font-size: 12.0pt; mso-bidi-font-family: "Trebuchet MS"; mso-bidi-font-size: 11.0pt; mso-fareast-font-family: "Trebuchet MS";">a.<span style="font-family: "Times New Roman"; font-size: 7pt; font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal;"> </span></span><!--[endif]--><span style="font-family: "Trebuchet MS",sans-serif; font-size: 12.0pt; mso-bidi-font-size: 11.0pt;">Inserting
a <b>Comment</b> is optional, but
recommended in many cases<o:p></o:p></span></div>
<div class="MsoNoSpacing" style="margin-left: .5in; mso-list: l0 level1 lfo1; text-indent: -.25in;">
<!--[if !supportLists]--><span style="font-family: "Trebuchet MS",sans-serif; font-size: 12.0pt; mso-bidi-font-family: "Trebuchet MS"; mso-bidi-font-size: 11.0pt; mso-fareast-font-family: "Trebuchet MS";">3.<span style="font-family: "Times New Roman"; font-size: 7pt; font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal;">
</span></span><!--[endif]--><span style="font-family: "Trebuchet MS",sans-serif; font-size: 12.0pt; mso-bidi-font-size: 11.0pt;">in the <b>Refers To</b> dialog box, type the reference to the constant or formula
and click <b>OK</b><o:p></o:p></span></div>
<div class="MsoNoSpacing">
<span style="font-family: "Trebuchet MS", sans-serif; font-size: 12pt;"><br /></span></div>
<div class="MsoNoSpacing">
<span style="font-family: "Trebuchet MS", sans-serif; font-size: 12pt;">Although this practice of </span><b style="font-family: "Trebuchet MS", sans-serif; font-size: 12pt;">Defining Names</b><span style="font-family: "Trebuchet MS", sans-serif; font-size: 12pt;"> may not seem like a </span><b style="font-family: "Trebuchet MS", sans-serif; font-size: 12pt;"><i>Big
Deal</i></b><span style="font-family: "Trebuchet MS", sans-serif; font-size: 12pt;"> in your Excel life, it can add an easily accomplished additional
touch of professionalism to your work. Give it a try!</span></div>
Bob DeLaMartrehttp://www.blogger.com/profile/03761089085420930697noreply@blogger.com0tag:blogger.com,1999:blog-3794737577784583819.post-39626365392492965512018-01-17T12:03:00.001-08:002018-01-17T12:03:58.073-08:00Date-Related Data<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiAd8-HPoOSHL6v6GVJ1PD3yZyfe-I-1W9qB9FOjM2nfK6igvddo4LF_uOH8FvA2zPgOHSZq25kOWgMCoi-WLscmy8encn62foLsJiEuCGehmGLjoSOZ1flwVcHjwx53eNdCgLSZrpz_2-P/s1600/January17.png" imageanchor="1" style="clear: left; float: left; margin-bottom: 1em; margin-right: 1em;"><img border="0" data-original-height="616" data-original-width="176" height="200" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiAd8-HPoOSHL6v6GVJ1PD3yZyfe-I-1W9qB9FOjM2nfK6igvddo4LF_uOH8FvA2zPgOHSZq25kOWgMCoi-WLscmy8encn62foLsJiEuCGehmGLjoSOZ1flwVcHjwx53eNdCgLSZrpz_2-P/s200/January17.png" width="56" /></a></div>
<div class="MsoNoSpacing">
<span style="font-family: "Trebuchet MS",sans-serif; font-size: 12.0pt; mso-bidi-font-size: 11.0pt;">As many of us know, Excel stores dates
as the number of days elapsed since January 1, 1900, and times are stored as an
<b><i>additional
fraction</i></b> of the date number. Cells will, obviously, display the number
or date and/or time, depending on the cell format that you choose.<o:p></o:p></span></div>
<div class="MsoNoSpacing">
<br /></div>
<div class="MsoNoSpacing">
<span style="font-family: "Trebuchet MS",sans-serif; font-size: 12.0pt; mso-bidi-font-size: 11.0pt;">What is cool, of course, is that you can
use <b>Date Functions </b>to perform date
calculations and extract the proper date-related data.<o:p></o:p></span></div>
<div class="MsoNoSpacing">
<br /></div>
<div class="MsoNoSpacing">
<span style="font-family: "Trebuchet MS",sans-serif; font-size: 12.0pt; mso-bidi-font-size: 11.0pt;">The following are <b><i>Five</i></b> of the <b><i>Date-related
Functions</i></b> that are useful to nearly any Excel user:<o:p></o:p></span></div>
<div class="MsoNoSpacing">
<br /></div>
<div class="MsoNoSpacing" style="margin-left: .5in; mso-list: l1 level1 lfo2; text-indent: -.25in;">
<!--[if !supportLists]--><b><span style="font-family: "Trebuchet MS",sans-serif; font-size: 12.0pt; mso-bidi-font-family: "Trebuchet MS"; mso-bidi-font-size: 11.0pt; mso-fareast-font-family: "Trebuchet MS";">1.<span style="font-family: "Times New Roman"; font-size: 7pt; font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; font-weight: normal; line-height: normal;"> </span></span></b><!--[endif]--><b><span style="font-family: "Trebuchet MS",sans-serif; font-size: 12.0pt; mso-bidi-font-size: 11.0pt;">=DATE(year, month, day)<o:p></o:p></span></b></div>
<div class="MsoNoSpacing" style="margin-left: 1.0in; mso-list: l0 level2 lfo1; text-indent: -.25in;">
<!--[if !supportLists]--><span style="font-family: "Courier New"; font-size: 12.0pt; mso-bidi-font-size: 11.0pt; mso-fareast-font-family: "Courier New";">o<span style="font-family: "Times New Roman"; font-size: 7pt; font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal;">
</span></span><!--[endif]--><span style="font-family: "Trebuchet MS",sans-serif; font-size: 12.0pt; mso-bidi-font-size: 11.0pt;">This function returns the Excel
date number for the date indicated by the year, month, and day<o:p></o:p></span></div>
<div class="MsoNoSpacing" style="margin-left: .5in; mso-list: l0 level1 lfo1; text-indent: -.25in;">
<!--[if !supportLists]--><span style="font-family: "Trebuchet MS",sans-serif; font-size: 12.0pt; mso-bidi-font-family: "Trebuchet MS"; mso-bidi-font-size: 11.0pt; mso-fareast-font-family: "Trebuchet MS";">2.<span style="font-family: "Times New Roman"; font-size: 7pt; font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal;">
</span></span><!--[endif]--><b><span style="font-family: "Trebuchet MS",sans-serif; font-size: 12.0pt; mso-bidi-font-size: 11.0pt;">=YEAR(date)</span></b><span style="font-family: "Trebuchet MS",sans-serif; font-size: 12.0pt; mso-bidi-font-size: 11.0pt;">
or <b>=MONTH(date)</b> or <b>=DAY(date)</b><o:p></o:p></span></div>
<div class="MsoNoSpacing" style="margin-left: 1.0in; mso-list: l0 level2 lfo1; text-indent: -.25in;">
<!--[if !supportLists]--><span style="font-family: "Courier New"; font-size: 12.0pt; mso-bidi-font-size: 11.0pt; mso-fareast-font-family: "Courier New";">o<span style="font-family: "Times New Roman"; font-size: 7pt; font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal;">
</span></span><!--[endif]--><span style="font-family: "Trebuchet MS",sans-serif; font-size: 12.0pt; mso-bidi-font-size: 11.0pt;">Returns the year, month, or day
of the date<o:p></o:p></span></div>
<div class="MsoNoSpacing" style="margin-left: .5in; mso-list: l0 level1 lfo1; text-indent: -.25in;">
<!--[if !supportLists]--><b><span style="font-family: "Trebuchet MS",sans-serif; font-size: 12.0pt; mso-bidi-font-family: "Trebuchet MS"; mso-bidi-font-size: 11.0pt; mso-fareast-font-family: "Trebuchet MS";">3.<span style="font-family: "Times New Roman"; font-size: 7pt; font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; font-weight: normal; line-height: normal;"> </span></span></b><!--[endif]--><b><span style="font-family: "Trebuchet MS",sans-serif; font-size: 12.0pt; mso-bidi-font-size: 11.0pt;">=TODAY()<o:p></o:p></span></b></div>
<div class="MsoNoSpacing" style="margin-left: 1.0in; mso-list: l0 level2 lfo1; text-indent: -.25in;">
<!--[if !supportLists]--><span style="font-family: "Courier New"; font-size: 12.0pt; mso-bidi-font-size: 11.0pt; mso-fareast-font-family: "Courier New";">o<span style="font-family: "Times New Roman"; font-size: 7pt; font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal;">
</span></span><!--[endif]--><span style="font-family: "Trebuchet MS",sans-serif; font-size: 12.0pt; mso-bidi-font-size: 11.0pt;">Returns the <b><i>current date</i></b> (as
calibrated in the computer system being used)<o:p></o:p></span></div>
<div class="MsoNoSpacing" style="margin-left: .5in; mso-list: l0 level1 lfo1; text-indent: -.25in;">
<!--[if !supportLists]--><b><span style="font-family: "Trebuchet MS",sans-serif; font-size: 12.0pt; mso-bidi-font-family: "Trebuchet MS"; mso-bidi-font-size: 11.0pt; mso-fareast-font-family: "Trebuchet MS";">4.<span style="font-family: "Times New Roman"; font-size: 7pt; font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; font-weight: normal; line-height: normal;"> </span></span></b><!--[endif]--><b><span style="font-family: "Trebuchet MS",sans-serif; font-size: 12.0pt; mso-bidi-font-size: 11.0pt;">=WEEKDAY(date, start weekday)<o:p></o:p></span></b></div>
<div class="MsoNoSpacing" style="margin-left: 1.0in; mso-list: l0 level2 lfo1; text-indent: -.25in;">
<!--[if !supportLists]--><span style="font-family: "Courier New"; font-size: 12.0pt; mso-bidi-font-size: 11.0pt; mso-fareast-font-family: "Courier New";">o<span style="font-family: "Times New Roman"; font-size: 7pt; font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal;">
</span></span><!--[endif]--><span style="font-family: "Trebuchet MS",sans-serif; font-size: 12.0pt; mso-bidi-font-size: 11.0pt;">Returns the week’s <b><i>day
number</i></b> for the date specified<o:p></o:p></span></div>
<div class="MsoNoSpacing" style="margin-left: .5in; mso-list: l0 level1 lfo1; text-indent: -.25in;">
<!--[if !supportLists]--><b><span style="font-family: "Trebuchet MS",sans-serif; font-size: 12.0pt; mso-bidi-font-family: "Trebuchet MS"; mso-bidi-font-size: 11.0pt; mso-fareast-font-family: "Trebuchet MS";">5.<span style="font-family: "Times New Roman"; font-size: 7pt; font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; font-weight: normal; line-height: normal;"> </span></span></b><!--[endif]--><b><span style="font-family: "Trebuchet MS",sans-serif; font-size: 12.0pt; mso-bidi-font-size: 11.0pt;">=NETWORKDAYs(start, end,
holidays)<o:p></o:p></span></b></div>
<div class="MsoNoSpacing" style="margin-left: 1.0in; mso-list: l0 level2 lfo1; text-indent: -.25in;">
<!--[if !supportLists]--><span style="font-family: "Courier New"; font-size: 12.0pt; mso-bidi-font-size: 11.0pt; mso-fareast-font-family: "Courier New";">o<span style="font-family: "Times New Roman"; font-size: 7pt; font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal;">
</span></span><!--[endif]--><span style="font-family: "Trebuchet MS",sans-serif; font-size: 12.0pt; mso-bidi-font-size: 11.0pt;">Counts number of days between the
<b><i>start</i></b>
and <b><i>end</i></b>
dates, excluding Saturdays Sundays and holidays<o:p></o:p></span></div>
<div class="MsoNoSpacing">
<span style="font-family: "Trebuchet MS", sans-serif; font-size: 12pt;"><br /></span></div>
<div class="MsoNoSpacing">
<span style="font-family: "Trebuchet MS", sans-serif; font-size: 12pt;">Handling </span><b style="font-family: "Trebuchet MS", sans-serif; font-size: 12pt;"><i>Dates</i></b><span style="font-family: "Trebuchet MS", sans-serif; font-size: 12pt;"> and </span><b style="font-family: "Trebuchet MS", sans-serif; font-size: 12pt;"><i>Time</i></b><span style="font-family: "Trebuchet MS", sans-serif; font-size: 12pt;">
in Excel can be a bit daunting. If you take a few minutes and master the
foregoing </span><b style="font-family: "Trebuchet MS", sans-serif; font-size: 12pt;"><i>Five Functions</i></b><span style="font-family: "Trebuchet MS", sans-serif; font-size: 12pt;">, however, it can make your Excel life easier.</span></div>
Bob DeLaMartrehttp://www.blogger.com/profile/03761089085420930697noreply@blogger.com0tag:blogger.com,1999:blog-3794737577784583819.post-76006066692305602682018-01-09T12:10:00.000-08:002018-01-09T12:10:13.510-08:00A Small Excel Annoyance<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjJUp6aLOV1ewuTiv3hNOnYtQ1q6ah2JQ9IlB542V344QzlT_v3dIr4B_3gPmo7MKNc67-UNrPq8Lhb5tgXOsFGLxE9AXi5NAPBA9l4_ki85krlfbWXl4VFJABmtfWnJiG6oumMqP65ZoZh/s1600/AnnoyedCat.png" imageanchor="1" style="clear: left; float: left; margin-bottom: 1em; margin-right: 1em;"><img border="0" data-original-height="542" data-original-width="211" height="200" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjJUp6aLOV1ewuTiv3hNOnYtQ1q6ah2JQ9IlB542V344QzlT_v3dIr4B_3gPmo7MKNc67-UNrPq8Lhb5tgXOsFGLxE9AXi5NAPBA9l4_ki85krlfbWXl4VFJABmtfWnJiG6oumMqP65ZoZh/s200/AnnoyedCat.png" width="77" /></a></div>
<div class="MsoNoSpacing">
<span style="font-family: "Trebuchet MS",sans-serif; font-size: 12.0pt; mso-bidi-font-size: 11.0pt;">Let’s face it, there are some small annoyances
within many of the software programs we use. This is, of course, also true of a
few in Microsoft Excel.<o:p></o:p></span></div>
<div class="MsoNoSpacing">
<br /></div>
<div class="MsoNoSpacing">
<span style="font-family: "Trebuchet MS",sans-serif; font-size: 12.0pt; mso-bidi-font-size: 11.0pt;">Take for instance the way Excel changes
the <b><i>column
widths</i></b> when you refresh the data in one of your Pivot Tables. You’ve got
your table looking smart and professional, you refresh your data and, <b><i>Bamm</i></b>,
it’s a bit of a mess!<o:p></o:p></span></div>
<div class="MsoNoSpacing">
<br /></div>
<div class="MsoNoSpacing">
<span style="font-family: "Trebuchet MS",sans-serif; font-size: 12.0pt; mso-bidi-font-size: 11.0pt;">So how do you maintain your chosen
column width without having to reformat after every refresh? There are a few
good ways of doing this, but the easiest and most effective is to do the following:<o:p></o:p></span></div>
<div class="MsoNoSpacing">
<br /></div>
<div class="MsoNoSpacing" style="margin-left: .5in; mso-list: l0 level1 lfo1; text-indent: -.25in;">
<!--[if !supportLists]--><span style="border: none windowtext 1.0pt; font-family: "Trebuchet MS",sans-serif; font-size: 12.0pt; mso-bidi-font-family: "Trebuchet MS"; mso-bidi-font-size: 11.0pt; mso-border-alt: none windowtext 0in; mso-fareast-font-family: "Trebuchet MS"; padding: 0in;">1.<span style="font-family: "Times New Roman"; font-size: 7pt; font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal;">
</span></span><!--[endif]--><span style="border: none windowtext 1.0pt; font-family: "Trebuchet MS",sans-serif; font-size: 12.0pt; mso-bidi-font-size: 11.0pt; mso-border-alt: none windowtext 0in; padding: 0in;">Right-click your table and go to
<b><i>PivotTable
Options</i></b><o:p></o:p></span></div>
<div class="MsoNoSpacing" style="margin-left: .5in; mso-list: l0 level1 lfo1; text-indent: -.25in;">
<!--[if !supportLists]--><span style="font-family: "Trebuchet MS",sans-serif; font-size: 12.0pt; mso-bidi-font-family: "Trebuchet MS"; mso-bidi-font-size: 11.0pt; mso-fareast-font-family: "Trebuchet MS";">2.<span style="font-family: "Times New Roman"; font-size: 7pt; font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal;">
</span></span><!--[endif]--><span style="border: none windowtext 1.0pt; font-family: "Trebuchet MS",sans-serif; font-size: 12.0pt; mso-bidi-font-size: 11.0pt; mso-border-alt: none windowtext 0in; padding: 0in;">Click on the <b><i>Layout
& Format</i></b> tab</span><span style="font-family: "Trebuchet MS",sans-serif; font-size: 12.0pt; mso-bidi-font-size: 11.0pt;"><o:p></o:p></span></div>
<div class="MsoNoSpacing" style="margin-left: .5in; mso-list: l0 level1 lfo1; text-indent: -.25in;">
<!--[if !supportLists]--><span style="font-family: "Trebuchet MS",sans-serif; font-size: 12.0pt; mso-bidi-font-family: "Trebuchet MS"; mso-bidi-font-size: 11.0pt; mso-fareast-font-family: "Trebuchet MS";">3.<span style="font-family: "Times New Roman"; font-size: 7pt; font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal;">
</span></span><!--[endif]--><span style="border: none windowtext 1.0pt; font-family: "Trebuchet MS",sans-serif; font-size: 12.0pt; mso-bidi-font-size: 11.0pt; mso-border-alt: none windowtext 0in; padding: 0in;">In the <b><i>Format </i></b>grouping at the bottom,
remove the checkmark from Autofit column width on update</span></div>
<br />
<div class="MsoNoSpacing">
<span style="font-family: "Trebuchet MS",sans-serif; font-size: 12.0pt; mso-bidi-font-size: 11.0pt;">Simple as that. Oftentimes, the best solutions
are modest and elegant. Give it a try, and get rid of one of the small
annoyances in your Excel work.<o:p></o:p></span></div>
Bob DeLaMartrehttp://www.blogger.com/profile/03761089085420930697noreply@blogger.com1tag:blogger.com,1999:blog-3794737577784583819.post-86022138220095316682018-01-03T08:26:00.002-08:002018-01-03T08:26:46.899-08:00Turn the Tables<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEi4-RI0dkOc3sqqyZ_y9haoVuJaE4uZKw028KQfxyhvY0qf8iLFxXLlum_WWyMkRoMT-S5lUZKFqkPLS2OUrgARPQr7WI35OVvrwd6yKWuGzPosAW-hs5aycuI-rfI8_YUY-LRdWGLvSPam/s1600/TurnTables.png" imageanchor="1" style="clear: left; float: left; margin-bottom: 1em; margin-right: 1em;"><img border="0" data-original-height="528" data-original-width="157" height="200" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEi4-RI0dkOc3sqqyZ_y9haoVuJaE4uZKw028KQfxyhvY0qf8iLFxXLlum_WWyMkRoMT-S5lUZKFqkPLS2OUrgARPQr7WI35OVvrwd6yKWuGzPosAW-hs5aycuI-rfI8_YUY-LRdWGLvSPam/s200/TurnTables.png" width="59" /></a></div>
<div class="MsoNoSpacing">
<span style="font-family: "Trebuchet MS",sans-serif; font-size: 12.0pt;"><br /></span></div>
<div class="MsoNoSpacing">
<span style="font-family: "Trebuchet MS",sans-serif; font-size: 12.0pt;">Excel
<b>Tables </b>are, without a doubt,
powerful tools for data analysis. If you are not familiar with <b><i>Tables</i></b>,
or you just haven’t gotten around to using these comparatively new gizmos very
much, I encourage you to take a closer look.<o:p></o:p></span></div>
<div class="MsoNoSpacing">
<br /></div>
<div class="MsoNoSpacing">
<b><span style="font-family: "Trebuchet MS",sans-serif; font-size: 12.0pt;">Here are Three of
My Favorite Benefits of Excel tables:<o:p></o:p></span></b></div>
<div class="MsoNoSpacing">
<br /></div>
<div class="MsoNoSpacing" style="margin-left: .25in; mso-list: l0 level1 lfo1; text-indent: -.25in;">
<!--[if !supportLists]--><b><span style="font-family: "Trebuchet MS",sans-serif; font-size: 12.0pt; mso-bidi-font-family: "Trebuchet MS"; mso-fareast-font-family: "Trebuchet MS";">1.<span style="font-family: "Times New Roman"; font-size: 7pt; font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; font-weight: normal; line-height: normal;"> </span></span></b><!--[endif]--><b><span style="font-family: "Trebuchet MS",sans-serif; font-size: 12.0pt;">They’re a Breeze to Create<o:p></o:p></span></b></div>
<div class="MsoNoSpacing" style="margin-left: .25in;">
<span style="font-family: "Trebuchet MS",sans-serif; font-size: 12.0pt;">There are two essential ways to create
an Excel Table: The first is to select your data, go to the <b><i>Insert</i></b>
ribbon, click on the<b><i> Tables </i></b>down-arrow and choose (are you ready for this?) <b>Tables!</b> Okay, that’s cool, but the way
the <b><i>Cool
Kids in School</i></b> do it is to simply select your data and click <b>Ctrl + T</b>. <b><i>Bamm</i></b>, Done!<o:p></o:p></span></div>
<div class="MsoNoSpacing">
<br /></div>
<div class="MsoNoSpacing" style="margin-left: .25in; mso-list: l0 level1 lfo1; text-indent: -.25in;">
<!--[if !supportLists]--><b><span style="font-family: "Trebuchet MS",sans-serif; font-size: 12.0pt; mso-bidi-font-family: "Trebuchet MS"; mso-fareast-font-family: "Trebuchet MS";">2.<span style="font-family: "Times New Roman"; font-size: 7pt; font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; font-weight: normal; line-height: normal;"> </span></span></b><!--[endif]--><b><span style="font-family: "Trebuchet MS",sans-serif; font-size: 12.0pt;">Slice Those Tables!<o:p></o:p></span></b></div>
<div class="MsoNoSpacing" style="margin-left: .25in;">
<b><span style="font-family: "Trebuchet MS",sans-serif; font-size: 12.0pt;">Slicers
</span></b><span style="font-family: "Trebuchet MS",sans-serif; font-size: 12.0pt;">are
another one of the comparatively new tools in Excel that should not be
overlooked. They are particularly effective at <b><i>visualizing your data</i></b>
which, is a best practice in getting your message across without unnecessarily
pouring through the numbers. Here’s what you do: Selector table, go to the <b><i>Design</i></b><i> </i>ribbon, and select <b><i>Insert Slicer</i></b>. <b><i>Nothing
to it!</i></b><o:p></o:p></span></div>
<div class="MsoNoSpacing" style="margin-left: .25in;">
<br /></div>
<div class="MsoNoSpacing" style="margin-left: .25in; mso-list: l0 level1 lfo1; text-indent: -.25in;">
<!--[if !supportLists]--><b><span style="font-family: "Trebuchet MS",sans-serif; font-size: 12.0pt; mso-bidi-font-family: "Trebuchet MS"; mso-fareast-font-family: "Trebuchet MS";">3.<span style="font-family: "Times New Roman"; font-size: 7pt; font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; font-weight: normal; line-height: normal;"> </span></span></b><!--[endif]--><b><span style="font-family: "Trebuchet MS",sans-serif; font-size: 12.0pt;">Create Self-Expanding Charts<o:p></o:p></span></b></div>
<div class="MsoNoSpacing" style="margin-left: .25in;">
<span style="font-family: "Trebuchet MS",sans-serif; font-size: 12.0pt;">If you are like most Excel users, you
have spent a great deal of time <b><i>manually </i></b>updating your chart data
ranges when making repetitive reports. This is a total drag and needless work
if you use <b><i>Tables</i></b>. If you set up your chart based upon data in<b><i>
Table</i></b> format, any additions that you make to the data within the table
will <b><i>automatically
</i></b>be reflected in the chart. Now I ask you, <b><i>How Cool Is That?</i></b></span><span style="font-family: "Trebuchet MS", sans-serif; font-size: 12pt;"> </span></div>
<div class="MsoNoSpacing" style="margin-left: .25in;">
<b><span style="font-family: "Trebuchet MS",sans-serif; font-size: 12.0pt;"><br /></span></b></div>
<div class="MsoNoSpacing" style="margin-left: .25in;">
<b><span style="font-family: "Trebuchet MS",sans-serif; font-size: 12.0pt;">Excel Tables.</span></b><span style="font-family: "Trebuchet MS", sans-serif; font-size: 12pt;"> Make a New
Year’s resolution to integrate them into your daily Excel routine. <b><i>Happy
New Year’s, All!</i></b></span></div>
Bob DeLaMartrehttp://www.blogger.com/profile/03761089085420930697noreply@blogger.com2tag:blogger.com,1999:blog-3794737577784583819.post-85454133025676768222017-12-29T11:45:00.000-08:002017-12-29T11:49:27.470-08:00Take the Indirect Route<div class="separator" style="clear: both; text-align: center;">
</div>
<br />
<div class="separator" style="clear: both; text-align: center;">
</div>
<div class="separator" style="clear: both; text-align: center;">
</div>
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEh_EjUXr5Vid2OqGNUh5396TcLIH1X1vwju-NOSdIs4kJ4nUt2bpHJjw-y32XeBx-w1mg0vQ2AeATFr69FIFjo4x0qOtr85aPyvl4Yhm995BizgTVWoVE0Gji2kZukbsZJexWX9jxobDSde/s1600/IndirectSign.png" imageanchor="1" style="clear: left; float: left; margin-bottom: 1em; margin-right: 1em;"><img border="0" data-original-height="621" data-original-width="187" height="200" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEh_EjUXr5Vid2OqGNUh5396TcLIH1X1vwju-NOSdIs4kJ4nUt2bpHJjw-y32XeBx-w1mg0vQ2AeATFr69FIFjo4x0qOtr85aPyvl4Yhm995BizgTVWoVE0Gji2kZukbsZJexWX9jxobDSde/s200/IndirectSign.png" width="60" /></a><b><span style="font-family: "trebuchet ms" , sans-serif; font-size: 12.0pt; line-height: 115%;">Named Ranges</span></b><span style="font-family: "trebuchet ms" , sans-serif; font-size: 12.0pt; line-height: 115%;"> can be a terrific boon to
the efficiency and ease-of-use of your Excel workbooks. Although using named
ranges is one of my favorite Best Practices, curious problems may arise when
referring to these effective shortcuts. To obtain true mastery in this regard,
however, the savvy Excel Guru can employ the humble <b>Indirect Function! </b>
<br />
<br />
Used in its simplest form, the <b>Indirect
Function </b>can simply take the <b><i>Name</i></b> in the cell it is referencing
and return the <b><i>Named Range</i></b> that it refers to. Using the<b> </b>Indirect Function along with drop-down
boxes created with <b>Validation</b>, is a very user-friendly way of making <b><i>Interactive
Reports.</i></b><br />
</span><br />
<span style="font-family: "trebuchet ms" , sans-serif; font-size: 12.0pt; line-height: 115%;"></span><br />
<span style="font-family: "trebuchet ms" , sans-serif; font-size: 12.0pt; line-height: 115%;"></span><br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjBIC0KjTYnC0OxZdvYEXNfhP-K8apmp6bpXImRXW4166PtQR-7DaolMNBmqgyZUxTu2PrpMjeW6NL2mkeEQwl5rIPgpQlzHrfGh3W7GWhPRxhgq8XS6qIYGXKLjkHInbUz6D5WOZCdxTyK/s1600/Indirect+Example.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="524" data-original-width="740" height="226" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjBIC0KjTYnC0OxZdvYEXNfhP-K8apmp6bpXImRXW4166PtQR-7DaolMNBmqgyZUxTu2PrpMjeW6NL2mkeEQwl5rIPgpQlzHrfGh3W7GWhPRxhgq8XS6qIYGXKLjkHInbUz6D5WOZCdxTyK/s320/Indirect+Example.png" width="320" /></a></div>
<span style="font-family: "trebuchet ms" , sans-serif; font-size: 12.0pt; line-height: 115%;"><b>Create a Spreadsheet Like the One Shown Above, and then: </b><br />
<br />
<b> 1)</b> Create your <b><i>Named Ranges</i></b> by selecting each of
your ranges and typing the new Name in the <b>Name Box</b> on the upper-left
corner of your worksheet. In the above example graphic, Name each of the
monthly rows for the <b><i>Units</i></b> shown. For instance, for December,
select <b>C15:G15</b> and name it<b><i>
December </i></b>(no sense in making this any more difficult than that…).<br />
<br />
<b>2)</b> In cell <b>D18</b> insert the
formula “<b>=SUM(INDIRECT(C18))</b>”. The <b>Indirect </b>Function reads the
name of the month you choose with the drop-down box in <b>C18</b> and the
formula sums up the total for the units.<br />
<br />
Using the<b> Indirect Function</b> along with <b>Validation</b> it is just one
example of how to use this powerful tool in Excel. I wish you all a very <b><i>Happy
and Successful New Year</i></b></span>Bob DeLaMartrehttp://www.blogger.com/profile/03761089085420930697noreply@blogger.com2tag:blogger.com,1999:blog-3794737577784583819.post-76179399924311187792017-12-20T12:08:00.000-08:002017-12-20T12:08:01.327-08:00Funnel Charts (not cakes…)<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhcwXianfs_zmIV-JrmTyKI9IfPGwDsmxr9jJsII8uOdCcRGfANMW2xeNpimCn9Zh30C582VF0CI14dGXZqgXMiuWLyOa5Kzd3NL0z0UizRoOzDmp0T0MhFcu1PqxmzQXk2xJgRSzvjJTkS/s1600/Funnel+Chart.png" imageanchor="1" style="clear: left; float: left; margin-bottom: 1em; margin-right: 1em;"><img border="0" data-original-height="488" data-original-width="211" height="200" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhcwXianfs_zmIV-JrmTyKI9IfPGwDsmxr9jJsII8uOdCcRGfANMW2xeNpimCn9Zh30C582VF0CI14dGXZqgXMiuWLyOa5Kzd3NL0z0UizRoOzDmp0T0MhFcu1PqxmzQXk2xJgRSzvjJTkS/s200/Funnel+Chart.png" width="86" /></a></div>
<br />
<div class="MsoNoSpacing">
<b><span style="color: #333333; font-family: "Trebuchet MS",sans-serif; font-size: 12.0pt;">Funnel Charts</span></b><span style="color: #333333; font-family: "Trebuchet MS",sans-serif; font-size: 12.0pt; mso-bidi-font-weight: bold;">,
(Not to be confused with<b> <i>Funnel Cakes</i>,
</b>which are something completely different…), are one of the newer features
in Excel that deserve a closer look</span><span style="color: #333333; font-family: "Trebuchet MS",sans-serif; font-size: 12.0pt;">. This new chart that appeared
in Excel for the first time last year, is excellent for illustrating processes
that incrementally become more focused as each stage is completed. Since I have
extensive experience in the insurance industry, I am showing how business “<b><i>Funnels</i></b>”
into an insurance company. <o:p></o:p></span></div>
<div class="MsoNoSpacing">
<br /></div>
<div class="MsoNoSpacing">
<span style="color: #333333; font-family: "Trebuchet MS",sans-serif; font-size: 12.0pt;">You will have to have the latest version of Excel, of course, but
it is a good idea to frequently update anyway…</span></div>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhNFAgP-yAuS1UhNBM2-8N2YRCYeUQDQlv0_F2S13CC2OdgnXtIKu2YLBbOmKw_xyMwfaDw5p9Npeqqc02mDs4gI8GbUMwa2GDLs0MzN3ui_v1lSrV4Gq1-tjQqEmkN1X3Z8yw3xpX37-1J/s1600/Funnel+Chart+2.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="455" data-original-width="1224" height="118" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhNFAgP-yAuS1UhNBM2-8N2YRCYeUQDQlv0_F2S13CC2OdgnXtIKu2YLBbOmKw_xyMwfaDw5p9Npeqqc02mDs4gI8GbUMwa2GDLs0MzN3ui_v1lSrV4Gq1-tjQqEmkN1X3Z8yw3xpX37-1J/s320/Funnel+Chart+2.png" width="320" /></a></div>
<div class="MsoNoSpacing">
<b><span style="color: #333333; font-family: "Trebuchet MS",sans-serif; font-size: 12.0pt;">With reference to the Funnel Chart above, here are the Simple
Steps:</span></b></div>
<div class="MsoNoSpacing" style="margin-left: .25in; mso-list: l0 level1 lfo1; text-indent: -.25in;">
<!--[if !supportLists]--><span style="color: #333333; font-family: "Trebuchet MS",sans-serif; font-size: 12.0pt; mso-bidi-font-family: "Trebuchet MS"; mso-bidi-font-weight: bold; mso-fareast-font-family: "Trebuchet MS";">1.<span style="font-family: "Times New Roman"; font-size: 7pt; font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal;"> </span></span><!--[endif]--><span style="color: #333333; font-family: "Trebuchet MS",sans-serif; font-size: 12.0pt;">As
in the illustration (which you may have to enlarge for best viewing), use one
column for the <b><i>Process</i></b> steps and the other for
the <b><i>Amounts</i><o:p></o:p></b></span></div>
<div class="MsoNoSpacing" style="margin-left: .25in; mso-list: l0 level1 lfo1; text-indent: -.25in;">
<!--[if !supportLists]--><span style="color: #333333; font-family: "Trebuchet MS",sans-serif; font-size: 12.0pt; mso-bidi-font-family: "Trebuchet MS"; mso-bidi-font-weight: bold; mso-fareast-font-family: "Trebuchet MS";">2.<span style="font-family: "Times New Roman"; font-size: 7pt; font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal;"> </span></span><!--[endif]--><span style="color: #333333; font-family: "Trebuchet MS",sans-serif; font-size: 12.0pt;">Select
the <b>Data</b> in the simple table you have created<b><o:p></o:p></b></span></div>
<div class="MsoNoSpacing" style="margin-left: .25in; mso-list: l0 level1 lfo1; text-indent: -.25in;">
<!--[if !supportLists]--><span style="color: #333333; font-family: "Trebuchet MS",sans-serif; font-size: 12.0pt; mso-bidi-font-family: "Trebuchet MS"; mso-bidi-font-weight: bold; mso-fareast-font-family: "Trebuchet MS";">3.<span style="font-family: "Times New Roman"; font-size: 7pt; font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal;"> </span></span><!--[endif]--><span style="color: #333333; font-family: "Trebuchet MS",sans-serif; font-size: 12.0pt;">Click Insert > Recommended
Charts > <b>Funnel<o:p></o:p></b></span></div>
<div class="MsoNoSpacing" style="margin-left: .25in; mso-list: l0 level1 lfo1; text-indent: -.25in;">
<!--[if !supportLists]--><span style="color: #333333; font-family: "Trebuchet MS",sans-serif; font-size: 12.0pt; mso-bidi-font-family: "Trebuchet MS"; mso-bidi-font-weight: bold; mso-fareast-font-family: "Trebuchet MS";">4.<span style="font-family: "Times New Roman"; font-size: 7pt; font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal;"> </span></span><!--[endif]--><b><span style="color: #333333; font-family: "Trebuchet MS",sans-serif; font-size: 12.0pt;">Format
</span></b><span style="color: #333333; font-family: "Trebuchet MS",sans-serif; font-size: 12.0pt; mso-bidi-font-weight: bold;">according to your tastes and/or your
audience<b><o:p></o:p></b></span></div>
<div class="MsoNoSpacing">
<br /></div>
<div class="MsoNoSpacing">
<span style="color: #333333; font-family: "Trebuchet MS", sans-serif; font-size: 12pt;">The result is a remarkably </span><b style="color: #333333; font-family: "Trebuchet MS", sans-serif; font-size: 12pt;"><i>easy-to-understand</i></b><span style="color: #333333; font-family: "Trebuchet MS", sans-serif; font-size: 12pt;"> (which should be
a major goal of </span><b style="color: #333333; font-family: "Trebuchet MS", sans-serif; font-size: 12pt;"><i>Any</i></b><span style="color: #333333; font-family: "Trebuchet MS", sans-serif; font-size: 12pt;"> chart…) illustration of how your processes interrelate. This
new chart will be, I am quite sure, very useful for many of us in the
future. </span><b style="color: #333333; font-family: "Trebuchet MS", sans-serif; font-size: 12pt;">Funnel Charts – </b><span style="color: #333333; font-family: "Trebuchet MS", sans-serif; font-size: 12pt;">Another way to </span><b style="color: #333333; font-family: "Trebuchet MS", sans-serif; font-size: 12pt;"><i>picture</i></b><span style="color: #333333; font-family: "Trebuchet MS", sans-serif; font-size: 12pt;">
your data and </span><b style="color: #333333; font-family: "Trebuchet MS", sans-serif; font-size: 12pt;"><i>enhance</i></b><span style="color: #333333; font-family: "Trebuchet MS", sans-serif; font-size: 12pt;"> your business communication!</span></div>
Bob DeLaMartrehttp://www.blogger.com/profile/03761089085420930697noreply@blogger.com7tag:blogger.com,1999:blog-3794737577784583819.post-63551066410127746212017-12-14T10:56:00.002-08:002017-12-14T10:56:47.854-08:00Formatting Shortcuts Revisited<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjzvS6aW99ROSFte0gc-MSS6MOBcSbstJ1-9QeA3YR0-tC9V8mfYlRaqW9uSnn-kwQ3ztwFWwCjTCSxt2sWU6dElrSR38bHMqrC7A-XQU_-w8gGOk3LdvEK8pElARm-tUINrTGgb_gIyYB9/s1600/Keyboards.png" imageanchor="1" style="clear: left; float: left; margin-bottom: 1em; margin-right: 1em;"><img border="0" data-original-height="571" data-original-width="184" height="200" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjzvS6aW99ROSFte0gc-MSS6MOBcSbstJ1-9QeA3YR0-tC9V8mfYlRaqW9uSnn-kwQ3ztwFWwCjTCSxt2sWU6dElrSR38bHMqrC7A-XQU_-w8gGOk3LdvEK8pElARm-tUINrTGgb_gIyYB9/s200/Keyboards.png" width="64" /></a></div>
<span style="font-family: "Trebuchet MS",sans-serif; font-size: 12.0pt; line-height: 115%; mso-ansi-language: EN-US; mso-bidi-font-family: "Times New Roman"; mso-bidi-font-size: 11.0pt; mso-bidi-language: AR-SA; mso-bidi-theme-font: minor-bidi; mso-fareast-font-family: Calibri; mso-fareast-language: EN-US; mso-fareast-theme-font: minor-latin;">Yes, it’s true. I am an enormous fan of <b>Keyboard
Shortcuts</b>. As every true Excel Guru will tell you, these shortcuts can save
you many<b><i> hours of work time</i></b> over the long haul, and enhance your
image as one of the <b><i>Cool Kids in School</i></b>. <br />
<br />
One set of keyboard shortcuts that is typically overlooked is the ability to
pull up the Right-Click menu to <b><i>Change the Number Format</i></b> in the
cell or range of cells you are working on. Most all of us know that <b><i>Right-clicking</i></b>
with your mouse and pulling up a context-sensitive menu is a good solution, but
using the keyboard is <b><i>Even Faster (and Cooler)! </i></b><br />
<br />
The <b><i>Right-Click
Keyboard Shortcuts</i></b> all start with “<b><i>Ctrl+Shift</i></b>”, which
makes them more effortless to remember. As we discussed 4 years ago, the
following are the most <b><i>Commonly Used Tricks</i></b> for rapid
number formatting: <br />
<br />
<b>1. <i>General </i>= Ctrl+Shift+~ </b><br />
<br />
<b>2. <i>Number</i> = = Ctrl+Shift+! </b><br />
<br />
<b>3. <i>Time</i> = Ctrl+Shift+@ </b><br />
<br />
<b>4. <i>Date</i> = Ctrl+Shift+# </b><br />
<br />
<b>5. <i>Currency</i> = Ctrl+Shift+$ </b><br />
<br />
<b>6. <i>Percentage</i> = Ctrl+Shift+%</b> <br />
<br />
It will, of course, take a little bit of time to become fully accustomed to
using these shortcuts, but the time invested will certainly be worth it. Mastering
keyboard shortcuts is an excellent way to maximize your Excel time (and make you
appear the <b><i>Brilliant Soul</i></b> you truly are…).</span>Bob DeLaMartrehttp://www.blogger.com/profile/03761089085420930697noreply@blogger.com0tag:blogger.com,1999:blog-3794737577784583819.post-37399818617355375402017-12-05T13:52:00.000-08:002017-12-05T13:52:26.370-08:00Special Naming Ranges Tricks<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhVpr0GlWRAxEXDx6iSjp8xiQtlWoCtg8aWx_tBSZnRV5rDxUJk7kpmH6SK4lsFF-rBbip8MXbd8JJm1myIzqHrwq1r6uP3Ael30n4AwkFEVdH1MpN6Ex4Qc_P5_l7rgwf79IuOqkBezvxh/s1600/Mountains.png" imageanchor="1" style="clear: left; float: left; margin-bottom: 1em; margin-right: 1em;"><img border="0" data-original-height="627" data-original-width="171" height="200" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhVpr0GlWRAxEXDx6iSjp8xiQtlWoCtg8aWx_tBSZnRV5rDxUJk7kpmH6SK4lsFF-rBbip8MXbd8JJm1myIzqHrwq1r6uP3Ael30n4AwkFEVdH1MpN6Ex4Qc_P5_l7rgwf79IuOqkBezvxh/s200/Mountains.png" width="54" /></a></div>
<div class="MsoNoSpacing">
<span style="font-family: "Trebuchet MS",sans-serif; font-size: 12.0pt; mso-bidi-font-size: 11.0pt;">As we have discussed in this blog, and
most of us know quite well, <b>Naming
Ranges</b> is a well agreed-upon <b><i>Best Practice</i></b>.<o:p></o:p></span></div>
<div class="MsoNoSpacing">
<br /></div>
<div class="MsoNoSpacing">
<span style="font-family: "Trebuchet MS",sans-serif; font-size: 12.0pt; mso-bidi-font-size: 11.0pt;">As with any tool within Excel, however,
if you dig into it a little bit deeper you’ll find there are <b><i>additional
tips and tricks</i></b> that can add to your knowledge and help you do even
more in less time. Take for instance the following <b>Two Tricks for Naming Ranges</b>:<o:p></o:p></span></div>
<div class="MsoNoSpacing">
<br /></div>
<div class="MsoNoSpacing" style="margin-left: .5in; mso-list: l0 level1 lfo1; text-indent: -.25in;">
<!--[if !supportLists]--><b><span style="font-family: "Trebuchet MS",sans-serif; font-size: 12.0pt; mso-bidi-font-family: "Trebuchet MS"; mso-bidi-font-size: 11.0pt; mso-fareast-font-family: "Trebuchet MS";">1.<span style="font-family: "Times New Roman"; font-size: 7pt; font-stretch: normal; font-variant-numeric: normal; font-weight: normal; line-height: normal;"> </span></span></b><!--[endif]--><b><span style="font-family: "Trebuchet MS",sans-serif; font-size: 12.0pt; mso-bidi-font-size: 11.0pt;">Limiting the Scope of a Name<o:p></o:p></span></b></div>
<div class="MsoNoSpacing" style="margin-left: .5in;">
<span style="font-family: "Trebuchet MS",sans-serif; font-size: 12.0pt; mso-bidi-font-size: 11.0pt;">If you wish to
reuse a <b><i>Name</i></b> on different worksheets within a workbook, you can limit
the scope of a single use of the name to a single worksheet:<o:p></o:p></span></div>
<div class="MsoNoSpacing" style="margin-left: 1.0in; mso-list: l0 level2 lfo1; text-indent: -.25in;">
<!--[if !supportLists]--><span style="font-family: "Trebuchet MS",sans-serif; font-size: 12.0pt; mso-bidi-font-family: "Trebuchet MS"; mso-bidi-font-size: 11.0pt; mso-fareast-font-family: "Trebuchet MS";">a.<span style="font-family: "Times New Roman"; font-size: 7pt; font-stretch: normal; font-variant-numeric: normal; line-height: normal;"> </span></span><!--[endif]--><span style="font-family: "Trebuchet MS",sans-serif; font-size: 12.0pt; mso-bidi-font-size: 11.0pt;">Choose
<b>FORMULAS,</b> and then click <b><i>Define
Name</i></b><o:p></o:p></span></div>
<div class="MsoNoSpacing" style="margin-left: 1.0in; mso-list: l0 level2 lfo1; text-indent: -.25in;">
<!--[if !supportLists]--><span style="font-family: "Trebuchet MS",sans-serif; font-size: 12.0pt; mso-bidi-font-family: "Trebuchet MS"; mso-bidi-font-size: 11.0pt; mso-fareast-font-family: "Trebuchet MS";">b.<span style="font-family: "Times New Roman"; font-size: 7pt; font-stretch: normal; font-variant-numeric: normal; line-height: normal;"> </span></span><!--[endif]--><span style="font-family: "Trebuchet MS",sans-serif; font-size: 12.0pt; mso-bidi-font-size: 11.0pt;">Fill
in the details, specify <b>SCOPE</b>, and
click <b>OK</b><o:p></o:p></span></div>
<div class="MsoNoSpacing">
<br /></div>
<div class="MsoNoSpacing" style="margin-left: .5in; mso-list: l0 level1 lfo1; text-indent: -.25in;">
<!--[if !supportLists]--><b><span style="font-family: "Trebuchet MS",sans-serif; font-size: 12.0pt; mso-bidi-font-family: "Trebuchet MS"; mso-bidi-font-size: 11.0pt; mso-fareast-font-family: "Trebuchet MS";">2.<span style="font-family: "Times New Roman"; font-size: 7pt; font-stretch: normal; font-variant-numeric: normal; font-weight: normal; line-height: normal;"> </span></span></b><!--[endif]--><b><span style="font-family: "Trebuchet MS",sans-serif; font-size: 12.0pt; mso-bidi-font-size: 11.0pt;">Defining a Constant or Formula
for a Name<o:p></o:p></span></b></div>
<div class="MsoNoSpacing" style="margin-left: .5in;">
<span style="font-family: "Trebuchet MS",sans-serif; font-size: 12.0pt; mso-bidi-font-size: 11.0pt;">The ability to
have a <b><i>Constant </i></b>or <b><i>Formula</i></b> available in <b>Name</b><i>
</i>form can be a true timesaver when working with complex workbooks:<o:p></o:p></span></div>
<div class="MsoNoSpacing" style="margin-left: 1.0in; mso-list: l0 level2 lfo1; text-indent: -.25in;">
<!--[if !supportLists]--><span style="font-family: "Trebuchet MS",sans-serif; font-size: 12.0pt; mso-bidi-font-family: "Trebuchet MS"; mso-bidi-font-size: 11.0pt; mso-fareast-font-family: "Trebuchet MS";">a.<span style="font-family: "Times New Roman"; font-size: 7pt; font-stretch: normal; font-variant-numeric: normal; line-height: normal;"> </span></span><!--[endif]--><span style="font-family: "Trebuchet MS",sans-serif; font-size: 12.0pt; mso-bidi-font-size: 11.0pt;">Choose
<b>FORMULAS</b>, and then click <b><i>Define
Name</i></b><o:p></o:p></span></div>
<div class="MsoNoSpacing" style="margin-left: 1.0in; mso-list: l0 level2 lfo1; text-indent: -.25in;">
<!--[if !supportLists]--><span style="font-family: "Trebuchet MS",sans-serif; font-size: 12.0pt; mso-bidi-font-family: "Trebuchet MS"; mso-bidi-font-size: 11.0pt; mso-fareast-font-family: "Trebuchet MS";">b.<span style="font-family: "Times New Roman"; font-size: 7pt; font-stretch: normal; font-variant-numeric: normal; line-height: normal;"> </span></span><!--[endif]--><span style="font-family: "Trebuchet MS",sans-serif; font-size: 12.0pt; mso-bidi-font-size: 11.0pt;">Type
the <b>NAME</b>, <b>SCOPE</b>, and (if you wish, <b>COMMENT</b>)<o:p></o:p></span></div>
<div class="MsoNoSpacing" style="margin-left: 1.0in; mso-list: l0 level2 lfo1; text-indent: -.25in;">
<!--[if !supportLists]--><span style="font-family: "Trebuchet MS",sans-serif; font-size: 12.0pt; mso-bidi-font-family: "Trebuchet MS"; mso-bidi-font-size: 11.0pt; mso-fareast-font-family: "Trebuchet MS";">c.<span style="font-family: "Times New Roman"; font-size: 7pt; font-stretch: normal; font-variant-numeric: normal; line-height: normal;"> </span></span><!--[endif]--><span style="font-family: "Trebuchet MS",sans-serif; font-size: 12.0pt; mso-bidi-font-size: 11.0pt;">In
the dialog box, <b>REFERS TO</b>, insert
the Constant or Formula preceded by an <b>=</b>,
and click <b>OK</b><o:p></o:p></span></div>
<div class="MsoNoSpacing">
<br /></div>
<div class="MsoNoSpacing">
<span style="font-family: "Trebuchet MS", sans-serif; font-size: 12pt;">Knowing several </span><b style="font-family: "Trebuchet MS", sans-serif; font-size: 12pt;"><i>Best Practices</i></b><span style="font-family: "Trebuchet MS", sans-serif; font-size: 12pt;"> is the
hallmark of any true Excel Guru. However, knowing a few extra special tricks in
addition to these best practices can get you a place </span><b style="font-family: "Trebuchet MS", sans-serif; font-size: 12pt;"><i>higher on the mountain</i></b><span style="font-family: "Trebuchet MS", sans-serif; font-size: 12pt;">.</span></div>
Bob DeLaMartrehttp://www.blogger.com/profile/03761089085420930697noreply@blogger.com0tag:blogger.com,1999:blog-3794737577784583819.post-88743152351421296292017-11-30T14:01:00.000-08:002017-11-30T14:01:53.888-08:00More Rounding…<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEji54rDcmYta6iyI3bu71XhlOQW2pi8FMDeiA7isGEhuVFQb_fKP_SZRJ1mvUk5cjRtddy3hSooDZYDHE3VLB3qmIQ-rDC9nWPomMFhgGa5Qix7SsCbhYjBIBJu9mTyPBhv7jhuYn8CPkfj/s1600/Targets.png" imageanchor="1" style="clear: left; float: left; margin-bottom: 1em; margin-right: 1em;"><img border="0" data-original-height="367" data-original-width="99" height="200" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEji54rDcmYta6iyI3bu71XhlOQW2pi8FMDeiA7isGEhuVFQb_fKP_SZRJ1mvUk5cjRtddy3hSooDZYDHE3VLB3qmIQ-rDC9nWPomMFhgGa5Qix7SsCbhYjBIBJu9mTyPBhv7jhuYn8CPkfj/s200/Targets.png" width="53" /></a></div>
<div class="MsoNoSpacing">
<span style="font-size: 12pt;"><span style="font-family: Trebuchet MS, sans-serif;">Last
week we discussed the use of the <b>CEILING
</b>and <b>FLOOR</b> rounding functions.
Those are, of course, <b><i>Not All</i></b> that is available when it
comes to controlling your <b><i>Precision</i></b> in Excel. We will look at
the <b>INT</b>, <b>EVEN</b>, and <b>ODD</b> functions.<o:p></o:p></span></span></div>
<div class="MsoNoSpacing">
<br /></div>
<div class="MsoNoSpacing">
<span style="font-family: Trebuchet MS, sans-serif;"><b><span style="font-size: 12pt;">=INT(number)</span></b><span style="font-size: 12pt;"><o:p></o:p></span></span></div>
<div class="MsoNoSpacing">
<span style="font-size: 12pt;"><span style="font-family: Trebuchet MS, sans-serif;">This
function rounds the<b><i> </i></b>number <b><i>down</i></b> to the nearest integer. Many
possible applications, including staffing of projects.<o:p></o:p></span></span></div>
<div class="MsoNoSpacing">
<br /></div>
<div class="MsoNoSpacing">
<b><span style="font-size: 12pt;"><span style="font-family: Trebuchet MS, sans-serif;">=Even(number)<o:p></o:p></span></span></b></div>
<div class="MsoNoSpacing">
<span style="font-size: 12pt;"><span style="font-family: Trebuchet MS, sans-serif;">Using
this function, your number rounds <b><i>away from zero</i></b> to the nearest <b><i>even</i></b>
integer. If your staffing requires pairs of individuals working together, this
may be just your ticket.<o:p></o:p></span></span></div>
<div class="MsoNoSpacing">
<br /></div>
<div class="MsoNoSpacing">
<b><span style="font-size: 12pt;"><span style="font-family: Trebuchet MS, sans-serif;">=ODD(number)<o:p></o:p></span></span></b></div>
<div class="MsoNoSpacing">
<span style="font-size: 12pt;"><span style="font-family: Trebuchet MS, sans-serif;">As
you might expect, this function always rounds <b><i>away from zero</i></b> to the
nearest <b><i>odd </i></b>integer. Undoubtedly useful in certain <b><i>Odd</i></b> situations (sorry, I
couldn’t resist…).<o:p></o:p></span></span></div>
<div class="MsoNoSpacing">
<br /></div>
<div class="MsoNoSpacing">
<b><span style="font-size: 12pt;"><span style="font-family: Trebuchet MS, sans-serif;">INT</span></span></b><span style="font-size: 12pt;"><span style="font-family: Trebuchet MS, sans-serif;">, <b>EVEN</b>, and <b>ODD</b>. More ways to round your data <b><i>your way</i></b> in Excel.<o:p></o:p></span></span></div>
Bob DeLaMartrehttp://www.blogger.com/profile/03761089085420930697noreply@blogger.com0tag:blogger.com,1999:blog-3794737577784583819.post-89827646844913108852017-11-22T12:03:00.002-08:002017-11-22T12:03:50.334-08:00Ceiling & Floor<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEi5RjCPEaSwARiGCz7Ev9tVsmyFyAvJJmQbXxq28BWLU3Bl-DvmUYBRgmoEUZJuizuSszcWApdQdb5SfojW6EAFF9mRxwxoMdSLjMPw0LJgx2Bxwfj4_pPw4M16XTff86TP1CIexlwvm0Ep/s1600/FloorAndCeiling.png" imageanchor="1" style="clear: left; float: left; margin-bottom: 1em; margin-right: 1em;"><img border="0" data-original-height="649" data-original-width="206" height="200" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEi5RjCPEaSwARiGCz7Ev9tVsmyFyAvJJmQbXxq28BWLU3Bl-DvmUYBRgmoEUZJuizuSszcWApdQdb5SfojW6EAFF9mRxwxoMdSLjMPw0LJgx2Bxwfj4_pPw4M16XTff86TP1CIexlwvm0Ep/s200/FloorAndCeiling.png" width="63" /></a></div>
<div class="MsoNoSpacing">
<span style="font-family: "Trebuchet MS",sans-serif; font-size: 12.0pt;">When
Excel is displaying <b><i>slightly different</i></b> values than what is actually stored in the
cells that you are working on, it can be beneficial to use one of the several <b>Rounding</b> functions. Two of these
rounding functions are <b>CEILING</b> and <b>FLOOR</b>.<o:p></o:p></span></div>
<div class="MsoNoSpacing">
<br /></div>
<div class="MsoNoSpacing">
<span style="font-family: "Trebuchet MS",sans-serif; font-size: 12.0pt;">The
use of a rounding function is often recommended, especially if the numbers you
are investigating are the result of multiple mathematical calculations. Multiple
calculations may, of course, result in the introduction of rounding errors. These
behind-the-scenes errors can cause <b><i>small</i></b> inaccuracies which may be <b><i>very
significant</i></b> when precision is necessary.<o:p></o:p></span></div>
<div class="MsoNoSpacing">
<br /></div>
<div class="MsoNoSpacing">
<span style="font-family: "Trebuchet MS",sans-serif; font-size: 12.0pt;">The
syntax for CEILING is <b>CEILING(Number, Significance)</b>
where<o:p></o:p></span></div>
<div class="MsoNoSpacing" style="margin-left: .5in; mso-list: l0 level1 lfo1; text-indent: -.25in;">
<!--[if !supportLists]--><span style="font-family: Symbol; font-size: 12.0pt; mso-bidi-font-family: Symbol; mso-fareast-font-family: Symbol;">·<span style="font-family: "Times New Roman"; font-size: 7pt; font-stretch: normal; font-variant-numeric: normal; line-height: normal;">
</span></span><!--[endif]--><b><i><span style="font-family: "Trebuchet MS",sans-serif; font-size: 12.0pt;">Number</span></i></b><span style="font-family: "Trebuchet MS",sans-serif; font-size: 12.0pt;"> is the number being rounded, and<o:p></o:p></span></div>
<div class="MsoNoSpacing" style="margin-left: .5in; mso-list: l0 level1 lfo1; text-indent: -.25in;">
<!--[if !supportLists]--><span style="font-family: Symbol; font-size: 12.0pt; mso-bidi-font-family: Symbol; mso-fareast-font-family: Symbol;">·<span style="font-family: "Times New Roman"; font-size: 7pt; font-stretch: normal; font-variant-numeric: normal; line-height: normal;">
</span></span><!--[endif]--><b><i><span style="font-family: "Trebuchet MS",sans-serif; font-size: 12.0pt;">Significance</span></i></b><span style="font-family: "Trebuchet MS",sans-serif; font-size: 12.0pt;"> is the number of additional integers
to which the number is being rounded.<o:p></o:p></span></div>
<div class="MsoNoSpacing">
<br /></div>
<div class="MsoNoSpacing">
<span style="font-family: "Trebuchet MS",sans-serif; font-size: 12.0pt;">The
following <b><i>Examples</i></b> make this a bit clearer:<o:p></o:p></span></div>
<div class="MsoNoSpacing" style="margin-left: .5in; mso-list: l1 level1 lfo2; text-indent: -.25in;">
<!--[if !supportLists]--><span style="font-family: Symbol; font-size: 12.0pt; mso-bidi-font-family: Symbol; mso-fareast-font-family: Symbol;">·<span style="font-family: "Times New Roman"; font-size: 7pt; font-stretch: normal; font-variant-numeric: normal; line-height: normal;">
</span></span><!--[endif]--><b><span style="font-family: "Trebuchet MS",sans-serif; font-size: 12.0pt;">= CEILING(20.3,
1) = 21<o:p></o:p></span></b></div>
<div class="MsoNoSpacing" style="margin-left: .5in; mso-list: l1 level1 lfo2; text-indent: -.25in;">
<!--[if !supportLists]--><span style="font-family: Symbol; font-size: 12.0pt; mso-bidi-font-family: Symbol; mso-fareast-font-family: Symbol;">·<span style="font-family: "Times New Roman"; font-size: 7pt; font-stretch: normal; font-variant-numeric: normal; line-height: normal;">
</span></span><!--[endif]--><b><span style="font-family: "Trebuchet MS",sans-serif; font-size: 12.0pt;">= CEILING(20.3, 5)
= 25<o:p></o:p></span></b></div>
<div class="MsoNoSpacing" style="margin-left: .5in; mso-list: l1 level1 lfo2; text-indent: -.25in;">
<!--[if !supportLists]--><span style="font-family: Symbol; font-size: 12.0pt; mso-bidi-font-family: Symbol; mso-fareast-font-family: Symbol;">·<span style="font-family: "Times New Roman"; font-size: 7pt; font-stretch: normal; font-variant-numeric: normal; line-height: normal;">
</span></span><!--[endif]--><b><span style="font-family: "Trebuchet MS",sans-serif; font-size: 12.0pt;">= CEILING(-19.9,
2) = -18<o:p></o:p></span></b></div>
<div class="MsoNoSpacing">
<br /></div>
<div class="MsoNoSpacing">
<span style="font-family: "Trebuchet MS",sans-serif; font-size: 12.0pt;">The
<b>FLOOR </b>function does the very same
thing as the <b>CEILING</b> function;
however it always <b><i>rounds down</i></b>.<o:p></o:p></span></div>
<div class="MsoNoSpacing">
<br /></div>
<div class="MsoNoSpacing">
</div>
<div class="MsoNoSpacing">
<b><span style="font-family: "Trebuchet MS",sans-serif; font-size: 12.0pt;">CEILING</span></b><span style="font-family: "Trebuchet MS",sans-serif; font-size: 12.0pt;"> and <b>FLOOR</b>. Two more very useful functions
for your Excel toolbelt.<o:p></o:p></span></div>
Bob DeLaMartrehttp://www.blogger.com/profile/03761089085420930697noreply@blogger.com0tag:blogger.com,1999:blog-3794737577784583819.post-75433128910641325322017-11-17T11:44:00.000-08:002017-11-17T11:44:28.510-08:00Time Management with Excel<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEg_iE2M4uPMvNXoNXsC0eTJUOUOwrwApabWulxRBvgRSZvnE-5A4KPHT0uTCZdEqeD-MwPCC9DpGcS7-D7dEF4bIrUE08T5TmuGMvlv9ULskI8788tf37ZTKJlI0MXl2NH_WJM7NPviLQZV/s1600/clocks.png" imageanchor="1" style="clear: left; float: left; margin-bottom: 1em; margin-right: 1em;"><img border="0" data-original-height="573" data-original-width="117" height="200" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEg_iE2M4uPMvNXoNXsC0eTJUOUOwrwApabWulxRBvgRSZvnE-5A4KPHT0uTCZdEqeD-MwPCC9DpGcS7-D7dEF4bIrUE08T5TmuGMvlv9ULskI8788tf37ZTKJlI0MXl2NH_WJM7NPviLQZV/s200/clocks.png" width="40" /></a></div>
<div class="MsoNoSpacing">
<span style="font-family: "Trebuchet MS",sans-serif; font-size: 12.0pt; mso-bidi-font-size: 11.0pt;">I admit that I have been a bit
overwhelmed with work lately, and as an instructor and avid proponent of <i>Time Management</i>, I find this slightly
upsetting. If, in fact, you employ good time management techniques, you can
very directly determine what you should be working on and when. By the way, <i>Ready for Anything</i> by David Allen is an
excellent book on this subject.<o:p></o:p></span></div>
<div class="MsoNoSpacing">
<br /></div>
<div class="MsoNoSpacing">
<span style="font-family: "Trebuchet MS",sans-serif; font-size: 12.0pt; mso-bidi-font-size: 11.0pt;">But how can Excel help in this regard?
Well, let’s say that you have your<b><i> Calendar</i></b> and <b><i>To-Do Lists </i></b>in PDF form.
You can import data in this formant directly into an Excel spreadsheet using a <b><i>PDF
to Excel Converter</i></b>. The result is that you to easily keep track of key
dates or to-do lists. You can eliminate a plethora of reminder notes and other
calendars. In the process, you’ll also save paper and time, (<b><i>Time
Management</i></b>, get it?...)</span></div>
<div class="MsoNoSpacing">
<span style="font-family: "Trebuchet MS", sans-serif; font-size: 12pt;"><br /></span></div>
<div class="MsoNoSpacing">
<span style="font-family: "Trebuchet MS", sans-serif; font-size: 12pt;">A large project with several
professionals collaborating can use this Excel spreadsheet as a database for </span><b style="font-family: "Trebuchet MS", sans-serif; font-size: 12pt;"><i>Project
Management</i></b><span style="font-family: "Trebuchet MS", sans-serif; font-size: 12pt;">. Share the spreadsheet, and everyone can add their own
information. You can include data such as availability, contact info, element ideas,
resource tools, and so forth. For starters, check out the many If you are
organized, you won’t be sitting at your desk wondering what to do next and, that
my friends, is a very good thing.</span></div>
Bob DeLaMartrehttp://www.blogger.com/profile/03761089085420930697noreply@blogger.com0tag:blogger.com,1999:blog-3794737577784583819.post-92028258406917289082017-11-10T09:26:00.001-08:002017-11-10T09:26:29.944-08:00Sparklines Series # 5<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjeVb4XInF6zk5ytnd2SlX8y7lwKhvzPqlfxmATX1e1U2yy6JeW2f6kTCRUGPibmzx4H-auuIybQoe_ifEAF4RdY7FLdqKJVvkGdC4oBiag_f9Fkhgmvpsjn7gsJurKvvE_TdC1j91ftFlR/s1600/Sparklines5.png" imageanchor="1" style="clear: left; float: left; margin-bottom: 1em; margin-right: 1em;"><img border="0" data-original-height="569" data-original-width="147" height="320" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjeVb4XInF6zk5ytnd2SlX8y7lwKhvzPqlfxmATX1e1U2yy6JeW2f6kTCRUGPibmzx4H-auuIybQoe_ifEAF4RdY7FLdqKJVvkGdC4oBiag_f9Fkhgmvpsjn7gsJurKvvE_TdC1j91ftFlR/s320/Sparklines5.png" width="81" /></a></div>
<div class="MsoNoSpacing">
<span style="font-family: "Trebuchet MS",sans-serif; font-size: 12.0pt;">This
week we are concluding our examination of <b><i>Sparklines</i></b> with this fifth installment
of our series on this remarkably versatile Excel tool.<o:p></o:p></span></div>
<div class="MsoNoSpacing">
<br /></div>
<div class="MsoNoSpacing">
<span style="font-family: "Trebuchet MS",sans-serif; font-size: 12.0pt;">In
this series we have explored the <b><i>basics</i></b> of creating Sparklines, <b><i>changing</i></b>
their type, <b><i>highlighting </i></b>values, adding <b><i>text</i></b>, as well as changing
the <b><i>color
</i></b>and <b><i>weight</i></b>. In this final week will take a look at how to handle<b><i> Hidden
and</i></b> <b><i>Empty Cells </i></b>in Sparklines.<o:p></o:p></span></div>
<div class="MsoNoSpacing">
<br /></div>
<div class="MsoNoSpacing">
<span style="font-family: "Trebuchet MS",sans-serif; font-size: 12.0pt;">Let’s
examine the procedure of managing these potentially troublesome hidden and
empty cells:<o:p></o:p></span></div>
<div class="MsoNoSpacing">
<br /></div>
<div class="MsoNoSpacing" style="margin-left: .5in; mso-list: l0 level1 lfo1; text-indent: -.25in;">
<!--[if !supportLists]--><span style="font-family: "Trebuchet MS",sans-serif; font-size: 12.0pt; mso-bidi-font-family: "Trebuchet MS"; mso-fareast-font-family: "Trebuchet MS";">1.<span style="font-family: "Times New Roman"; font-size: 7pt; font-stretch: normal; font-variant-numeric: normal; line-height: normal;"> </span></span><!--[endif]--><span style="font-family: "Trebuchet MS",sans-serif; font-size: 12.0pt;">Select the Sparkline
group<o:p></o:p></span></div>
<div class="MsoNoSpacing" style="margin-left: .5in; mso-list: l0 level1 lfo1; text-indent: -.25in;">
<!--[if !supportLists]--><span style="font-family: "Trebuchet MS",sans-serif; font-size: 12.0pt; mso-bidi-font-family: "Trebuchet MS"; mso-fareast-font-family: "Trebuchet MS";">2.<span style="font-family: "Times New Roman"; font-size: 7pt; font-stretch: normal; font-variant-numeric: normal; line-height: normal;"> </span></span><!--[endif]--><span style="font-family: "Trebuchet MS",sans-serif; font-size: 12.0pt;">From the left
side of the <b>Sparkline Tools/Design</b> ribbon,
click on <b><i>Edit Data</i></b><o:p></o:p></span></div>
<div class="MsoNoSpacing" style="margin-left: .5in; mso-list: l0 level1 lfo1; text-indent: -.25in;">
<!--[if !supportLists]--><span style="font-family: "Trebuchet MS",sans-serif; font-size: 12.0pt; mso-bidi-font-family: "Trebuchet MS"; mso-fareast-font-family: "Trebuchet MS";">3.<span style="font-family: "Times New Roman"; font-size: 7pt; font-stretch: normal; font-variant-numeric: normal; line-height: normal;"> </span></span><!--[endif]--><span style="font-family: "Trebuchet MS",sans-serif; font-size: 12.0pt;">Select <b><i>Hidden
& Empty Cells</i></b><o:p></o:p></span></div>
<div class="MsoNoSpacing" style="margin-left: .5in; mso-list: l0 level1 lfo1; text-indent: -.25in;">
<!--[if !supportLists]--><span style="font-family: "Trebuchet MS",sans-serif; font-size: 12.0pt; mso-bidi-font-family: "Trebuchet MS"; mso-fareast-font-family: "Trebuchet MS";">4.<span style="font-family: "Times New Roman"; font-size: 7pt; font-stretch: normal; font-variant-numeric: normal; line-height: normal;"> </span></span><!--[endif]--><span style="font-family: "Trebuchet MS",sans-serif; font-size: 12.0pt;">You will be
presented with <b><i>Hidden and Empty Cell Settings</i></b><o:p></o:p></span></div>
<div class="MsoNoSpacing" style="margin-left: .5in; mso-list: l0 level1 lfo1; text-indent: -.25in;">
<!--[if !supportLists]--><span style="font-family: "Trebuchet MS",sans-serif; font-size: 12.0pt; mso-bidi-font-family: "Trebuchet MS"; mso-fareast-font-family: "Trebuchet MS";">5.<span style="font-family: "Times New Roman"; font-size: 7pt; font-stretch: normal; font-variant-numeric: normal; line-height: normal;"> </span></span><!--[endif]--><span style="font-family: "Trebuchet MS",sans-serif; font-size: 12.0pt;">Choose <b><i>Show
Empty Cells:</i></b><o:p></o:p></span></div>
<div class="MsoNoSpacing" style="margin-left: 1.0in; mso-list: l0 level2 lfo1; text-indent: -.25in;">
<!--[if !supportLists]--><span style="font-family: Symbol; font-size: 12.0pt; mso-bidi-font-family: Symbol; mso-fareast-font-family: Symbol;">·<span style="font-family: "Times New Roman"; font-size: 7pt; font-stretch: normal; font-variant-numeric: normal; line-height: normal;">
</span></span><!--[endif]--><b><span style="font-family: "Trebuchet MS",sans-serif; font-size: 12.0pt;">Gaps</span></b><span style="font-family: "Trebuchet MS",sans-serif; font-size: 12.0pt;"> - Show a space
between points<o:p></o:p></span></div>
<div class="MsoNoSpacing" style="margin-left: 1.0in; mso-list: l0 level2 lfo1; text-indent: -.25in;">
<!--[if !supportLists]--><span style="font-family: Symbol; font-size: 12.0pt; mso-bidi-font-family: Symbol; mso-fareast-font-family: Symbol;">·<span style="font-family: "Times New Roman"; font-size: 7pt; font-stretch: normal; font-variant-numeric: normal; line-height: normal;">
</span></span><!--[endif]--><b><span style="font-family: "Trebuchet MS",sans-serif; font-size: 12.0pt;">Zero:</span></b><span style="font-family: "Trebuchet MS",sans-serif; font-size: 12.0pt;"> Show as a zero
(0) value<o:p></o:p></span></div>
<div class="MsoNoSpacing" style="margin-left: 1.0in; mso-list: l0 level2 lfo1; text-indent: -.25in;">
<!--[if !supportLists]--><span style="font-family: Symbol; font-size: 12.0pt; mso-bidi-font-family: Symbol; mso-fareast-font-family: Symbol;">·<span style="font-family: "Times New Roman"; font-size: 7pt; font-stretch: normal; font-variant-numeric: normal; line-height: normal;">
</span></span><!--[endif]--><b><span style="font-family: "Trebuchet MS",sans-serif; font-size: 12.0pt;">Connect data
points with line:</span></b><span style="font-family: "Trebuchet MS",sans-serif; font-size: 12.0pt;">
Connects the points before and after the empty cell<o:p></o:p></span></div>
<div class="MsoNoSpacing">
<br /></div>
<div class="MsoNoSpacing">
<span style="font-family: "Trebuchet MS", sans-serif; font-size: 12pt;">Try
giving</span><b style="font-family: "Trebuchet MS", sans-serif; font-size: 12pt;"> Sparklines </b><span style="font-family: "Trebuchet MS", sans-serif; font-size: 12pt;">a try sometime.
You may be surprised at </span><b style="font-family: "Trebuchet MS", sans-serif; font-size: 12pt;"><i>how efficient</i></b><span style="font-family: "Trebuchet MS", sans-serif; font-size: 12pt;"> they are to create,
and </span><b style="font-family: "Trebuchet MS", sans-serif; font-size: 12pt;"><i>how
well</i></b><span style="font-family: "Trebuchet MS", sans-serif; font-size: 12pt;"> they will be received by your users.</span></div>
Bob DeLaMartrehttp://www.blogger.com/profile/03761089085420930697noreply@blogger.com0tag:blogger.com,1999:blog-3794737577784583819.post-56593849217318501972017-11-06T12:23:00.000-08:002017-11-06T12:23:24.958-08:00Sparklines Series #4<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEh7ddQRXg-XtpdycFSAajB888htFRXquq1pYOv1K5gIfgZd6CBBhaK4fnNmujJtKMwEwq3aDAOMQqmWhG6NqTiqYP31o9WE5hBVxlC9pE-WPfdvQo11Y6VUbZh_4HakwyHdVirzlrP4Khbl/s1600/Sparklines4.png" imageanchor="1" style="clear: left; float: left; margin-bottom: 1em; margin-right: 1em;"><img border="0" data-original-height="508" data-original-width="159" height="200" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEh7ddQRXg-XtpdycFSAajB888htFRXquq1pYOv1K5gIfgZd6CBBhaK4fnNmujJtKMwEwq3aDAOMQqmWhG6NqTiqYP31o9WE5hBVxlC9pE-WPfdvQo11Y6VUbZh_4HakwyHdVirzlrP4Khbl/s200/Sparklines4.png" width="62" /></a></div>
<div class="MsoNoSpacing">
<span style="font-family: "Trebuchet MS",sans-serif; font-size: 12.0pt;">This
week we are continuing with the fourth installment of our series on the surprisingly
versatile and useful Excel tool called <b>Sparklines.</b><o:p></o:p></span></div>
<div class="MsoNoSpacing">
<br /></div>
<div class="MsoNormal" style="line-height: normal;">
<span style="font-family: "Trebuchet MS",sans-serif; font-size: 12.0pt; mso-bidi-font-family: Calibri; mso-fareast-font-family: "Times New Roman";">As we have noted,<b> </b>these <b><i>Miniature Charts</i></b> fit into a single cell or
range in your worksheet and, as with any chart, they provide you with a <b><i>visual
representation</i></b> of your data. The beauty of </span><b><i><span style="font-family: "Trebuchet MS",sans-serif; font-size: 12.0pt;">Sparklines</span></i></b><span style="font-family: "Trebuchet MS",sans-serif; font-size: 12.0pt; mso-bidi-font-family: Calibri; mso-fareast-font-family: "Times New Roman";">, of course is that they provide
this visual representation <b><i>without</i></b> having to resort to a
full-blown chart.</span><span style="font-family: "Times New Roman",serif; font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";"><o:p></o:p></span></div>
<div class="MsoNoSpacing">
<span style="font-family: "Trebuchet MS",sans-serif; font-size: 12.0pt; mso-bidi-font-family: Calibri; mso-fareast-font-family: "Times New Roman";">Let’s
see what else we can do with these <b><i>Miniature Marvels</i></b>.</span><span style="font-family: "Trebuchet MS",sans-serif; font-size: 12.0pt;"> For instance,
let’s say that you want to add a little bit more <b><i>Visual Pizzazz</i></b> to your Sparkline
group. Here is what you do:<o:p></o:p></span></div>
<div class="MsoNoSpacing">
<br /></div>
<div class="MsoNoSpacing">
<b><span style="font-family: "Trebuchet MS",sans-serif; font-size: 12.0pt;">Change the Color
and Weight of Your Sparklines<o:p></o:p></span></b></div>
<div class="MsoNoSpacing" style="margin-left: .5in; mso-list: l1 level1 lfo1; text-indent: -.25in;">
<!--[if !supportLists]--><span style="font-family: "Trebuchet MS",sans-serif; font-size: 12.0pt; mso-bidi-font-family: "Trebuchet MS"; mso-fareast-font-family: "Trebuchet MS";">1.<span style="font-family: "Times New Roman"; font-size: 7pt; font-stretch: normal; font-variant-numeric: normal; line-height: normal;"> </span></span><!--[endif]--><span style="font-family: "Trebuchet MS",sans-serif; font-size: 12.0pt;">First select a <a href="https://www.blogger.com/null" name="_Hlk497733704">Sparkline</a> or <a href="https://www.blogger.com/null" name="_Hlk497733829">Sparkline</a>
group, and then choose <b><i>Sparkline Tools/Design</i></b><o:p></o:p></span></div>
<div class="MsoNoSpacing" style="margin-left: .5in; mso-list: l1 level1 lfo1; text-indent: -.25in;">
<!--[if !supportLists]--><span style="font-family: "Trebuchet MS",sans-serif; font-size: 12.0pt; mso-bidi-font-family: "Trebuchet MS"; mso-fareast-font-family: "Trebuchet MS";">2.<span style="font-family: "Times New Roman"; font-size: 7pt; font-stretch: normal; font-variant-numeric: normal; line-height: normal;"> </span></span><!--[endif]--><span style="font-family: "Trebuchet MS",sans-serif; font-size: 12.0pt;">Then select a
style of your choosing to change both the <b><i>Sparkline</i></b> and the <b><i>Marker</i></b>
color<o:p></o:p></span></div>
<div class="MsoNoSpacing" style="margin-left: .5in; mso-list: l1 level1 lfo1; text-indent: -.25in;">
<!--[if !supportLists]--><span style="font-family: "Trebuchet MS",sans-serif; font-size: 12.0pt; mso-bidi-font-family: "Trebuchet MS"; mso-fareast-font-family: "Trebuchet MS";">3.<span style="font-family: "Times New Roman"; font-size: 7pt; font-stretch: normal; font-variant-numeric: normal; line-height: normal;"> </span></span><!--[endif]--><span style="font-family: "Trebuchet MS",sans-serif; font-size: 12.0pt;">Alternatively,
you can change just the Sparkline color and weight<o:p></o:p></span></div>
<div class="MsoNoSpacing">
<br /></div>
<div class="MsoNoSpacing">
<b><span style="font-family: "Trebuchet MS",sans-serif; font-size: 12.0pt;">Customize the
Axis Settings<o:p></o:p></span></b></div>
<div class="MsoNoSpacing" style="margin-left: .5in; mso-list: l0 level1 lfo2; text-indent: -.25in;">
<!--[if !supportLists]--><span style="font-family: "Trebuchet MS",sans-serif; font-size: 12.0pt; mso-bidi-font-family: "Trebuchet MS"; mso-fareast-font-family: "Trebuchet MS";">1.<span style="font-family: "Times New Roman"; font-size: 7pt; font-stretch: normal; font-variant-numeric: normal; line-height: normal;"> </span></span><!--[endif]--><span style="font-family: "Trebuchet MS",sans-serif; font-size: 12.0pt;">Select the Sparkline
or Sparkline group<o:p></o:p></span></div>
<div class="MsoNoSpacing" style="margin-left: .5in; mso-list: l0 level1 lfo2; text-indent: -.25in;">
<!--[if !supportLists]--><span style="font-family: "Trebuchet MS",sans-serif; font-size: 12.0pt; mso-bidi-font-family: "Trebuchet MS"; mso-fareast-font-family: "Trebuchet MS";">2.<span style="font-family: "Times New Roman"; font-size: 7pt; font-stretch: normal; font-variant-numeric: normal; line-height: normal;"> </span></span><!--[endif]--><span style="font-family: "Trebuchet MS",sans-serif; font-size: 12.0pt;">Choose <b><i>Sparkline
Tools/Design</i></b><o:p></o:p></span></div>
<div class="MsoNoSpacing" style="margin-left: .5in; mso-list: l0 level1 lfo2; text-indent: -.25in;">
<!--[if !supportLists]--><span style="font-family: "Trebuchet MS",sans-serif; font-size: 12.0pt; mso-bidi-font-family: "Trebuchet MS"; mso-fareast-font-family: "Trebuchet MS";">3.<span style="font-family: "Times New Roman"; font-size: 7pt; font-stretch: normal; font-variant-numeric: normal; line-height: normal;"> </span></span><!--[endif]--><span style="font-family: "Trebuchet MS",sans-serif; font-size: 12.0pt;">Choose the <b><i>Axis</i></b>
and options of your choice<o:p></o:p></span></div>
<div class="MsoNoSpacing">
<br /></div>
<div class="MsoNoSpacing">
<b><span style="font-family: "Trebuchet MS",sans-serif; font-size: 12.0pt;">Sparklines.</span></b><span style="font-family: "Trebuchet MS", sans-serif; font-size: 12pt;"> Who knew all
the wonders of the small but powerful tools?</span></div>
Bob DeLaMartrehttp://www.blogger.com/profile/03761089085420930697noreply@blogger.com0tag:blogger.com,1999:blog-3794737577784583819.post-62876149877338537732017-10-26T14:16:00.001-07:002017-10-26T14:16:39.592-07:00Sparklines Series #3<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgm3jmg1pwNy7xUPlI9oygJhFE4vjL-xuIVdieN1NVJbbcdEWWXZMvqqMIRYjqZLBlvuX7EH1pkZijjZpdOHqJlITfw8chFDp2UxE_2CX3n0ALyiiQkzYG4C6YAiHfUwJyD7kteXYeV5WA9/s1600/Sparks3.png" imageanchor="1" style="clear: left; float: left; margin-bottom: 1em; margin-right: 1em;"><img border="0" data-original-height="525" data-original-width="141" height="200" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgm3jmg1pwNy7xUPlI9oygJhFE4vjL-xuIVdieN1NVJbbcdEWWXZMvqqMIRYjqZLBlvuX7EH1pkZijjZpdOHqJlITfw8chFDp2UxE_2CX3n0ALyiiQkzYG4C6YAiHfUwJyD7kteXYeV5WA9/s200/Sparks3.png" width="53" /></a></div>
<div class="MsoNoSpacing">
<span style="font-family: "Trebuchet MS",sans-serif; font-size: 12.0pt;">This
week we are proceeding with our series on the wonderful little Excel beasts, <b>Sparklines.</b><o:p></o:p></span></div>
<div class="MsoNoSpacing">
<br /></div>
<div class="MsoNoSpacing">
<span style="font-family: "Trebuchet MS",sans-serif; font-size: 12.0pt;">As
was mentioned last week, among the several advantages for using Sparklines, is
their <b><i>versatility</i></b>. We looked
at changing Sparkline types and removing Sparklines, but as they say in cheesy
TV ads, “<b><i>That’s not All!</i></b>” Let’s look at the following <b><i>Customization
Options</i></b>:<o:p></o:p></span></div>
<div class="MsoNoSpacing">
<br /></div>
<div class="MsoNoSpacing">
<b><span style="font-family: "Trebuchet MS",sans-serif; font-size: 12.0pt;">Adding Text<o:p></o:p></span></b></div>
<div class="MsoNoSpacing">
<span style="font-family: "Trebuchet MS",sans-serif; font-size: 12.0pt;">Adding
text to a sparkling cell is a <b><i>Snap</i></b> (or is it a Spark?)! Simply
select the cell, type in the text that you want, and press <i>E<b>nter</b></i>. The text will
display in the foreground with the Sparkline in the background. <b><i>Totally
Cool!</i></b><o:p></o:p></span></div>
<div class="MsoNoSpacing">
<br /></div>
<div class="MsoNoSpacing">
<b><span style="font-family: "Trebuchet MS",sans-serif; font-size: 12.0pt;">Highlighting
Values<o:p></o:p></span></b></div>
<div class="MsoNoSpacing">
<span style="font-family: "Trebuchet MS",sans-serif; font-size: 12.0pt;">Highlighting
data with a <a href="https://www.blogger.com/null" name="_Hlk496789452">Sparkline</a> is slightly more complex, but
really no big deal. First select the Sparkline, choose <b><i>Sparkline Tools/Design</i>,</b>
and select the following options from the <b><i>Show</i> </b>group:<o:p></o:p></span></div>
<div class="MsoNoSpacing" style="margin-left: .5in; mso-list: l0 level1 lfo1; text-indent: -.25in;">
<!--[if !supportLists]--><span style="font-family: "Trebuchet MS",sans-serif; font-size: 12.0pt; mso-bidi-font-family: "Trebuchet MS"; mso-fareast-font-family: "Trebuchet MS";">1.<span style="font-family: "Times New Roman"; font-size: 7pt; font-stretch: normal; font-variant-numeric: normal; line-height: normal;"> </span></span><!--[endif]--><span style="font-family: "Trebuchet MS",sans-serif; font-size: 12.0pt;">Add your markers
to all values by checking <b><i>Markers </i></b>(go figure…)<o:p></o:p></span></div>
<div class="MsoNoSpacing" style="margin-left: .5in; mso-list: l0 level1 lfo1; text-indent: -.25in;">
<!--[if !supportLists]--><span style="font-family: "Trebuchet MS",sans-serif; font-size: 12.0pt; mso-bidi-font-family: "Trebuchet MS"; mso-fareast-font-family: "Trebuchet MS";">2.<span style="font-family: "Times New Roman"; font-size: 7pt; font-stretch: normal; font-variant-numeric: normal; line-height: normal;"> </span></span><!--[endif]--><span style="font-family: "Trebuchet MS",sans-serif; font-size: 12.0pt;">You can show
negative values by checking <b><i>Negative Points </i></b>(not rocket science,
eh?)<o:p></o:p></span></div>
<div class="MsoNoSpacing" style="margin-left: .5in; mso-list: l0 level1 lfo1; text-indent: -.25in;">
<!--[if !supportLists]--><span style="font-family: "Trebuchet MS",sans-serif; font-size: 12.0pt; mso-bidi-font-family: "Trebuchet MS"; mso-fareast-font-family: "Trebuchet MS";">3.<span style="font-family: "Times New Roman"; font-size: 7pt; font-stretch: normal; font-variant-numeric: normal; line-height: normal;"> </span></span><!--[endif]--><span style="font-family: "Trebuchet MS",sans-serif; font-size: 12.0pt;">Showing the highest
or lowest values can be accomplished by checking <b><i>High Point</i></b><i> </i>or <b><i>Low Point</i></b><o:p></o:p></span></div>
<div class="MsoNoSpacing" style="margin-left: .5in; mso-list: l0 level1 lfo1; text-indent: -.25in;">
<!--[if !supportLists]--><span style="font-family: "Trebuchet MS",sans-serif; font-size: 12.0pt; mso-bidi-font-family: "Trebuchet MS"; mso-fareast-font-family: "Trebuchet MS";">4.<span style="font-family: "Times New Roman"; font-size: 7pt; font-stretch: normal; font-variant-numeric: normal; line-height: normal;"> </span></span><!--[endif]--><span style="font-family: "Trebuchet MS",sans-serif; font-size: 12.0pt;">You can also
show first or last values simply by checking <b><i>First Point</i></b> or <b><i>Last
Point</i></b> (this really is a <b><i>Snap</i></b>!)<o:p></o:p></span></div>
<div class="MsoNoSpacing">
<span style="font-family: "Trebuchet MS", sans-serif; font-size: 12pt;"><br /></span></div>
<div class="MsoNoSpacing">
<span style="font-family: "Trebuchet MS", sans-serif; font-size: 12pt;">I suspect you are beginning to see, there is a bit more to these little </span><b style="font-family: "Trebuchet MS", sans-serif; font-size: 12pt;"><i>Sparkline
gems</i></b><span style="font-family: "Trebuchet MS", sans-serif; font-size: 12pt;"> than first meets the eye. The next time you have five minutes to
yourself, play around with the surprisingly versatile little tools, and see how
you can add some more </span><b style="font-family: "Trebuchet MS", sans-serif; font-size: 12pt;"><i>Pizzazz</i></b><span style="font-family: "Trebuchet MS", sans-serif; font-size: 12pt;"> to your worksheets. Ciao,
baby!</span></div>
Bob DeLaMartrehttp://www.blogger.com/profile/03761089085420930697noreply@blogger.com0tag:blogger.com,1999:blog-3794737577784583819.post-24413088590822255822017-10-19T13:57:00.001-07:002017-10-19T13:58:06.361-07:00Sparklines Series #2<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEh_MxKLG3lTvDulOmREjCtzMjx4m7iaD0edoHk-QuRX_HtEkGD8Wa3B6_p-I9wBmKANgZxZadF5RnRUFzc4EULcVZHMFbt_txRdAfbUsianEe6x-NxV80yalKPvg19zU-fZHyAo09gPtBQ9/s1600/Lightning.png" imageanchor="1" style="clear: left; float: left; margin-bottom: 1em; margin-right: 1em;"><img border="0" data-original-height="376" data-original-width="128" height="200" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEh_MxKLG3lTvDulOmREjCtzMjx4m7iaD0edoHk-QuRX_HtEkGD8Wa3B6_p-I9wBmKANgZxZadF5RnRUFzc4EULcVZHMFbt_txRdAfbUsianEe6x-NxV80yalKPvg19zU-fZHyAo09gPtBQ9/s200/Lightning.png" width="67" /></a></div>
<div class="MsoNormal" style="line-height: normal; margin-bottom: 12.0pt;">
<span style="font-family: "trebuchet ms" , sans-serif; font-size: 12.0pt;">Last week we started an overall
inspection of the powerful little Excel tool whimsically name <b>Sparklines</b>.<o:p></o:p></span></div>
<div class="MsoNormal" style="line-height: normal; margin-bottom: 12.0pt;">
<span style="font-family: "trebuchet ms" , sans-serif; font-size: 12.0pt;">These <b><i>Miniature Charts</i></b> fit into
a single cell or range in your worksheet and can provide you with a visual
representation of your data <b><i>without</i></b> having to resort to a
full-blown chart.</span><span style="font-family: "trebuchet ms" , sans-serif; font-size: 12.0pt;"><o:p></o:p></span></div>
<div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in;">
<span style="font-family: "trebuchet ms" , sans-serif; font-size: 12.0pt;">Among
the several advantages for using <b>Sparklines,</b>
(Simplifying your visual representations of your data, creating Mini-Trendlines,
etc.), they are also quite <b><i>versatile</i></b>. Take, for instance, the
following:<b><i><o:p></o:p></i></b></span></div>
<div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in;">
<br /></div>
<div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; tab-stops: list .5in;">
<b><span style="font-family: "trebuchet ms" , sans-serif; font-size: 12.0pt;">Changing Sparkline
Types<o:p></o:p></span></b></div>
<div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; tab-stops: list .5in;">
<span style="font-family: "trebuchet ms" , sans-serif; font-size: 12.0pt;">If,
let’s say, you have created a Sparkline group, and changed you mind as to the
type of design element you wish to use – <b><i>No Problem!</i></b> Simply select your
Sparkline group and choose <b>Sparkline
Tools/Design</b>. Then select one of the alternative types from the (guess what…)
“<b>Type”</b> group.<o:p></o:p></span></div>
<div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; tab-stops: list .5in;">
<br /></div>
<div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; tab-stops: list .5in;">
<b><span style="font-family: "trebuchet ms" , sans-serif; font-size: 12.0pt;">Removing Sparklines<o:p></o:p></span></b></div>
<div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; tab-stops: list .5in;">
<span style="font-family: "trebuchet ms" , sans-serif; font-size: 12.0pt;">Very
easy, and only slightly more difficult than you would expect. Once again,
simply select the Sparkline group you wish to delete, choose <b>Sparkline Tools/Design</b>, and click the “<b>Clear</b>” button.<o:p></o:p></span></div>
<div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; tab-stops: list .5in;">
<br /></div>
<div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in;">
<b><span style="font-family: "trebuchet ms" , sans-serif; font-size: 12.0pt;">Sparklines!</span></b><span style="font-family: "trebuchet ms" , sans-serif; font-size: 12.0pt;"> <i>Simple</i>,
<i>Powerful</i>, and <i>Effective</i>. Next week we will look at some ways of highlighting
values, and other customizing you can freely do with <b><i>Sparklines!</i></b></span><span style="font-family: "trebuchet ms" , sans-serif; font-size: 12.0pt;"><o:p></o:p></span></div>
Bob DeLaMartrehttp://www.blogger.com/profile/03761089085420930697noreply@blogger.com0tag:blogger.com,1999:blog-3794737577784583819.post-35429150596027639172017-10-12T14:28:00.000-07:002017-10-12T14:28:28.092-07:00Sparklines Series #1<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEi15CLrSajxSAVrytjGbzKJh2el4YjhfSCt1N-mQ7a_A7VmYoWjPFhcloo2mdoBFh9O1Wgw7_bXz4cm2dnpQiULUYXAEonZoaEG8jecxGxAknPj4QQjNlynS_qEM9n0bteBXmSnTYprZzbt/s1600/TeslaCoil.png" imageanchor="1" style="clear: left; float: left; margin-bottom: 1em; margin-right: 1em;"><img border="0" data-original-height="445" data-original-width="209" height="200" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEi15CLrSajxSAVrytjGbzKJh2el4YjhfSCt1N-mQ7a_A7VmYoWjPFhcloo2mdoBFh9O1Wgw7_bXz4cm2dnpQiULUYXAEonZoaEG8jecxGxAknPj4QQjNlynS_qEM9n0bteBXmSnTYprZzbt/s200/TeslaCoil.png" width="93" /></a></div>
<div class="MsoNormal" style="line-height: normal; margin-bottom: 12.0pt;">
<span style="font-family: "Trebuchet MS",sans-serif; font-size: 12.0pt; mso-bidi-font-family: Calibri; mso-fareast-font-family: "Times New Roman";">Today we are going to start a
brief series of posts revolving around the powerful little (it truly is “little”)
Excel tool referred to as <b>Sparklines</b>.<o:p></o:p></span></div>
<div class="MsoNormal" style="line-height: normal; margin-bottom: 12.0pt;">
<span style="font-family: "Trebuchet MS",sans-serif; font-size: 12.0pt; mso-bidi-font-family: Calibri; mso-fareast-font-family: "Times New Roman";">These amazing miniature appliances
were first introduced in Excel 2010, and they have gained a fair amount of
popularity. <b>Sparklines</b> simply are <b><i>Miniature
Charts</i></b> that fit into a single cell or range in your worksheet. As with
any chart, they provide you with a visual representation of your data, but in
this case, they do so <b><i>without</i></b> having to resort to a
full-blown chart.</span><span style="font-family: "Trebuchet MS",sans-serif; font-size: 12.0pt; mso-bidi-font-family: "Times New Roman"; mso-fareast-font-family: "Times New Roman";"><o:p></o:p></span></div>
<div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in;">
<span style="font-family: "Trebuchet MS",sans-serif; font-size: 12.0pt; mso-bidi-font-family: Calibri; mso-fareast-font-family: "Times New Roman";">Although
these tiny charts are typically used in a separate cell range adjacent to your
data, they actually reside in the <b><i>background</i></b> of a cell similar to
formatting. It can overlay (or underlay)
other contents of a cell, adding creative possibilities for displaying your
information.</span><span style="font-family: "Trebuchet MS",sans-serif; font-size: 12.0pt; mso-bidi-font-family: "Times New Roman"; mso-fareast-font-family: "Times New Roman";"><o:p></o:p></span></div>
<div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in;">
<br /></div>
<div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in;">
<span style="font-family: "Trebuchet MS",sans-serif; font-size: 12.0pt; mso-bidi-font-family: Calibri; mso-fareast-font-family: "Times New Roman";">There
are several advantages for using <b>Sparklines</b>.
They can <b><i>Simplify</i></b> your visual representations of your data, and can do
so with just a couple of clicks! They
are particularly useful in creating <b><i>Mini-Trendlines</i></b> for tracking a
myriad of business metrics. Insert them in an adjacent column of a data-heavy
report, and you get a result that can tell you at a glance the direction of
your department.<o:p></o:p></span></div>
<div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in;">
<br /></div>
<div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in;">
<b><span style="font-family: "Trebuchet MS",sans-serif; font-size: 12.0pt; mso-bidi-font-family: Calibri; mso-fareast-font-family: "Times New Roman";">Creating Sparklines</span></b><span style="font-family: "Trebuchet MS",sans-serif; font-size: 12.0pt; mso-bidi-font-family: "Times New Roman"; mso-fareast-font-family: "Times New Roman";"><o:p></o:p></span></div>
<div class="MsoListParagraphCxSpFirst" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; margin-left: .45in; margin-right: 0in; margin-top: 0in; mso-add-space: auto; mso-list: l0 level1 lfo1; tab-stops: list .5in; text-indent: -.45in;">
<!--[if !supportLists]--><span style="font-family: "Trebuchet MS",sans-serif; font-size: 12.0pt; mso-bidi-font-family: "Trebuchet MS"; mso-fareast-font-family: "Trebuchet MS";">1.<span style="font-family: "Times New Roman"; font-size: 7pt; font-stretch: normal; font-variant-numeric: normal; line-height: normal;">
</span></span><!--[endif]--><span style="font-family: "Trebuchet MS",sans-serif; font-size: 12.0pt; mso-bidi-font-family: Calibri; mso-fareast-font-family: "Times New Roman";">Select
a group of cells in which you want to<b><i> insert </i></b>your Sparklines.</span><span style="font-family: "Trebuchet MS",sans-serif; font-size: 12.0pt; mso-bidi-font-family: "Times New Roman"; mso-fareast-font-family: "Times New Roman";"><o:p></o:p></span></div>
<div class="MsoListParagraphCxSpMiddle" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; margin-left: .45in; margin-right: 0in; margin-top: 0in; mso-add-space: auto; mso-list: l0 level1 lfo1; tab-stops: list .5in; text-indent: -.45in;">
<!--[if !supportLists]--><span style="font-family: "Trebuchet MS",sans-serif; font-size: 12.0pt; mso-bidi-font-family: "Trebuchet MS"; mso-fareast-font-family: "Trebuchet MS";">2.<span style="font-family: "Times New Roman"; font-size: 7pt; font-stretch: normal; font-variant-numeric: normal; line-height: normal;">
</span></span><!--[endif]--><span style="font-family: "Trebuchet MS",sans-serif; font-size: 12.0pt; mso-bidi-font-family: Calibri; mso-fareast-font-family: "Times New Roman";">Find
the <b>Sparklines</b> group on the<b><i> Insert</i></b> ribbon, and click the type
of <b><i>Sparkline</i></b>
that you want to create, (<b><i>Line</i></b><i>, <b>Column</b></i>, or even <b><i>Win/Loss</i></b>).</span><span style="font-family: "Trebuchet MS",sans-serif; font-size: 12.0pt; mso-bidi-font-family: "Times New Roman"; mso-fareast-font-family: "Times New Roman";"><o:p></o:p></span></div>
<div class="MsoListParagraphCxSpLast" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; margin-left: .45in; margin-right: 0in; margin-top: 0in; mso-add-space: auto; mso-list: l0 level1 lfo1; tab-stops: list .5in; text-indent: -.45in;">
<!--[if !supportLists]--><span style="font-family: "Trebuchet MS",sans-serif; font-size: 12.0pt; mso-bidi-font-family: "Trebuchet MS"; mso-fareast-font-family: "Trebuchet MS";">3.<span style="font-family: "Times New Roman"; font-size: 7pt; font-stretch: normal; font-variant-numeric: normal; line-height: normal;">
</span></span><!--[endif]--><span style="font-family: "Trebuchet MS",sans-serif; font-size: 12.0pt; mso-bidi-font-family: Calibri; mso-fareast-font-family: "Times New Roman";">In the <b>Data</b>
box, select and insert the range of the cells that contain the data on which
you want to create your Sparklines.</span><span style="font-family: "Trebuchet MS",sans-serif; font-size: 12.0pt; mso-bidi-font-family: "Times New Roman"; mso-fareast-font-family: "Times New Roman";"><o:p></o:p></span></div>
<div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in;">
<br /></div>
<div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in;">
<b><i><span style="font-family: "Trebuchet MS",sans-serif; font-size: 12.0pt; mso-bidi-font-family: Calibri; mso-fareast-font-family: "Times New Roman";">Bamm -
Sparklines!</span></i></b><span style="font-family: "Trebuchet MS", sans-serif; font-size: 12pt;"> Simple
as that! Next week we will look at some creative ways of customizing these
little gems, so you can truly make your Excel reports, well, <b><i>Sparkle!</i></b></span></div>
Bob DeLaMartrehttp://www.blogger.com/profile/03761089085420930697noreply@blogger.com0tag:blogger.com,1999:blog-3794737577784583819.post-19055481633969860002017-10-05T11:30:00.000-07:002017-10-05T11:30:04.484-07:00Precedence<div class="separator" style="clear: both; text-align: center;">
</div>
<div class="separator" style="clear: both; text-align: center;">
</div>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEji8lunKMJ_pN07iKpt6RSEcejgCKUyGLKO3rUsHj6H3o0Ed1TBdoNSo__GIC9X-uDTQUIR282Q9jlcPnWSzpKnQFZivTqNEFX52LI-_ofpOSz5uNQYK6Cwqc5ZJhw2tAfR4Z5AVDPFCfCQ/s1600/Precedence3.png" imageanchor="1" style="clear: left; float: left; margin-bottom: 1em; margin-right: 1em;"><img border="0" data-original-height="290" data-original-width="250" height="200" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEji8lunKMJ_pN07iKpt6RSEcejgCKUyGLKO3rUsHj6H3o0Ed1TBdoNSo__GIC9X-uDTQUIR282Q9jlcPnWSzpKnQFZivTqNEFX52LI-_ofpOSz5uNQYK6Cwqc5ZJhw2tAfR4Z5AVDPFCfCQ/s200/Precedence3.png" width="171" /></a></div>
<div class="MsoNoSpacing">
<span style="font-family: "Trebuchet MS", sans-serif; font-size: 12pt;">As anyone who works with our favorite
spreadsheet program for any length of time knows, it is very apparent that </span><b style="font-family: "Trebuchet MS", sans-serif; font-size: 12pt;">Excel</b><span style="font-family: "Trebuchet MS", sans-serif; font-size: 12pt;"> and </span><b style="font-family: "Trebuchet MS", sans-serif; font-size: 12pt;">Math </b><span style="font-family: "Trebuchet MS", sans-serif; font-size: 12pt;">go hand-in-hand. That being the case, this week we will take a
look at how Excel controls the order of</span><i style="font-family: "Trebuchet MS", sans-serif; font-size: 12pt;"> Precedence</i><span style="font-family: "Trebuchet MS", sans-serif; font-size: 12pt;">.</span></div>
<div class="MsoNoSpacing">
<br /></div>
<div class="MsoNoSpacing">
<b><span style="font-family: "Trebuchet MS",sans-serif; font-size: 12.0pt; mso-bidi-font-size: 11.0pt;">For
<i>Arithmetic</i>, the order of <i>Precedence</i> for symbols is as follows:<o:p></o:p></span></b></div>
<div class="MsoNoSpacing" style="margin-left: .5in; mso-list: l0 level1 lfo1; text-indent: -.25in;">
<!--[if !supportLists]--><span style="font-family: "Courier New"; font-size: 12.0pt; mso-bidi-font-size: 11.0pt; mso-fareast-font-family: "Courier New";">o<span style="font-family: "Times New Roman"; font-size: 7pt; font-stretch: normal; font-variant-numeric: normal; line-height: normal;"> </span></span><!--[endif]--><b><span style="font-family: "Trebuchet MS",sans-serif; font-size: 12.0pt; mso-bidi-font-size: 11.0pt;">Negative </span></b><span style="font-family: "Trebuchet MS",sans-serif; font-size: 12.0pt; mso-bidi-font-size: 11.0pt;">=<b> </b></span><b><span style="font-family: "Trebuchet MS",sans-serif; font-size: 14.0pt; mso-bidi-font-size: 11.0pt;">-</span></b><b><span style="font-family: "Trebuchet MS",sans-serif; font-size: 12.0pt; mso-bidi-font-size: 11.0pt;"><o:p></o:p></span></b></div>
<div class="MsoNoSpacing" style="margin-left: .5in; mso-list: l0 level1 lfo1; text-indent: -.25in;">
<!--[if !supportLists]--><span style="font-family: "Courier New"; font-size: 12.0pt; mso-bidi-font-size: 11.0pt; mso-fareast-font-family: "Courier New";">o<span style="font-family: "Times New Roman"; font-size: 7pt; font-stretch: normal; font-variant-numeric: normal; line-height: normal;"> </span></span><!--[endif]--><b><span style="font-family: "Trebuchet MS",sans-serif; font-size: 12.0pt; mso-bidi-font-size: 11.0pt;">Percent </span></b><span style="font-family: "Trebuchet MS",sans-serif; font-size: 12.0pt; mso-bidi-font-size: 11.0pt;">=<b> </b></span><b><span style="font-family: "Trebuchet MS",sans-serif; font-size: 14.0pt; mso-bidi-font-size: 11.0pt;">%</span></b><b><span style="font-family: "Trebuchet MS",sans-serif; font-size: 12.0pt; mso-bidi-font-size: 11.0pt;"><o:p></o:p></span></b></div>
<div class="MsoNoSpacing" style="margin-left: .5in; mso-list: l0 level1 lfo1; text-indent: -.25in;">
<!--[if !supportLists]--><span style="font-family: "Courier New"; font-size: 12.0pt; mso-bidi-font-size: 11.0pt; mso-fareast-font-family: "Courier New";">o<span style="font-family: "Times New Roman"; font-size: 7pt; font-stretch: normal; font-variant-numeric: normal; line-height: normal;"> </span></span><!--[endif]--><b><span style="font-family: "Trebuchet MS",sans-serif; font-size: 12.0pt; mso-bidi-font-size: 11.0pt;">Exponential </span></b><span style="font-family: "Trebuchet MS",sans-serif; font-size: 12.0pt; mso-bidi-font-size: 11.0pt;">=<b> </b></span><b><span style="font-family: "Trebuchet MS",sans-serif; font-size: 14.0pt; mso-bidi-font-size: 11.0pt;">^</span></b><b><span style="font-family: "Trebuchet MS",sans-serif; font-size: 12.0pt; mso-bidi-font-size: 11.0pt;">
<o:p></o:p></span></b></div>
<div class="MsoNoSpacing" style="margin-left: .5in; mso-list: l0 level1 lfo1; text-indent: -.25in;">
<!--[if !supportLists]--><span style="font-family: "Courier New"; font-size: 12.0pt; mso-bidi-font-size: 11.0pt; mso-fareast-font-family: "Courier New";">o<span style="font-family: "Times New Roman"; font-size: 7pt; font-stretch: normal; font-variant-numeric: normal; line-height: normal;"> </span></span><!--[endif]--><b><span style="font-family: "Trebuchet MS",sans-serif; font-size: 12.0pt; mso-bidi-font-size: 11.0pt;">Multiplication </span></b><span style="font-family: "Trebuchet MS",sans-serif; font-size: 12.0pt; mso-bidi-font-size: 11.0pt;">=</span><b><span style="font-family: "Trebuchet MS",sans-serif; font-size: 14.0pt; mso-bidi-font-size: 11.0pt;"> *</span></b><b><span style="font-family: "Trebuchet MS",sans-serif; font-size: 12.0pt; mso-bidi-font-size: 11.0pt;"><o:p></o:p></span></b></div>
<div class="MsoNoSpacing" style="margin-left: .5in; mso-list: l0 level1 lfo1; text-indent: -.25in;">
<!--[if !supportLists]--><span style="font-family: "Courier New"; font-size: 12.0pt; mso-bidi-font-size: 11.0pt; mso-fareast-font-family: "Courier New";">o<span style="font-family: "Times New Roman"; font-size: 7pt; font-stretch: normal; font-variant-numeric: normal; line-height: normal;"> </span></span><!--[endif]--><b><span style="font-family: "Trebuchet MS",sans-serif; font-size: 12.0pt; mso-bidi-font-size: 11.0pt;">Division </span></b><span style="font-family: "Trebuchet MS",sans-serif; font-size: 12.0pt; mso-bidi-font-size: 11.0pt;">=
</span><b><span style="font-family: "Trebuchet MS",sans-serif; font-size: 14.0pt; mso-bidi-font-size: 11.0pt;">/</span></b><b><span style="font-family: "Trebuchet MS",sans-serif; font-size: 12.0pt; mso-bidi-font-size: 11.0pt;"><o:p></o:p></span></b></div>
<div class="MsoNoSpacing" style="margin-left: .5in; mso-list: l0 level1 lfo1; text-indent: -.25in;">
<!--[if !supportLists]--><span style="font-family: "Courier New"; font-size: 12.0pt; mso-bidi-font-size: 11.0pt; mso-fareast-font-family: "Courier New";">o<span style="font-family: "Times New Roman"; font-size: 7pt; font-stretch: normal; font-variant-numeric: normal; line-height: normal;"> </span></span><!--[endif]--><b><span style="font-family: "Trebuchet MS",sans-serif; font-size: 12.0pt; mso-bidi-font-size: 11.0pt;">Addition </span></b><span style="font-family: "Trebuchet MS",sans-serif; font-size: 12.0pt; mso-bidi-font-size: 11.0pt;">=<b> </b></span><b><span style="font-family: "Trebuchet MS",sans-serif; font-size: 14.0pt; mso-bidi-font-size: 11.0pt;">+</span></b><b><span style="font-family: "Trebuchet MS",sans-serif; font-size: 12.0pt; mso-bidi-font-size: 11.0pt;"><o:p></o:p></span></b></div>
<div class="MsoNoSpacing" style="margin-left: .5in; mso-list: l0 level1 lfo1; text-indent: -.25in;">
<!--[if !supportLists]--><span style="font-family: "Courier New"; font-size: 12.0pt; mso-bidi-font-size: 11.0pt; mso-fareast-font-family: "Courier New";">o<span style="font-family: "Times New Roman"; font-size: 7pt; font-stretch: normal; font-variant-numeric: normal; line-height: normal;"> </span></span><!--[endif]--><b><span style="font-family: "Trebuchet MS",sans-serif; font-size: 12.0pt; mso-bidi-font-size: 11.0pt;">Subtraction </span></b><span style="font-family: "Trebuchet MS",sans-serif; font-size: 12.0pt; mso-bidi-font-size: 11.0pt;">=<b> </b></span><b><span style="font-family: "Trebuchet MS",sans-serif; font-size: 14.0pt; mso-bidi-font-size: 11.0pt;">-</span></b><b><span style="font-family: "Trebuchet MS",sans-serif; font-size: 12.0pt; mso-bidi-font-size: 11.0pt;"><o:p></o:p></span></b></div>
<div class="MsoNoSpacing">
<br /></div>
<div class="MsoNoSpacing">
<b><span style="font-family: "Trebuchet MS",sans-serif; font-size: 12.0pt; mso-bidi-font-size: 11.0pt;">For
<i>Comparing </i>data, the order of <i>Precedence </i>is as follows:<o:p></o:p></span></b></div>
<div class="MsoNoSpacing" style="margin-left: .5in; mso-list: l1 level1 lfo2; text-indent: -.25in;">
<!--[if !supportLists]--><span style="font-family: "Courier New"; font-size: 12.0pt; mso-bidi-font-size: 11.0pt; mso-fareast-font-family: "Courier New";">o<span style="font-family: "Times New Roman"; font-size: 7pt; font-stretch: normal; font-variant-numeric: normal; line-height: normal;"> </span></span><!--[endif]--><b><span style="font-family: "Trebuchet MS",sans-serif; font-size: 12.0pt; mso-bidi-font-size: 11.0pt;">Less than </span></b><span style="font-family: "Trebuchet MS",sans-serif; font-size: 12.0pt; mso-bidi-font-size: 11.0pt;">=<b> </b></span><b><span style="font-family: "Trebuchet MS",sans-serif; font-size: 14.0pt; mso-bidi-font-size: 11.0pt;"><</span></b><b><span style="font-family: "Trebuchet MS",sans-serif; font-size: 12.0pt; mso-bidi-font-size: 11.0pt;"><o:p></o:p></span></b></div>
<div class="MsoNoSpacing" style="margin-left: .5in; mso-list: l1 level1 lfo2; text-indent: -.25in;">
<!--[if !supportLists]--><span style="font-family: "Courier New"; font-size: 12.0pt; mso-bidi-font-size: 11.0pt; mso-fareast-font-family: "Courier New";">o<span style="font-family: "Times New Roman"; font-size: 7pt; font-stretch: normal; font-variant-numeric: normal; line-height: normal;"> </span></span><!--[endif]--><b><span style="font-family: "Trebuchet MS",sans-serif; font-size: 12.0pt; mso-bidi-font-size: 11.0pt;">Greater than </span></b><span style="font-family: "Trebuchet MS",sans-serif; font-size: 12.0pt; mso-bidi-font-size: 11.0pt;">=<b> </b></span><b><span style="font-family: "Trebuchet MS",sans-serif; font-size: 14.0pt; mso-bidi-font-size: 11.0pt;">></span></b><b><span style="font-family: "Trebuchet MS",sans-serif; font-size: 12.0pt; mso-bidi-font-size: 11.0pt;"><o:p></o:p></span></b></div>
<div class="MsoNoSpacing" style="margin-left: .5in; mso-list: l1 level1 lfo2; text-indent: -.25in;">
<!--[if !supportLists]--><span style="font-family: "Courier New"; font-size: 12.0pt; mso-bidi-font-size: 11.0pt; mso-fareast-font-family: "Courier New";">o<span style="font-family: "Times New Roman"; font-size: 7pt; font-stretch: normal; font-variant-numeric: normal; line-height: normal;"> </span></span><!--[endif]--><b><span style="font-family: "Trebuchet MS",sans-serif; font-size: 12.0pt; mso-bidi-font-size: 11.0pt;">Less than or equal to </span></b><span style="font-family: "Trebuchet MS",sans-serif; font-size: 12.0pt; mso-bidi-font-size: 11.0pt;">=<b> </b></span><b><span style="font-family: "Trebuchet MS",sans-serif; font-size: 14.0pt; mso-bidi-font-size: 11.0pt;"><=</span></b><b><span style="font-family: "Trebuchet MS",sans-serif; font-size: 12.0pt; mso-bidi-font-size: 11.0pt;"><o:p></o:p></span></b></div>
<div class="MsoNoSpacing" style="margin-left: .5in; mso-list: l1 level1 lfo2; text-indent: -.25in;">
<!--[if !supportLists]--><span style="font-family: "Courier New"; font-size: 12.0pt; mso-bidi-font-size: 11.0pt; mso-fareast-font-family: "Courier New";">o<span style="font-family: "Times New Roman"; font-size: 7pt; font-stretch: normal; font-variant-numeric: normal; line-height: normal;"> </span></span><!--[endif]--><b><span style="font-family: "Trebuchet MS",sans-serif; font-size: 12.0pt; mso-bidi-font-size: 11.0pt;">Greater than or equal to </span></b><span style="font-family: "Trebuchet MS",sans-serif; font-size: 12.0pt; mso-bidi-font-size: 11.0pt;">=<b> </b></span><b><span style="font-family: "Trebuchet MS",sans-serif; font-size: 14.0pt; mso-bidi-font-size: 11.0pt;">>=</span></b><b><span style="font-family: "Trebuchet MS",sans-serif; font-size: 12.0pt; mso-bidi-font-size: 11.0pt;"><o:p></o:p></span></b></div>
<div class="MsoNoSpacing" style="margin-left: .5in; mso-list: l1 level1 lfo2; text-indent: -.25in;">
<!--[if !supportLists]--><span style="font-family: "Courier New"; font-size: 12.0pt; mso-bidi-font-size: 11.0pt; mso-fareast-font-family: "Courier New";">o<span style="font-family: "Times New Roman"; font-size: 7pt; font-stretch: normal; font-variant-numeric: normal; line-height: normal;"> </span></span><!--[endif]--><b><span style="font-family: "Trebuchet MS",sans-serif; font-size: 12.0pt; mso-bidi-font-size: 11.0pt;">Does not equal </span></b><span style="font-family: "Trebuchet MS",sans-serif; font-size: 12.0pt; mso-bidi-font-size: 11.0pt;">=<b> </b></span><b><span style="font-family: "Trebuchet MS",sans-serif; font-size: 14.0pt; mso-bidi-font-size: 11.0pt;"><></span></b><b><span style="font-family: "Trebuchet MS",sans-serif; font-size: 12.0pt; mso-bidi-font-size: 11.0pt;"><o:p></o:p></span></b></div>
<div class="MsoNoSpacing">
<br /></div>
<div class="MsoNoSpacing">
<span style="font-family: "Trebuchet MS",sans-serif; font-size: 12.0pt; mso-bidi-font-size: 11.0pt;">You will note that the foregoing differs
at times from traditional math. As in nearly all of mathematics, however, you
can control the order of precedence by using parentheses.<o:p></o:p></span></div>
<div class="MsoNoSpacing">
<br /></div>
<br />
<div class="MsoNoSpacing">
<b><span style="font-family: "Trebuchet MS",sans-serif; font-size: 14.0pt; mso-bidi-font-size: 11.0pt;">Math.</span></b><span style="font-family: "Trebuchet MS",sans-serif; font-size: 14.0pt; mso-bidi-font-size: 11.0pt;">
</span><span style="font-family: "Trebuchet MS",sans-serif; font-size: 12.0pt; mso-bidi-font-size: 11.0pt;">Every <b>Excel
Guru’s </b>favorite subject in school!<o:p></o:p></span></div>
Bob DeLaMartrehttp://www.blogger.com/profile/03761089085420930697noreply@blogger.com0tag:blogger.com,1999:blog-3794737577784583819.post-3732645296131179282017-09-28T12:06:00.000-07:002017-09-28T12:06:02.290-07:00Rounding<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjxImMWmamSxssP31urnZOXxPtW_RYoygv5eg72M04gQ94GVtbY_PGu-fa8qo4_1glnsceOVjLzwMu3OXbfFxWSMo-OGrecxBnIlHlaIRewjEzqQaK36eh3AYs43fywoeO4gKgPcEt4CeEf/s1600/RoundingStack.png" imageanchor="1" style="clear: left; float: left; margin-bottom: 1em; margin-right: 1em;"><img border="0" data-original-height="614" data-original-width="194" height="200" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjxImMWmamSxssP31urnZOXxPtW_RYoygv5eg72M04gQ94GVtbY_PGu-fa8qo4_1glnsceOVjLzwMu3OXbfFxWSMo-OGrecxBnIlHlaIRewjEzqQaK36eh3AYs43fywoeO4gKgPcEt4CeEf/s200/RoundingStack.png" width="63" /></a></div>
<div class="MsoNoSpacing">
<span style="font-family: "Trebuchet MS",sans-serif; font-size: 12.0pt; mso-bidi-font-size: 11.0pt;">As has been observed in many scientific
and business circle,<b><i> Precision </i></b>would appear to always be preferable. For example, <b><i>.98977 </i></b>is more precise
that <b><i>1.0</i></b>. There are times, however, when precision is<b><i> Not</i></b>
desirable, and may even not make much sense.<o:p></o:p></span></div>
<div class="MsoNoSpacing">
<span style="font-family: "Trebuchet MS",sans-serif; font-size: 12.0pt; mso-bidi-font-size: 11.0pt;">For instance, let’s say you are staffing
for a project, and you have calculated that you need <b><i>11.92</i></b> people to complete
the job. Obviously, it would be
difficult to find that <b><i>.92</i></b> person (<b>Please Note</b> that we are not taking number of <b><i>person-hours</i></b> into
consideration) so you <b><i>Round</i></b> up to <b>12</b>. The same may be said
about <b><i>currency</i></b>, as fractions of a penny may not produce valid data.<o:p></o:p></span></div>
<div class="MsoNoSpacing">
<span style="font-family: "Trebuchet MS",sans-serif; font-size: 12.0pt; mso-bidi-font-size: 11.0pt;"> <o:p></o:p></span></div>
<div class="MsoNoSpacing">
<span style="font-family: "Trebuchet MS",sans-serif; font-size: 12.0pt; mso-bidi-font-size: 11.0pt;">You can, of course, use formatting in
Excel that will make your numbers <b><i>Appea</i></b>r to be rounded. The trouble with relying on formatting, however,
is that Excel <b><i>retains the precision</i></b> in the background, and any calculations
will be based on that precision. If you are dealing with numbers
representing currency that have four decimal places, you would obviously want
to express the results in no more that 2 decimal places, so you can format for
showing exactly that (the unwanted Precision will nonetheless be lurking in the
invisible details)! <br />
<o:p></o:p></span></div>
<div class="MsoNoSpacing">
<span style="font-family: "Trebuchet MS",sans-serif; font-size: 12.0pt; mso-bidi-font-size: 11.0pt;">So what is the solution? Enter the<b> ROUND</b> function! </span><span lang="EN" style="font-family: "Trebuchet MS",sans-serif; font-size: 12.0pt; mso-ansi-language: EN; mso-bidi-font-size: 11.0pt;">The syntax for the <b>ROUND </b>function
is: <b>ROUND(
number, digits )<o:p></o:p></b></span></div>
<div class="MsoNoSpacing">
<br /></div>
<div class="MsoNoSpacing">
<b><span lang="EN" style="font-family: "Trebuchet MS",sans-serif; font-size: 12.0pt; mso-ansi-language: EN; mso-bidi-font-size: 11.0pt;">A Few Examples are Illustrated Below:</span></b><span style="font-family: "Trebuchet MS",sans-serif; font-size: 12.0pt; mso-bidi-font-size: 11.0pt;"><o:p></o:p></span></div>
<div class="MsoNoSpacing">
<span lang="EN" style="font-family: "Trebuchet MS",sans-serif; font-size: 12.0pt; mso-ansi-language: EN; mso-bidi-font-size: 11.0pt;">In the following
worksheet, <b>Column B</b> contains the
rounding functions: </span><span style="font-family: "Trebuchet MS",sans-serif; font-size: 12.0pt; mso-bidi-font-size: 11.0pt;"><o:p></o:p></span></div>
<div class="MsoNoSpacing">
<span lang="EN" style="font-family: "Trebuchet MS",sans-serif; font-size: 12.0pt; mso-ansi-language: EN; mso-bidi-font-size: 11.0pt;">1.
B1 has: <b>=ROUND(A1, 2) </b>and therefore rounds to two decimal places.</span><span style="font-family: "Trebuchet MS",sans-serif; font-size: 12.0pt; mso-bidi-font-size: 11.0pt;"><o:p></o:p></span></div>
<div class="MsoNoSpacing">
<span lang="EN" style="font-family: "Trebuchet MS",sans-serif; font-size: 12.0pt; mso-ansi-language: EN; mso-bidi-font-size: 11.0pt;">2.
In B2, <b>=ROUND(A1, 0) </b>is being used
to round to zero decimals.</span><span style="font-family: "Trebuchet MS",sans-serif; font-size: 12.0pt; mso-bidi-font-size: 11.0pt;"><o:p></o:p></span></div>
<div class="MsoNoSpacing">
<span lang="EN" style="font-family: "Trebuchet MS",sans-serif; font-size: 12.0pt; mso-ansi-language: EN; mso-bidi-font-size: 11.0pt;">3.
In B3, more<b><i> extreme</i></b> rounding is being achieved by using <b>=ROUND(A3, -1)</b></span><span style="font-family: "Trebuchet MS",sans-serif; font-size: 12.0pt; mso-bidi-font-size: 11.0pt;"><o:p></o:p></span></div>
<div class="MsoNoSpacing">
<span lang="EN" style="font-family: "Trebuchet MS",sans-serif; font-size: 12.0pt; mso-ansi-language: EN; mso-bidi-font-size: 11.0pt;">4.
B4 uses the same function as B3 and applies it to B4: <b>=ROUND(A4,
-1)</b></span></div>
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEivRZXDXGoX6fgmNjpreNhoW3HfRh4cLFdjlbzIa3mNLvwqCPcq7r6IyHxdNXTOEuKAhD72VdX5xYtCS-pKisLSnMVBFWW6qa0yqPGizHm7uN4iHuMm8fOG5uf3yY05F8SNLlLqjvX2_NGY/s1600/RoundIllustration.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="215" data-original-width="320" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEivRZXDXGoX6fgmNjpreNhoW3HfRh4cLFdjlbzIa3mNLvwqCPcq7r6IyHxdNXTOEuKAhD72VdX5xYtCS-pKisLSnMVBFWW6qa0yqPGizHm7uN4iHuMm8fOG5uf3yY05F8SNLlLqjvX2_NGY/s1600/RoundIllustration.png" /></a></div>
<div class="MsoNoSpacing">
<span style="font-family: "Trebuchet MS",sans-serif; font-size: 12.0pt; mso-bidi-font-size: 11.0pt;"><b>Note:</b> If you always want Excel to round up, you can
use the <b>ROUNDUP</b> function. In our illustration, this would result in B3
being 30 rather than 20. The<b> ROUNDDOWN</b> function does (no surprise)
just the opposite.<br />
<br />
Sometimes<b><i> Precision</i></b> is simply not the best choice. In such cases, use <b>ROUND</b>!<o:p></o:p></span></div>
Bob DeLaMartrehttp://www.blogger.com/profile/03761089085420930697noreply@blogger.com1tag:blogger.com,1999:blog-3794737577784583819.post-9532557060177143432017-09-18T13:54:00.003-07:002017-09-18T13:55:26.851-07:00Pivot Tables – A Final Look (for now…)<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgA4_o4LfMmyR0h4KD-XPpE9-G-l4OCk-9hM5959jLwGFn7w3vw3TpxBMRxVFldPYYxgZ2j-_tww7CwC5F7mHAqm5v9YNBnMu9CTZb7knj_YiK4pZyh2q9GZll49HIcS8-4RBm69lBuOITF/s1600/Pivot-FinalLook.png" imageanchor="1" style="clear: left; float: left; margin-bottom: 1em; margin-right: 1em;"><img border="0" data-original-height="704" data-original-width="185" height="320" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgA4_o4LfMmyR0h4KD-XPpE9-G-l4OCk-9hM5959jLwGFn7w3vw3TpxBMRxVFldPYYxgZ2j-_tww7CwC5F7mHAqm5v9YNBnMu9CTZb7knj_YiK4pZyh2q9GZll49HIcS8-4RBm69lBuOITF/s320/Pivot-FinalLook.png" width="84" /></a></div>
<div class="MsoNoSpacing">
<span style="font-family: "trebuchet ms" , sans-serif; font-size: 12.0pt;">As is apparent in our exploration of <b>Pivot Tables</b> in the last three weeks,
they are <b><i>powerful</i></b>, <b><i>versatile</i></b>, and a <b><i>quick</i></b>
way to do analysis on the fly. This week, we will wrap up this series with a
quick look at some <b><i>additional ways</i></b> to garner more utility out of this vital tool.<o:p></o:p></span></div>
<div class="MsoNoSpacing">
<br /></div>
<div class="MsoNoSpacing">
<b><span style="font-family: "trebuchet ms" , sans-serif; font-size: 12.0pt;">Changing
the Summary Function<o:p></o:p></span></b></div>
<div class="MsoNoSpacing">
<span style="font-family: "trebuchet ms" , sans-serif; font-size: 12.0pt;">Go to any cell within the <b><i>Values</i></b>
section of your Pivot Table and Right-Click. Then select <b>Summarize Values By</b> and click on the calculation of your choice (Sum,
Count, Average, etc).<o:p></o:p></span></div>
<div class="MsoNoSpacing">
<br /></div>
<div class="MsoNoSpacing">
<b><span style="font-family: "trebuchet ms" , sans-serif; font-size: 12.0pt;">Changing
How Values are Shown<o:p></o:p></span></b></div>
<div class="MsoNoSpacing">
<span style="font-family: "trebuchet ms" , sans-serif; font-size: 12.0pt;">In a similar way as you change the <b><i>Summary</i></b>
function, Right-Click any value cell. Then select <b>Show Values As</b> and choose the calculation you wish to use (you may
be surprised at the <b><i>extensive</i></b> list…).<o:p></o:p></span></div>
<div class="MsoNoSpacing">
<br /></div>
<div class="MsoNoSpacing">
<b><span style="font-family: "trebuchet ms" , sans-serif; font-size: 12.0pt;">Give
Your Pivot Table Some Color<o:p></o:p></span></b></div>
<div class="MsoNoSpacing">
<span style="font-family: "trebuchet ms" , sans-serif; font-size: 12.0pt;">Providing some <b><i>Design </i></b>and<b><i> Color</i></b>
can go a long way to making your Pivot Table easier and more pleasant to use.
It can also add a considerable touch of professionalism that is so often
lacking. Simply click on any cell within your table and open the<b> Design</b> tab/ribbon. Note that you can
choose from a variety of <b>Styles</b>, as
well as <b><i>Banded Rows</i></b>, <b><i>Banded Columns</i></b>, and a surprising assortment
of <b>Layout</b> options.<o:p></o:p></span></div>
<div class="MsoNoSpacing">
<br /></div>
<div class="MsoNoSpacing">
<b><span style="font-family: "trebuchet ms" , sans-serif; font-size: 12.0pt;">Pivot
Tables</span></b><span style="font-family: "trebuchet ms" , sans-serif; font-size: 12.0pt;">. <b><i>Powerful</i></b>, <b><i>Versatile</i></b>, <b><i>Quick</i></b>.
Give them a try…<o:p></o:p></span></div>
Bob DeLaMartrehttp://www.blogger.com/profile/03761089085420930697noreply@blogger.com0tag:blogger.com,1999:blog-3794737577784583819.post-11947889396029547882017-09-11T13:59:00.002-07:002017-09-11T13:59:58.205-07:00Pivot Tables – A Third Look<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEi2df4DxwKIoLBbz3SQvsGuuCzJcUMLe2jNdvLNWkHy5QlAe83TMLQPKAOFTvJ8Ia7TPCXJLi9FlhTioIMfIYrko1JVA5NuvuiEgfUikfaM0Nu-n2_36e_37H5Gh9cO5V_ADqsUCL_Q3Mk1/s1600/Slicer.png" imageanchor="1" style="clear: left; float: left; margin-bottom: 1em; margin-right: 1em;"><img border="0" data-original-height="633" data-original-width="169" height="200" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEi2df4DxwKIoLBbz3SQvsGuuCzJcUMLe2jNdvLNWkHy5QlAe83TMLQPKAOFTvJ8Ia7TPCXJLi9FlhTioIMfIYrko1JVA5NuvuiEgfUikfaM0Nu-n2_36e_37H5Gh9cO5V_ADqsUCL_Q3Mk1/s200/Slicer.png" width="53" /></a></div>
<div class="MsoNoSpacing">
<span style="font-family: "Trebuchet MS",sans-serif; font-size: 12.0pt; mso-bidi-font-size: 11.0pt;">As most seasoned Excel users know, there
are multiple ways to <b><i>Filter</i></b> pivot tables. With the advent
of <b><i>Excel
2010</i></b>, however, you have a new, powerful option to use to filter (or
should we say, <b><i>Slice</i></b>) your data. <o:p></o:p></span></div>
<div class="MsoNoSpacing">
<br /></div>
<div class="MsoNoSpacing">
<b><span style="font-family: "Trebuchet MS",sans-serif; font-size: 12.0pt; mso-bidi-font-size: 11.0pt;">Slicers</span></b><span style="font-family: "Trebuchet MS",sans-serif; font-size: 12.0pt; mso-bidi-font-size: 11.0pt;">
give you the choice of using labeled buttons you can click to filter your Pivot
Table data on the fly. In addition to <b><i>quick filtering</i></b>, slicers also
indicate the <b><i>current filters</i></b> being used, which, of course, makes it easier
to understand <b><i>what</i></b> is being shown in a filtered Pivot Table report.<o:p></o:p></span></div>
<div class="MsoNoSpacing">
<br /></div>
<div class="MsoNoSpacing">
<b><span style="font-family: "Trebuchet MS",sans-serif; font-size: 12.0pt; mso-bidi-font-size: 11.0pt;">How-To Slice<o:p></o:p></span></b></div>
<div class="MsoNoSpacing" style="margin-left: .5in; mso-list: l0 level1 lfo1; text-indent: -.25in;">
<!--[if !supportLists]--><span style="font-family: "Trebuchet MS",sans-serif; font-size: 12.0pt; mso-bidi-font-family: "Trebuchet MS"; mso-bidi-font-size: 11.0pt; mso-fareast-font-family: "Trebuchet MS";">1.<span style="font-family: "Times New Roman"; font-size: 7pt; font-stretch: normal; font-variant-numeric: normal; line-height: normal;">
</span></span><!--[endif]--><span style="font-family: "Trebuchet MS",sans-serif; font-size: 12.0pt; mso-bidi-font-size: 11.0pt;">Select your table and then choose
<b><i>PivotTable
Tools, Analyze, Insert</i></b> <b><i>Slicer</i></b><o:p></o:p></span></div>
<div class="MsoNoSpacing" style="margin-left: .5in; mso-list: l0 level1 lfo1; text-indent: -.25in;">
<!--[if !supportLists]--><span style="font-family: "Trebuchet MS",sans-serif; font-size: 12.0pt; mso-bidi-font-family: "Trebuchet MS"; mso-bidi-font-size: 11.0pt; mso-fareast-font-family: "Trebuchet MS";">2.<span style="font-family: "Times New Roman"; font-size: 7pt; font-stretch: normal; font-variant-numeric: normal; line-height: normal;">
</span></span><!--[endif]--><span style="font-family: "Trebuchet MS",sans-serif; font-size: 12.0pt; mso-bidi-font-size: 11.0pt;">Check the fields you want to
filter, then click <b>OK</b><o:p></o:p></span></div>
<div class="MsoNoSpacing" style="margin-left: .5in; mso-list: l0 level1 lfo1; text-indent: -.25in;">
<!--[if !supportLists]--><span style="font-family: "Trebuchet MS",sans-serif; font-size: 12.0pt; mso-bidi-font-family: "Trebuchet MS"; mso-bidi-font-size: 11.0pt; mso-fareast-font-family: "Trebuchet MS";">3.<span style="font-family: "Times New Roman"; font-size: 7pt; font-stretch: normal; font-variant-numeric: normal; line-height: normal;">
</span></span><!--[endif]--><b><span style="font-family: "Trebuchet MS",sans-serif; font-size: 12.0pt; mso-bidi-font-size: 11.0pt;">To
Filter:</span></b><span style="font-family: "Trebuchet MS",sans-serif; font-size: 12.0pt; mso-bidi-font-size: 11.0pt;"> Click items in any order<o:p></o:p></span></div>
<div class="MsoNoSpacing" style="margin-left: .5in; mso-list: l0 level1 lfo1; text-indent: -.25in;">
<!--[if !supportLists]--><span style="font-family: "Trebuchet MS",sans-serif; font-size: 12.0pt; mso-bidi-font-family: "Trebuchet MS"; mso-bidi-font-size: 11.0pt; mso-fareast-font-family: "Trebuchet MS";">4.<span style="font-family: "Times New Roman"; font-size: 7pt; font-stretch: normal; font-variant-numeric: normal; line-height: normal;">
</span></span><!--[endif]--><b><span style="font-family: "Trebuchet MS",sans-serif; font-size: 12.0pt; mso-bidi-font-size: 11.0pt;">To
Clear a Filter</span></b><span style="font-family: "Trebuchet MS",sans-serif; font-size: 12.0pt; mso-bidi-font-size: 11.0pt;">, click the filter graphic<o:p></o:p></span></div>
<div class="MsoNoSpacing">
<br /></div>
<div class="MsoNoSpacing">
<b><span style="font-family: "Trebuchet MS",sans-serif; font-size: 12.0pt; mso-bidi-font-size: 11.0pt;">Multiple
Pivot Tables<o:p></o:p></span></b></div>
<div class="MsoNoSpacing">
<span style="font-family: "Trebuchet MS",sans-serif; font-size: 12.0pt; mso-bidi-font-size: 11.0pt;">To filter <b>multiple tables</b> with a single slicer, select the slicer and then
choose <b><i>Slicer Tools, Options, Report Connections.</i></b> To delete a slicer,
simply select it and press <b><i>Delete</i></b>.</span></div>
<div class="MsoNoSpacing">
<b><span style="font-family: "Trebuchet MS",sans-serif; font-size: 12.0pt; mso-bidi-font-size: 11.0pt;"><br /></span></b></div>
<div class="MsoNoSpacing">
<b><span style="font-family: "Trebuchet MS",sans-serif; font-size: 12.0pt; mso-bidi-font-size: 11.0pt;">Slicers.
</span></b><span style="font-family: "Trebuchet MS", sans-serif; font-size: 12pt;">Another great <b><i>cooking tool </i></b>for your Excel kitchen
set!</span></div>
Bob DeLaMartrehttp://www.blogger.com/profile/03761089085420930697noreply@blogger.com0tag:blogger.com,1999:blog-3794737577784583819.post-5707124634434815472017-09-05T12:16:00.003-07:002017-09-05T12:16:53.797-07:00Pivot Tables – A Second Look…<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiOi1zBeuuSvdQ-XVKE7F_kzInjTGn6xhP1JM3G0-Xp7eu2DCHHasWReeExydfP1M5-4sKNgXl5qx1oUovRttjsfwtU8fAwftkE9A45-96bmJSsgX0RsHmIOuYUVDLuPSiGK9yqBbmYARyT/s1600/WeatherVane.png" imageanchor="1" style="clear: left; float: left; margin-bottom: 1em; margin-right: 1em;"><img border="0" data-original-height="501" data-original-width="189" height="200" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiOi1zBeuuSvdQ-XVKE7F_kzInjTGn6xhP1JM3G0-Xp7eu2DCHHasWReeExydfP1M5-4sKNgXl5qx1oUovRttjsfwtU8fAwftkE9A45-96bmJSsgX0RsHmIOuYUVDLuPSiGK9yqBbmYARyT/s200/WeatherVane.png" width="75" /></a></div>
<div class="MsoNoSpacing">
<span style="font-family: "Trebuchet MS",sans-serif; font-size: 12.0pt; mso-bidi-font-size: 11.0pt;">Last week we looked at a general
overview of <b>Pivot Tables</b>. This week
we will continue our examination of this powerful tool, by exploring a few
rudimentary <b><i>How-Tos</i></b>:<o:p></o:p></span></div>
<div class="MsoNoSpacing">
<br /></div>
<div class="MsoNoSpacing">
<b><span style="font-family: "Trebuchet MS",sans-serif; font-size: 12.0pt; mso-bidi-font-size: 11.0pt;">Show
or Hide the Pivot Table Fields Pane<o:p></o:p></span></b></div>
<div class="MsoNoSpacing">
<span style="font-family: "Trebuchet MS",sans-serif; font-size: 12.0pt; mso-bidi-font-size: 11.0pt;">To hide or show the <b>Fields Pane</b>, select any pivot table cell, and choose <b><i>PivotTable
Tools</i></b><i>, <b>Analyze, Show: Field List</b></i>. Simple as that…<o:p></o:p></span></div>
<div class="MsoNoSpacing">
<br /></div>
<div class="MsoNoSpacing">
<b><span style="font-family: "Trebuchet MS",sans-serif; font-size: 12.0pt; mso-bidi-font-size: 11.0pt;">Refreshing
a Pivot Table<o:p></o:p></span></b></div>
<div class="MsoNoSpacing">
<span style="font-family: "Trebuchet MS",sans-serif; font-size: 12.0pt; mso-bidi-font-size: 11.0pt;">Select any cell within the table and
choose:<i> <b>PivotTable Tools, Analyze, Data: Refresh</b>. </i>Bamm, refreshed pivot
table!<o:p></o:p></span></div>
<div class="MsoNoSpacing">
<br /></div>
<div class="MsoNoSpacing">
<b><span style="font-family: "Trebuchet MS",sans-serif; font-size: 12.0pt; mso-bidi-font-size: 11.0pt;">Add
a Pivot Chart<o:p></o:p></span></b></div>
<div class="MsoNoSpacing">
<span style="font-family: "Trebuchet MS",sans-serif; font-size: 12.0pt; mso-bidi-font-size: 11.0pt;">Click any cell within an existing Pivot
Table, and choose <b><i>PivotTable Tools, Analyze, Tools: PivotChart</i></b>. Then click <b><i>OK</i></b>
after choosing the <b><i>Chart Type</i> </b>and <b><i>Subtype</i></b><i>.</i> Finally, it is a good practice to move the chart to another
worksheet by clicking in the chart and choosing <b><i>PivotTable Tools, Design, Move
Chart: New Sheet [OK</i>]</b>. Piece of cake!<o:p></o:p></span></div>
<div class="MsoNoSpacing">
<br /></div>
<div class="MsoNoSpacing">
<span style="font-family: "Trebuchet MS", sans-serif; font-size: 12pt;">Knowing some simple techniques for
manipulating </span><b style="font-family: "Trebuchet MS", sans-serif; font-size: 12pt;">Pivot Tables </b><span style="font-family: "Trebuchet MS", sans-serif; font-size: 12pt;">and </span><b style="font-family: "Trebuchet MS", sans-serif; font-size: 12pt;">Charts</b><span style="font-family: "Trebuchet MS", sans-serif; font-size: 12pt;"> can take this vital tool from
being a bit </span><b style="font-family: "Trebuchet MS", sans-serif; font-size: 12pt;"><i>daunting</i></b><span style="font-family: "Trebuchet MS", sans-serif; font-size: 12pt;"> to being a </span><b style="font-family: "Trebuchet MS", sans-serif; font-size: 12pt;"><i>stress-free</i></b><span style="font-family: "Trebuchet MS", sans-serif; font-size: 12pt;"> tool!</span><span style="font-family: "Trebuchet MS", sans-serif; font-size: 12pt;"> </span></div>
Bob DeLaMartrehttp://www.blogger.com/profile/03761089085420930697noreply@blogger.com0tag:blogger.com,1999:blog-3794737577784583819.post-91045372340088865682017-08-29T10:33:00.002-07:002017-08-29T10:33:51.945-07:00Pivot Tables – A First Look<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjud4Z82N94wAvCONEjQMyPz2AECtl3Eg8D07isy-UL-Gc8cpm0Ckcfn0BT6mRRvQDm8J8LKrfRC8g55vWes7o7t1DSOfZDNXE0zDmjDdNlppIz77mxX3iPkjkWOaPfE6FE5KXNgmacf7Vz/s1600/PivotMonitor.png" imageanchor="1" style="clear: left; float: left; margin-bottom: 1em; margin-right: 1em;"><img border="0" data-original-height="665" data-original-width="188" height="200" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjud4Z82N94wAvCONEjQMyPz2AECtl3Eg8D07isy-UL-Gc8cpm0Ckcfn0BT6mRRvQDm8J8LKrfRC8g55vWes7o7t1DSOfZDNXE0zDmjDdNlppIz77mxX3iPkjkWOaPfE6FE5KXNgmacf7Vz/s200/PivotMonitor.png" width="56" /></a></div>
<div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in;">
<span style="font-family: "Trebuchet MS",sans-serif; font-size: 12.0pt; mso-bidi-font-family: "Times New Roman"; mso-fareast-font-family: "Times New Roman";">This
week, we will begin a <b><i>brief series</i></b> of posts regarding <b>Pivot Tables</b>. As I mentioned in my blog
post seven years ago, much has been written on <b><i>Pivot Tables</i></b>, and
much has also been misunderstood about this <b><i>highly practical</i></b>, but not
perfect tool.<o:p></o:p></span></div>
<div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in;">
<br /></div>
<br />
<div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in;">
<b><span style="font-family: "Trebuchet MS",sans-serif; font-size: 12.0pt; mso-bidi-font-family: "Times New Roman"; mso-fareast-font-family: "Times New Roman";">Good
Things about Pivot Tables</span></b><span style="color: #002060; font-family: "Trebuchet MS",sans-serif; font-size: 12.0pt; mso-bidi-font-family: "Times New Roman"; mso-fareast-font-family: "Times New Roman";"><br />
</span><span style="font-family: "Trebuchet MS",sans-serif; font-size: 12.0pt; mso-bidi-font-family: "Times New Roman"; mso-fareast-font-family: "Times New Roman";">Every
analyst or manager should have at least moderate skills at using pivot tables.
You can use pivot tables to summarize, analyze, and explore what-ifs in your
data. What is particularly beneficial about <b><i>Pivot Tables</i></b> is they are
very powerful, lightning fast, and <b><i>easy to use</i></b>. As if that wasn’t
enough, if you change your mind on your layout, it takes only <b><i>seconds
to modify </i></b>the view of your data/information. <span style="color: #002060;"><br />
<br />
</span><b>A Few Quirks…</b><br />
Although some analysts live and breathe Pivot tables, they are not all things
for all situations. Though powerful, they have some <b><i><span style="background: white;">odd quirks</span></i></b>, such as resizing your columns
when you change an entry and, (although this is easy do so…), often need to be
rebuilt if your data significantly changes.<span style="color: #002060;"><br />
<br />
</span><b>A Word regarding Aesthetics</b><br />
Let’s face it, pivot tables are not going to win any beauty contests! Whereas,
you can apply one of the <b><i>stock formatting schemes</i></b> that haven’t
changed in many years, they are still rather, well, <b><i>homely</i></b>. This, of course,
may not be or key importance to you if you are just doing some “<b><i>quick and
dirty</i></b>” analysis, but it may not be something you want to show the board
of directors.<span style="color: #002060;"><br />
<br />
</span><b>Some Final Thoughts </b><br />
Though not an ideal tool for every circumstance, pivot tables can often save
you many <b><i>hours of analysis time</i></b>, and they truly are easy to use. If you
have never experimented with Pivot Tables, give them a try. I can guarantee
that you will amaze yourself with how simple it is to manipulate your data.<span style="color: #002060;"><o:p></o:p></span></span></div>
Bob DeLaMartrehttp://www.blogger.com/profile/03761089085420930697noreply@blogger.com0tag:blogger.com,1999:blog-3794737577784583819.post-28554473634647023382017-08-22T09:28:00.001-07:002017-08-22T09:28:45.834-07:00Automating Macros<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEg74xDsBQpt_q__khOL0iYrKK1KfW1m8mB-VUczFc4Bvl693gBQY9O9IPsTuoB-SYP4cK1isXVKsyVJz8SNHX0zxKBYXaMLRZqP1GwggSpPJkSXX9NIdr01bD6pkob0RIqNbX8jE5yZBUaq/s1600/Automation.png" imageanchor="1" style="clear: left; float: left; margin-bottom: 1em; margin-right: 1em;"><img border="0" data-original-height="560" data-original-width="143" height="200" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEg74xDsBQpt_q__khOL0iYrKK1KfW1m8mB-VUczFc4Bvl693gBQY9O9IPsTuoB-SYP4cK1isXVKsyVJz8SNHX0zxKBYXaMLRZqP1GwggSpPJkSXX9NIdr01bD6pkob0RIqNbX8jE5yZBUaq/s200/Automation.png" width="50" /></a></div>
<div class="MsoNoSpacing">
<span style="font-family: "Trebuchet MS",sans-serif; font-size: 12.0pt; mso-bidi-font-size: 11.0pt;">Continuing with the subject of using
macros in your Excel workbooks, we are going to look at <b><i>Executing Macros Automatically</i></b>
this week.<o:p></o:p></span></div>
<div class="MsoNoSpacing">
<br /></div>
<div class="MsoNoSpacing">
<span style="font-family: "Trebuchet MS",sans-serif; font-size: 12.0pt; mso-bidi-font-size: 11.0pt;">There are times when you may wish to
have a recorded macro perform automatically whenever your workbook is opened
(or closed). Let’s say, for instance, that you have a <b><i>Customer Service Report</i></b>
workbook that you routinely access to update how the department is progressing.
If you know that you are going to want a particular macro to run each time you open
the workbook, you can <b><i>automate the process</i></b> with a very
simple piece of VBA code.<o:p></o:p></span></div>
<div class="MsoNoSpacing">
<br /></div>
<div class="MsoNoSpacing">
<span style="font-family: "Trebuchet MS",sans-serif; font-size: 12.0pt; mso-bidi-font-size: 11.0pt;">All that is needed to have your workbook
update whenever it is opened is to <b><i>name </i></b>your macro, <b>Auto_Open</b> (or <b>Auto_Close</b> if you want it to run when the workbook is closed…). It
really is as simple as that!<o:p></o:p></span></div>
<div class="MsoNoSpacing">
<br /></div>
<div class="MsoNoSpacing">
<span style="font-family: "Trebuchet MS",sans-serif; font-size: 12.0pt; mso-bidi-font-size: 11.0pt;">But, what if occasionally you do <b><i>not</i></b>
want to have the macro execute? Merely hold down <b><shift></shift></b> when opening or closing your workbook to suppress the
operation.</span></div>
<div class="MsoNoSpacing">
<b><i><span style="font-family: "Trebuchet MS",sans-serif; font-size: 12.0pt; mso-bidi-font-size: 11.0pt;"><br /></span></i></b></div>
<div class="MsoNoSpacing">
<b><i><span style="font-family: "Trebuchet MS",sans-serif; font-size: 12.0pt; mso-bidi-font-size: 11.0pt;">Automating Macros:</span></i></b><span style="font-family: "Trebuchet MS", sans-serif; font-size: 12pt;">
Easily accomplished with a simple renaming of your macro.</span></div>
Bob DeLaMartrehttp://www.blogger.com/profile/03761089085420930697noreply@blogger.com0tag:blogger.com,1999:blog-3794737577784583819.post-81798537588557934972017-08-16T09:01:00.000-07:002017-08-16T09:01:12.185-07:00Recording Macros<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgS3FjnKoYbYfK56RNg52ruUnT0AbDkwpJi5xzgVfKsikLG-KUyTbRAcdKLvVmQzUj39QFxM-d-kFdpfIJlnErYzFSTnPX53FUoR1L6mI-CI8LUf55UqugVFHRToE_wEssa9pCOiZfhqisb/s1600/Recording.png" imageanchor="1" style="clear: left; float: left; margin-bottom: 1em; margin-right: 1em;"><img border="0" data-original-height="565" data-original-width="163" height="200" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgS3FjnKoYbYfK56RNg52ruUnT0AbDkwpJi5xzgVfKsikLG-KUyTbRAcdKLvVmQzUj39QFxM-d-kFdpfIJlnErYzFSTnPX53FUoR1L6mI-CI8LUf55UqugVFHRToE_wEssa9pCOiZfhqisb/s200/Recording.png" width="57" /></a></div>
<div class="MsoNoSpacing">
<span style="font-family: "Trebuchet MS",sans-serif; font-size: 12.0pt; mso-bidi-font-family: "Times New Roman"; mso-fareast-font-family: "Times New Roman";">Ah,
Macros… The wonderfully mysterious tools of glorious geekdom! As is the case
with many otherwise meaningful and useful words in business, “<b><i>Macros</i></b>”
has for some become a <b><i>Buzzword</i></b> (e.g. “Well, can’t you just
build a <b><i>macro</i></b> to do that?” or “I hear <b><i>macros</i></b> can be very useful
in spreadsheets. We probably should use those...”)<o:p></o:p></span></div>
<div class="MsoNoSpacing">
<br /></div>
<div class="MsoNoSpacing">
<span style="font-family: "Trebuchet MS",sans-serif; font-size: 12.0pt; mso-bidi-font-family: "Times New Roman"; mso-fareast-font-family: "Times New Roman";">Everyday
users and clueless managers occasionally seem to think that using a <b><i>Macro</i></b>
is the <b><i>Silver Bullet</i></b> for solving their Excel issues. In fact, macros can be extremely beneficial,
but they are more typically created and used by employing the special skillset of
VBA programmers. On many levels this is true.<o:p></o:p></span></div>
<div class="MsoNoSpacing">
<br /></div>
<div class="MsoNoSpacing">
<span style="font-family: "Trebuchet MS",sans-serif; font-size: 12.0pt; mso-bidi-font-family: "Times New Roman"; mso-fareast-font-family: "Times New Roman";">When
it comes to using <b><i>Recorded Macros</i></b>, however, the entry level is much more
accessible. A <b><i>Recorded Macro</i></b> can,
in fact, be an ideal way of lessening your repetitious work and simplifying
your routine chores.<o:p></o:p></span></div>
<div class="MsoNoSpacing">
<br /></div>
<div class="MsoNoSpacing">
<span style="font-family: "Trebuchet MS",sans-serif; font-size: 12.0pt; mso-bidi-font-size: 11.0pt;">Rather than <b><i>recreating the wheel</i></b> each
time you do a report, a recorded macro can store your mouse clicks and
keystrokes while you work, and then let you to play them back in future
revisions of your workbook. You can <b><i>save your recordings</i></b>, and when you
run the macro, it will play the commands in the same order that you recorded them.
It can be an amazing timesaver (and do away with repetitious drudgery…). <br />
<br />
For example, let’s say you track the performance of the <b><i>Customer Service Reps</i></b> in
your company. This may be a repetitive weekly task that can be easily
automated. Here's how to <b><i>Record a Macro</i></b> for this type of
situation: <br />
<br />
<b>1.</b> Access your report workbook and click the start of the cells
you are going to update. <br />
<b>2.</b> Point to the <b>Developer</b>
tab, and then click <b>Record Macro</b>. <br />
<b>3</b>. In the <b>Record Macro</b> dialog box, enter a <b><i>Name</i></b>
that applies to your operation. For ease of operation later on, assign a custom
<b>Shortcut Key </b>(this will be your<b><i> Magic Button </i></b>to replay the macro…).
<br />
<b>4.</b> Now perform the calculations, formatting, moving, etc that
applies to the repetitive and monotonous update. <br />
<b>5.</b> Finish recording the macro by clicking the <b><i>Stop Recording</i></b> button. <br />
<br />
To <b><i>Run your Macro</i></b>, simply press your <b><i>Custom Shortcut Keys</i></b>
or click on the <b><i>Replay Button </i></b>that you may have created.<br />
<br />
<b><i>Presto! Instant update!</i></b> Your newly created <b><i>Macro</i></b>
has just done all the work that may have taken you a considerable amount of
time to complete. Try this out – It may even inspire you to learn more about
the VBA programming that you can use to conquer the business world…<o:p></o:p></span></div>
Bob DeLaMartrehttp://www.blogger.com/profile/03761089085420930697noreply@blogger.com0