tag:blogger.com,1999:blog-9013961995062642762024-03-12T18:04:12.710-07:00Microsoft Office Excel TTK (Tips, Tricks & Keys)Unknownnoreply@blogger.comBlogger10125tag:blogger.com,1999:blog-901396199506264276.post-50064745212964313242013-09-08T05:15:00.000-07:002013-09-08T05:15:00.177-07:00Camera Tool in Excel<div dir="ltr" style="text-align: left;" trbidi="on">
<div class="MsoNormal">
<b><span style="font-family: Constantia, serif; font-size: 10pt;">Camera tool is your way of creating visual reference in an excel
sheet.</span></b><span style="font-family: Constantia, serif; font-size: 10pt;"> It is one of the useful and hidden features of excel. Here
is how it works. You specify a rectangular area in your workbook and camera
tool creates a mirror image of that area as a drawing object. You can move it
or resize it. And whenever the contents of original rectangular area changes
(charts, drawings or cell values) the mirror image changes too.<o:p></o:p></span></div>
<div class="MsoNormal">
<b><span style="font-family: Constantia, serif; font-size: 10pt;">How to add camera tool to
standard toolbar?<o:p></o:p></span></b></div>
<div class="MsoNormal">
<span style="font-family: Constantia, serif; font-size: 10pt;">In order to use camera tool, you must add the tool to a tool bar
in excel menu area. Here is how you can do that:<o:p></o:p></span></div>
<ol start="1" type="1">
<li class="MsoNormal"><span style="font-family: "Constantia","serif"; font-size: 10.0pt; mso-bidi-font-family: "Times New Roman"; mso-fareast-font-family: "Times New Roman";">Go to menu >
tools > customize<o:p></o:p></span></li>
<li class="MsoNormal"><span style="font-family: "Constantia","serif"; font-size: 10.0pt; mso-bidi-font-family: "Times New Roman"; mso-fareast-font-family: "Times New Roman";">In the dialog go
to “Commands” tab and select “tools” in categories.<o:p></o:p></span></li>
<li class="MsoNormal"><span style="font-family: "Constantia","serif"; font-size: 10.0pt; mso-bidi-font-family: "Times New Roman"; mso-fareast-font-family: "Times New Roman";">Scroll down in
the commands area until you see a little camera tool<o:p></o:p></span></li>
<li class="MsoNormal"><span style="font-family: "Constantia","serif"; font-size: 10.0pt; mso-bidi-font-family: "Times New Roman"; mso-fareast-font-family: "Times New Roman";">Now drag and
drop this in your tool bar as shown below<o:p></o:p></span></li>
</ol>
<div class="MsoNormal" style="margin-left: 0.5in;">
<span style="font-family: Constantia, serif; font-size: 10pt;"><!--[if gte vml 1]><v:shapetype
id="_x0000_t75" coordsize="21600,21600" o:spt="75" o:preferrelative="t"
path="m@4@5l@4@11@9@11@9@5xe" filled="f" stroked="f">
<v:stroke joinstyle="miter"/>
<v:formulas>
<v:f eqn="if lineDrawn pixelLineWidth 0"/>
<v:f eqn="sum @0 1 0"/>
<v:f eqn="sum 0 0 @1"/>
<v:f eqn="prod @2 1 2"/>
<v:f eqn="prod @3 21600 pixelWidth"/>
<v:f eqn="prod @3 21600 pixelHeight"/>
<v:f eqn="sum @0 0 1"/>
<v:f eqn="prod @6 1 2"/>
<v:f eqn="prod @7 21600 pixelWidth"/>
<v:f eqn="sum @8 21600 0"/>
<v:f eqn="prod @7 21600 pixelHeight"/>
<v:f eqn="sum @10 21600 0"/>
</v:formulas>
<v:path o:extrusionok="f" gradientshapeok="t" o:connecttype="rect"/>
<o:lock v:ext="edit" aspectratio="t"/>
</v:shapetype><v:shape id="Picture_x0020_1" o:spid="_x0000_i1025" type="#_x0000_t75"
alt="Adding Camera tool to Excel Toolbar" style='width:445.5pt;height:219pt;
visibility:visible;mso-wrap-style:square'>
<v:imagedata src="file:///C:\Users\kkhari\AppData\Local\Temp\msohtmlclip1\01\clip_image001.png"
o:title="Adding Camera tool to Excel Toolbar"/>
</v:shape><![endif]--><!--[if !vml]--><img alt="Adding Camera tool to Excel Toolbar" height="292" src="file:///C:\Users\kkhari\AppData\Local\Temp\msohtmlclip1\01\clip_image001.png" title="Adding Camera tool to Excel Toolbar" v:shapes="Picture_x0020_1" width="594" /><!--[endif]--></span><span style="font-family: Constantia, serif; font-size: 10pt;"><o:p></o:p></span></div>
<div class="MsoNormal">
<b><span style="font-family: Constantia, serif; font-size: 10pt;"><br /></span></b></div>
<div class="MsoNormal">
<b><span style="font-family: Constantia, serif; font-size: 10pt;">How to use excel camera
tool?<o:p></o:p></span></b></div>
<div class="MsoNormal">
<span style="font-family: Constantia, serif; font-size: 10pt;"><br />
<b>We will use camera tool to create a micro-chart in excel.</b><o:p></o:p></span></div>
<ol start="1" type="1">
<li class="MsoNormal"><span style="font-family: "Constantia","serif"; font-size: 10.0pt; mso-bidi-font-family: "Times New Roman"; mso-fareast-font-family: "Times New Roman";">First make a
normal chart.<o:p></o:p></span></li>
<li class="MsoNormal"><span style="font-family: "Constantia","serif"; font-size: 10.0pt; mso-bidi-font-family: "Times New Roman"; mso-fareast-font-family: "Times New Roman";">Now select the
cells surrounding the chart<o:p></o:p></span></li>
<li class="MsoNormal"><span style="font-family: "Constantia","serif"; font-size: 10.0pt; mso-bidi-font-family: "Times New Roman"; mso-fareast-font-family: "Times New Roman";">Click on camera
tool<o:p></o:p></span></li>
<li class="MsoNormal"><span style="font-family: "Constantia","serif"; font-size: 10.0pt; mso-bidi-font-family: "Times New Roman"; mso-fareast-font-family: "Times New Roman";">Now click any
where in the worksheet and excel places a snapshot of the range you have
selected<o:p></o:p></span></li>
<li class="MsoNormal"><span style="font-family: "Constantia","serif"; font-size: 10.0pt; mso-bidi-font-family: "Times New Roman"; mso-fareast-font-family: "Times New Roman";">Resize it until
you get the microchart effect.<o:p></o:p></span></li>
<li class="MsoNormal"><span style="font-family: "Constantia","serif"; font-size: 10.0pt; mso-bidi-font-family: "Times New Roman"; mso-fareast-font-family: "Times New Roman";">Bingo !<o:p></o:p></span></li>
<li class="MsoNormal"><span style="font-family: "Constantia","serif"; font-size: 10.0pt; mso-bidi-font-family: "Times New Roman"; mso-fareast-font-family: "Times New Roman";">Btw, excel adds
a border to the camera tool output. You can remove it by using drawing
tool bar<o:p></o:p></span></li>
</ol>
<br />
<div class="MsoNormal">
<br /></div>
</div>
Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-901396199506264276.post-70244888523555150312013-09-07T04:58:00.000-07:002013-09-07T04:58:00.635-07:00Excel Formulae to Solve the Situational Needs<div dir="ltr" style="text-align: left;" trbidi="on">
<span style="background-color: white; color: #333333; font-family: Verdana, Arial, Helvetica, sans-serif; font-size: 12px; line-height: 22px;">26. </span><strong style="background-color: white; border: none; color: #333333; font-family: Verdana, Arial, Helvetica, sans-serif; font-size: 12px; line-height: 22px; margin: 0px; padding: 0px;">To get the first name of a person</strong><span style="background-color: white; color: #333333; font-family: Verdana, Arial, Helvetica, sans-serif; font-size: 12px; line-height: 22px;">, use =left(name,find(” “,name)-1)</span><br style="background-color: white; border: none; color: #333333; font-family: Verdana, Arial, Helvetica, sans-serif; font-size: 12px; line-height: 22px; margin: 0px; padding: 0px;" /><span style="background-color: white; color: #333333; font-family: Verdana, Arial, Helvetica, sans-serif; font-size: 12px; line-height: 22px;">27. </span><strong style="background-color: white; border: none; color: #333333; font-family: Verdana, Arial, Helvetica, sans-serif; font-size: 12px; line-height: 22px; margin: 0px; padding: 0px;">To calculate mortgage payments</strong><span style="background-color: white; color: #333333; font-family: Verdana, Arial, Helvetica, sans-serif; font-size: 12px; line-height: 22px;">, use =PMT(interest-rate,number-of-payments,how-much-loan)</span><br style="background-color: white; border: none; color: #333333; font-family: Verdana, Arial, Helvetica, sans-serif; font-size: 12px; line-height: 22px; margin: 0px; padding: 0px;" /><span style="background-color: white; color: #333333; font-family: Verdana, Arial, Helvetica, sans-serif; font-size: 12px; line-height: 22px;">28. </span><strong style="background-color: white; border: none; color: #333333; font-family: Verdana, Arial, Helvetica, sans-serif; font-size: 12px; line-height: 22px; margin: 0px; padding: 0px;">To get nth largest number in a range</strong><span style="background-color: white; color: #333333; font-family: Verdana, Arial, Helvetica, sans-serif; font-size: 12px; line-height: 22px;">, use =large(range,n)</span><br style="background-color: white; border: none; color: #333333; font-family: Verdana, Arial, Helvetica, sans-serif; font-size: 12px; line-height: 22px; margin: 0px; padding: 0px;" /><span style="background-color: white; color: #333333; font-family: Verdana, Arial, Helvetica, sans-serif; font-size: 12px; line-height: 22px;">29. </span><strong style="background-color: white; border: none; color: #333333; font-family: Verdana, Arial, Helvetica, sans-serif; font-size: 12px; line-height: 22px; margin: 0px; padding: 0px;">To get nth smallest number in a range</strong><span style="background-color: white; color: #333333; font-family: Verdana, Arial, Helvetica, sans-serif; font-size: 12px; line-height: 22px;">, use = small(range,n)</span><br style="background-color: white; border: none; color: #333333; font-family: Verdana, Arial, Helvetica, sans-serif; font-size: 12px; line-height: 22px; margin: 0px; padding: 0px;" /><span style="background-color: white; color: #333333; font-family: Verdana, Arial, Helvetica, sans-serif; font-size: 12px; line-height: 22px;">30. </span><strong style="background-color: white; border: none; color: #333333; font-family: Verdana, Arial, Helvetica, sans-serif; font-size: 12px; line-height: 22px; margin: 0px; padding: 0px;">To generate a random phone number</strong><span style="background-color: white; color: #333333; font-family: Verdana, Arial, Helvetica, sans-serif; font-size: 12px; line-height: 22px;">, use =randbetween(1000000000,9999999999), needs analysis toolpak if you are using excel 2003 or earlier… </span><br style="background-color: white; border: none; color: #333333; font-family: Verdana, Arial, Helvetica, sans-serif; font-size: 12px; line-height: 22px; margin: 0px; padding: 0px;" /><span style="background-color: white; color: #333333; font-family: Verdana, Arial, Helvetica, sans-serif; font-size: 12px; line-height: 22px;">31. </span><strong style="background-color: white; border: none; color: #333333; font-family: Verdana, Arial, Helvetica, sans-serif; font-size: 12px; line-height: 22px; margin: 0px; padding: 0px;">To count number of words in a cell</strong><span style="background-color: white; color: #333333; font-family: Verdana, Arial, Helvetica, sans-serif; font-size: 12px; line-height: 22px;">, use =len(trim(text))-len(SUBSTITUTE(trim(text),” “,”"))</span><br style="background-color: white; border: none; color: #333333; font-family: Verdana, Arial, Helvetica, sans-serif; font-size: 12px; line-height: 22px; margin: 0px; padding: 0px;" /><span style="background-color: white; color: #333333; font-family: Verdana, Arial, Helvetica, sans-serif; font-size: 12px; line-height: 22px;">32. </span><strong style="background-color: white; border: none; color: #333333; font-family: Verdana, Arial, Helvetica, sans-serif; font-size: 12px; line-height: 22px; margin: 0px; padding: 0px;">To count positive values in a range</strong><span style="background-color: white; color: #333333; font-family: Verdana, Arial, Helvetica, sans-serif; font-size: 12px; line-height: 22px;">, use =countif(range,”>0″)</span><br style="background-color: white; border: none; color: #333333; font-family: Verdana, Arial, Helvetica, sans-serif; font-size: 12px; line-height: 22px; margin: 0px; padding: 0px;" /><span style="background-color: white; color: #333333; font-family: Verdana, Arial, Helvetica, sans-serif; font-size: 12px; line-height: 22px;">33. </span><strong style="background-color: white; border: none; color: #333333; font-family: Verdana, Arial, Helvetica, sans-serif; font-size: 12px; line-height: 22px; margin: 0px; padding: 0px;">To calculate weighted average</strong><span style="background-color: white; color: #333333; font-family: Verdana, Arial, Helvetica, sans-serif; font-size: 12px; line-height: 22px;">, use SUMPRODUCT() function</span><br style="background-color: white; border: none; color: #333333; font-family: Verdana, Arial, Helvetica, sans-serif; font-size: 12px; line-height: 22px; margin: 0px; padding: 0px;" /><span style="background-color: white; color: #333333; font-family: Verdana, Arial, Helvetica, sans-serif; font-size: 12px; line-height: 22px;">34. </span><strong style="background-color: white; border: none; color: #333333; font-family: Verdana, Arial, Helvetica, sans-serif; font-size: 12px; line-height: 22px; margin: 0px; padding: 0px;">To remove unnecessary spaces</strong><span style="background-color: white; color: #333333; font-family: Verdana, Arial, Helvetica, sans-serif; font-size: 12px; line-height: 22px;">, use =trim(text)</span><br style="background-color: white; border: none; color: #333333; font-family: Verdana, Arial, Helvetica, sans-serif; font-size: 12px; line-height: 22px; margin: 0px; padding: 0px;" /><span style="background-color: white; color: #333333; font-family: Verdana, Arial, Helvetica, sans-serif; font-size: 12px; line-height: 22px;">35. </span><strong style="background-color: white; border: none; color: #333333; font-family: Verdana, Arial, Helvetica, sans-serif; font-size: 12px; line-height: 22px; margin: 0px; padding: 0px;">To format a number as SSN using formulas</strong><span style="background-color: white; color: #333333; font-family: Verdana, Arial, Helvetica, sans-serif; font-size: 12px; line-height: 22px;">, use =text(ssn-text,”000-00-0000″)</span><br style="background-color: white; border: none; color: #333333; font-family: Verdana, Arial, Helvetica, sans-serif; font-size: 12px; line-height: 22px; margin: 0px; padding: 0px;" /><span style="background-color: white; color: #333333; font-family: Verdana, Arial, Helvetica, sans-serif; font-size: 12px; line-height: 22px;">36. </span><strong style="background-color: white; border: none; color: #333333; font-family: Verdana, Arial, Helvetica, sans-serif; font-size: 12px; line-height: 22px; margin: 0px; padding: 0px;">To find age of a person based on DOB</strong><span style="background-color: white; color: #333333; font-family: Verdana, Arial, Helvetica, sans-serif; font-size: 12px; line-height: 22px;">, use =TEXT((NOW()-birth_date)&”",”yy “”years”" m “”months”" dd “”days”"”), output will be like 27 years 7 months 29 days</span><br style="background-color: white; border: none; color: #333333; font-family: Verdana, Arial, Helvetica, sans-serif; font-size: 12px; line-height: 22px; margin: 0px; padding: 0px;" /><span style="background-color: white; color: #333333; font-family: Verdana, Arial, Helvetica, sans-serif; font-size: 12px; line-height: 22px;">37. </span><strong style="background-color: white; border: none; color: #333333; font-family: Verdana, Arial, Helvetica, sans-serif; font-size: 12px; line-height: 22px; margin: 0px; padding: 0px;">To get name from initials from a name</strong><span style="background-color: white; color: #333333; font-family: Verdana, Arial, Helvetica, sans-serif; font-size: 12px; line-height: 22px;">, use IF(), FIND(), LEN() and SUBSTITUTE() formulas</span><br style="background-color: white; border: none; color: #333333; font-family: Verdana, Arial, Helvetica, sans-serif; font-size: 12px; line-height: 22px; margin: 0px; padding: 0px;" /><span style="background-color: white; color: #333333; font-family: Verdana, Arial, Helvetica, sans-serif; font-size: 12px; line-height: 22px;">38. </span><strong style="background-color: white; border: none; color: #333333; font-family: Verdana, Arial, Helvetica, sans-serif; font-size: 12px; line-height: 22px; margin: 0px; padding: 0px;">To get proper fraction from a number (for eg 1/3 from 6/18)</strong><span style="background-color: white; color: #333333; font-family: Verdana, Arial, Helvetica, sans-serif; font-size: 12px; line-height: 22px;">, use =text(fraction, “?/?”)</span><br style="background-color: white; border: none; color: #333333; font-family: Verdana, Arial, Helvetica, sans-serif; font-size: 12px; line-height: 22px; margin: 0px; padding: 0px;" /><span style="background-color: white; color: #333333; font-family: Verdana, Arial, Helvetica, sans-serif; font-size: 12px; line-height: 22px;">39. </span><strong style="background-color: white; border: none; color: #333333; font-family: Verdana, Arial, Helvetica, sans-serif; font-size: 12px; line-height: 22px; margin: 0px; padding: 0px;">To get partial matches in vlookup</strong><span style="background-color: white; color: #333333; font-family: Verdana, Arial, Helvetica, sans-serif; font-size: 12px; line-height: 22px;">, use * operator like this: =vlookup(“abc*”,lookup_range,return_column)</span><br style="background-color: white; border: none; color: #333333; font-family: Verdana, Arial, Helvetica, sans-serif; font-size: 12px; line-height: 22px; margin: 0px; padding: 0px;" /><span style="background-color: white; color: #333333; font-family: Verdana, Arial, Helvetica, sans-serif; font-size: 12px; line-height: 22px;">40. </span><strong style="background-color: white; border: none; color: #333333; font-family: Verdana, Arial, Helvetica, sans-serif; font-size: 12px; line-height: 22px; margin: 0px; padding: 0px;">To simulate averageif() in earlier versions of excel</strong><span style="background-color: white; color: #333333; font-family: Verdana, Arial, Helvetica, sans-serif; font-size: 12px; line-height: 22px;">, use =sumif(range, criteria)/countif(range, criteria)</span><br style="background-color: white; border: none; color: #333333; font-family: Verdana, Arial, Helvetica, sans-serif; font-size: 12px; line-height: 22px; margin: 0px; padding: 0px;" /><span style="background-color: white; color: #333333; font-family: Verdana, Arial, Helvetica, sans-serif; font-size: 12px; line-height: 22px;">41. </span><strong style="background-color: white; border: none; color: #333333; font-family: Verdana, Arial, Helvetica, sans-serif; font-size: 12px; line-height: 22px; margin: 0px; padding: 0px;">To debug your formulas</strong><span style="background-color: white; color: #333333; font-family: Verdana, Arial, Helvetica, sans-serif; font-size: 12px; line-height: 22px;">, select the portions of formula and press F9 to see the result of that portion</span><br style="background-color: white; border: none; color: #333333; font-family: Verdana, Arial, Helvetica, sans-serif; font-size: 12px; line-height: 22px; margin: 0px; padding: 0px;" /><span style="background-color: white; color: #333333; font-family: Verdana, Arial, Helvetica, sans-serif; font-size: 12px; line-height: 22px;">42. </span><strong style="background-color: white; border: none; color: #333333; font-family: Verdana, Arial, Helvetica, sans-serif; font-size: 12px; line-height: 22px; margin: 0px; padding: 0px;">To get the file extension from a file name</strong><span style="background-color: white; color: #333333; font-family: Verdana, Arial, Helvetica, sans-serif; font-size: 12px; line-height: 22px;">, use =right(filename,3) (doesn’t work for files that have weird extensions like .docx, .htaccess etc.)</span><br style="background-color: white; border: none; color: #333333; font-family: Verdana, Arial, Helvetica, sans-serif; font-size: 12px; line-height: 22px; margin: 0px; padding: 0px;" /><span style="background-color: white; color: #333333; font-family: Verdana, Arial, Helvetica, sans-serif; font-size: 12px; line-height: 22px;">43. </span><strong style="background-color: white; border: none; color: #333333; font-family: Verdana, Arial, Helvetica, sans-serif; font-size: 12px; line-height: 22px; margin: 0px; padding: 0px;">To quickly insert an in cell micro-chart</strong><span style="background-color: white; color: #333333; font-family: Verdana, Arial, Helvetica, sans-serif; font-size: 12px; line-height: 22px;">, use REPT() function</span><br style="background-color: white; border: none; color: #333333; font-family: Verdana, Arial, Helvetica, sans-serif; font-size: 12px; line-height: 22px; margin: 0px; padding: 0px;" /><span style="background-color: white; color: #333333; font-family: Verdana, Arial, Helvetica, sans-serif; font-size: 12px; line-height: 22px;">44. </span><strong style="background-color: white; border: none; color: #333333; font-family: Verdana, Arial, Helvetica, sans-serif; font-size: 12px; line-height: 22px; margin: 0px; padding: 0px;">COUNT() only counts number of cells with numbers in them</strong><span style="background-color: white; color: #333333; font-family: Verdana, Arial, Helvetica, sans-serif; font-size: 12px; line-height: 22px;">, if you want to count number of cells with anything in them, use COUNTA()</span><br style="background-color: white; border: none; color: #333333; font-family: Verdana, Arial, Helvetica, sans-serif; font-size: 12px; line-height: 22px; margin: 0px; padding: 0px;" /><span style="background-color: white; color: #333333; font-family: Verdana, Arial, Helvetica, sans-serif; font-size: 12px; line-height: 22px;">45. </span><strong style="background-color: white; border: none; color: #333333; font-family: Verdana, Arial, Helvetica, sans-serif; font-size: 12px; line-height: 22px; margin: 0px; padding: 0px;">Using named ranges in formulas saves you a lot of time. To define one</strong><span style="background-color: white; color: #333333; font-family: Verdana, Arial, Helvetica, sans-serif; font-size: 12px; line-height: 22px;">, just select some cells, and go to menu > insert > named ranges > define</span></div>
Unknownnoreply@blogger.com0Bangalore, Karnataka, India12.9715987 77.59456269999998312.4737417 76.946369199999978 13.4694557 78.242756199999988tag:blogger.com,1999:blog-901396199506264276.post-12293321504698304962013-09-06T04:57:00.001-07:002013-09-06T04:57:01.905-07:00Excel Useful Shortcut Keys<div dir="ltr" style="text-align: left;" trbidi="on">
<span style="background-color: white; color: #333333; font-family: Verdana, Arial, Helvetica, sans-serif; font-size: 12px; line-height: 22px;">1. </span><strong style="background-color: white; border: none; color: #333333; font-family: Verdana, Arial, Helvetica, sans-serif; font-size: 12px; line-height: 22px; margin: 0px; padding: 0px;">To format any selected object</strong><span style="background-color: white; color: #333333; font-family: Verdana, Arial, Helvetica, sans-serif; font-size: 12px; line-height: 22px;">, press ctrl+1</span><br style="background-color: white; border: none; color: #333333; font-family: Verdana, Arial, Helvetica, sans-serif; font-size: 12px; line-height: 22px; margin: 0px; padding: 0px;" /><span style="background-color: white; color: #333333; font-family: Verdana, Arial, Helvetica, sans-serif; font-size: 12px; line-height: 22px;">2. </span><strong style="background-color: white; border: none; color: #333333; font-family: Verdana, Arial, Helvetica, sans-serif; font-size: 12px; line-height: 22px; margin: 0px; padding: 0px;">To insert current date</strong><span style="background-color: white; color: #333333; font-family: Verdana, Arial, Helvetica, sans-serif; font-size: 12px; line-height: 22px;">, press ctrl+;</span><br style="background-color: white; border: none; color: #333333; font-family: Verdana, Arial, Helvetica, sans-serif; font-size: 12px; line-height: 22px; margin: 0px; padding: 0px;" /><span style="background-color: white; color: #333333; font-family: Verdana, Arial, Helvetica, sans-serif; font-size: 12px; line-height: 22px;">3. </span><strong style="background-color: white; border: none; color: #333333; font-family: Verdana, Arial, Helvetica, sans-serif; font-size: 12px; line-height: 22px; margin: 0px; padding: 0px;">To insert current time</strong><span style="background-color: white; color: #333333; font-family: Verdana, Arial, Helvetica, sans-serif; font-size: 12px; line-height: 22px;">, press ctrl+shift+;</span><br style="background-color: white; border: none; color: #333333; font-family: Verdana, Arial, Helvetica, sans-serif; font-size: 12px; line-height: 22px; margin: 0px; padding: 0px;" /><span style="background-color: white; color: #333333; font-family: Verdana, Arial, Helvetica, sans-serif; font-size: 12px; line-height: 22px;">4. </span><strong style="background-color: white; border: none; color: #333333; font-family: Verdana, Arial, Helvetica, sans-serif; font-size: 12px; line-height: 22px; margin: 0px; padding: 0px;">To repeat last action</strong><span style="background-color: white; color: #333333; font-family: Verdana, Arial, Helvetica, sans-serif; font-size: 12px; line-height: 22px;">, press F4</span><br style="background-color: white; border: none; color: #333333; font-family: Verdana, Arial, Helvetica, sans-serif; font-size: 12px; line-height: 22px; margin: 0px; padding: 0px;" /><span style="background-color: white; color: #333333; font-family: Verdana, Arial, Helvetica, sans-serif; font-size: 12px; line-height: 22px;">5. </span><strong style="background-color: white; border: none; color: #333333; font-family: Verdana, Arial, Helvetica, sans-serif; font-size: 12px; line-height: 22px; margin: 0px; padding: 0px;">To edit a cell comment</strong><span style="background-color: white; color: #333333; font-family: Verdana, Arial, Helvetica, sans-serif; font-size: 12px; line-height: 22px;">, press shift + F2</span><br style="background-color: white; border: none; color: #333333; font-family: Verdana, Arial, Helvetica, sans-serif; font-size: 12px; line-height: 22px; margin: 0px; padding: 0px;" /><span style="background-color: white; color: #333333; font-family: Verdana, Arial, Helvetica, sans-serif; font-size: 12px; line-height: 22px;">6. </span><strong style="background-color: white; border: none; color: #333333; font-family: Verdana, Arial, Helvetica, sans-serif; font-size: 12px; line-height: 22px; margin: 0px; padding: 0px;">To autosum selected cells</strong><span style="background-color: white; color: #333333; font-family: Verdana, Arial, Helvetica, sans-serif; font-size: 12px; line-height: 22px;">, press alt + =</span><br style="background-color: white; border: none; color: #333333; font-family: Verdana, Arial, Helvetica, sans-serif; font-size: 12px; line-height: 22px; margin: 0px; padding: 0px;" /><span style="background-color: white; color: #333333; font-family: Verdana, Arial, Helvetica, sans-serif; font-size: 12px; line-height: 22px;">7. </span><strong style="background-color: white; border: none; color: #333333; font-family: Verdana, Arial, Helvetica, sans-serif; font-size: 12px; line-height: 22px; margin: 0px; padding: 0px;">To see the suggest drop-down in a cell</strong><span style="background-color: white; color: #333333; font-family: Verdana, Arial, Helvetica, sans-serif; font-size: 12px; line-height: 22px;">, press alt + down arrow</span><br style="background-color: white; border: none; color: #333333; font-family: Verdana, Arial, Helvetica, sans-serif; font-size: 12px; line-height: 22px; margin: 0px; padding: 0px;" /><span style="background-color: white; color: #333333; font-family: Verdana, Arial, Helvetica, sans-serif; font-size: 12px; line-height: 22px;">8. </span><strong style="background-color: white; border: none; color: #333333; font-family: Verdana, Arial, Helvetica, sans-serif; font-size: 12px; line-height: 22px; margin: 0px; padding: 0px;">To enter multiple lines in a cell</strong><span style="background-color: white; color: #333333; font-family: Verdana, Arial, Helvetica, sans-serif; font-size: 12px; line-height: 22px;">, press alt+enter</span><br style="background-color: white; border: none; color: #333333; font-family: Verdana, Arial, Helvetica, sans-serif; font-size: 12px; line-height: 22px; margin: 0px; padding: 0px;" /><span style="background-color: white; color: #333333; font-family: Verdana, Arial, Helvetica, sans-serif; font-size: 12px; line-height: 22px;">9. </span><strong style="background-color: white; border: none; color: #333333; font-family: Verdana, Arial, Helvetica, sans-serif; font-size: 12px; line-height: 22px; margin: 0px; padding: 0px;">To insert a new sheet</strong><span style="background-color: white; color: #333333; font-family: Verdana, Arial, Helvetica, sans-serif; font-size: 12px; line-height: 22px;">, press shift + F11</span><br style="background-color: white; border: none; color: #333333; font-family: Verdana, Arial, Helvetica, sans-serif; font-size: 12px; line-height: 22px; margin: 0px; padding: 0px;" /><span style="background-color: white; color: #333333; font-family: Verdana, Arial, Helvetica, sans-serif; font-size: 12px; line-height: 22px;">10. </span><strong style="background-color: white; border: none; color: #333333; font-family: Verdana, Arial, Helvetica, sans-serif; font-size: 12px; line-height: 22px; margin: 0px; padding: 0px;">To edit active cell</strong><span style="background-color: white; color: #333333; font-family: Verdana, Arial, Helvetica, sans-serif; font-size: 12px; line-height: 22px;">, press F2 (places cursor in the end)</span><br style="background-color: white; border: none; color: #333333; font-family: Verdana, Arial, Helvetica, sans-serif; font-size: 12px; line-height: 22px; margin: 0px; padding: 0px;" /><span style="background-color: white; color: #333333; font-family: Verdana, Arial, Helvetica, sans-serif; font-size: 12px; line-height: 22px;">11. </span><strong style="background-color: white; border: none; color: #333333; font-family: Verdana, Arial, Helvetica, sans-serif; font-size: 12px; line-height: 22px; margin: 0px; padding: 0px;">To hide current row</strong><span style="background-color: white; color: #333333; font-family: Verdana, Arial, Helvetica, sans-serif; font-size: 12px; line-height: 22px;">, press ctrl+9</span><br style="background-color: white; border: none; color: #333333; font-family: Verdana, Arial, Helvetica, sans-serif; font-size: 12px; line-height: 22px; margin: 0px; padding: 0px;" /><span style="background-color: white; color: #333333; font-family: Verdana, Arial, Helvetica, sans-serif; font-size: 12px; line-height: 22px;">12. </span><strong style="background-color: white; border: none; color: #333333; font-family: Verdana, Arial, Helvetica, sans-serif; font-size: 12px; line-height: 22px; margin: 0px; padding: 0px;">To hide current column</strong><span style="background-color: white; color: #333333; font-family: Verdana, Arial, Helvetica, sans-serif; font-size: 12px; line-height: 22px;">, press ctrl+0</span><br style="background-color: white; border: none; color: #333333; font-family: Verdana, Arial, Helvetica, sans-serif; font-size: 12px; line-height: 22px; margin: 0px; padding: 0px;" /><span style="background-color: white; color: #333333; font-family: Verdana, Arial, Helvetica, sans-serif; font-size: 12px; line-height: 22px;">13. </span><strong style="background-color: white; border: none; color: #333333; font-family: Verdana, Arial, Helvetica, sans-serif; font-size: 12px; line-height: 22px; margin: 0px; padding: 0px;">To unhide rows in selected range</strong><span style="background-color: white; color: #333333; font-family: Verdana, Arial, Helvetica, sans-serif; font-size: 12px; line-height: 22px;">, press ctrl+shift+9</span><br style="background-color: white; border: none; color: #333333; font-family: Verdana, Arial, Helvetica, sans-serif; font-size: 12px; line-height: 22px; margin: 0px; padding: 0px;" /><span style="background-color: white; color: #333333; font-family: Verdana, Arial, Helvetica, sans-serif; font-size: 12px; line-height: 22px;">14. </span><strong style="background-color: white; border: none; color: #333333; font-family: Verdana, Arial, Helvetica, sans-serif; font-size: 12px; line-height: 22px; margin: 0px; padding: 0px;">To unhide columns in selected range</strong><span style="background-color: white; color: #333333; font-family: Verdana, Arial, Helvetica, sans-serif; font-size: 12px; line-height: 22px;">, press ctrl+shift+0</span><br style="background-color: white; border: none; color: #333333; font-family: Verdana, Arial, Helvetica, sans-serif; font-size: 12px; line-height: 22px; margin: 0px; padding: 0px;" /><span style="background-color: white; color: #333333; font-family: Verdana, Arial, Helvetica, sans-serif; font-size: 12px; line-height: 22px;">15. </span><strong style="background-color: white; border: none; color: #333333; font-family: Verdana, Arial, Helvetica, sans-serif; font-size: 12px; line-height: 22px; margin: 0px; padding: 0px;">To recalculate formulas</strong><span style="background-color: white; color: #333333; font-family: Verdana, Arial, Helvetica, sans-serif; font-size: 12px; line-height: 22px;">, press F9</span><br style="background-color: white; border: none; color: #333333; font-family: Verdana, Arial, Helvetica, sans-serif; font-size: 12px; line-height: 22px; margin: 0px; padding: 0px;" /><span style="background-color: white; color: #333333; font-family: Verdana, Arial, Helvetica, sans-serif; font-size: 12px; line-height: 22px;">16. </span><strong style="background-color: white; border: none; color: #333333; font-family: Verdana, Arial, Helvetica, sans-serif; font-size: 12px; line-height: 22px; margin: 0px; padding: 0px;">To select data in current region</strong><span style="background-color: white; color: #333333; font-family: Verdana, Arial, Helvetica, sans-serif; font-size: 12px; line-height: 22px;">, press ctrl+shift+8</span><br style="background-color: white; border: none; color: #333333; font-family: Verdana, Arial, Helvetica, sans-serif; font-size: 12px; line-height: 22px; margin: 0px; padding: 0px;" /><span style="background-color: white; color: #333333; font-family: Verdana, Arial, Helvetica, sans-serif; font-size: 12px; line-height: 22px;">17. </span><strong style="background-color: white; border: none; color: #333333; font-family: Verdana, Arial, Helvetica, sans-serif; font-size: 12px; line-height: 22px; margin: 0px; padding: 0px;">To see formulas in the worksheet</strong><span style="background-color: white; color: #333333; font-family: Verdana, Arial, Helvetica, sans-serif; font-size: 12px; line-height: 22px;">, press ctrl+shift+` (ctrl+~)</span><br style="background-color: white; border: none; color: #333333; font-family: Verdana, Arial, Helvetica, sans-serif; font-size: 12px; line-height: 22px; margin: 0px; padding: 0px;" /><span style="background-color: white; color: #333333; font-family: Verdana, Arial, Helvetica, sans-serif; font-size: 12px; line-height: 22px;">18. </span><strong style="background-color: white; border: none; color: #333333; font-family: Verdana, Arial, Helvetica, sans-serif; font-size: 12px; line-height: 22px; margin: 0px; padding: 0px;">While editing formulas to change the reference type from absolute to relative vice versa</strong><span style="background-color: white; color: #333333; font-family: Verdana, Arial, Helvetica, sans-serif; font-size: 12px; line-height: 22px;">, press F4</span><br style="background-color: white; border: none; color: #333333; font-family: Verdana, Arial, Helvetica, sans-serif; font-size: 12px; line-height: 22px; margin: 0px; padding: 0px;" /><span style="background-color: white; color: #333333; font-family: Verdana, Arial, Helvetica, sans-serif; font-size: 12px; line-height: 22px;">19. </span><strong style="background-color: white; border: none; color: #333333; font-family: Verdana, Arial, Helvetica, sans-serif; font-size: 12px; line-height: 22px; margin: 0px; padding: 0px;">To format a number as currency</strong><span style="background-color: white; color: #333333; font-family: Verdana, Arial, Helvetica, sans-serif; font-size: 12px; line-height: 22px;">, press ctrl+shift+4 (ctrl+$)</span><br style="background-color: white; border: none; color: #333333; font-family: Verdana, Arial, Helvetica, sans-serif; font-size: 12px; line-height: 22px; margin: 0px; padding: 0px;" /><span style="background-color: white; color: #333333; font-family: Verdana, Arial, Helvetica, sans-serif; font-size: 12px; line-height: 22px;">20. </span><strong style="background-color: white; border: none; color: #333333; font-family: Verdana, Arial, Helvetica, sans-serif; font-size: 12px; line-height: 22px; margin: 0px; padding: 0px;">To apply outline border around selected cells</strong><span style="background-color: white; color: #333333; font-family: Verdana, Arial, Helvetica, sans-serif; font-size: 12px; line-height: 22px;">, press ctrl+shift+7</span><br style="background-color: white; border: none; color: #333333; font-family: Verdana, Arial, Helvetica, sans-serif; font-size: 12px; line-height: 22px; margin: 0px; padding: 0px;" /><span style="background-color: white; color: #333333; font-family: Verdana, Arial, Helvetica, sans-serif; font-size: 12px; line-height: 22px;">21. </span><strong style="background-color: white; border: none; color: #333333; font-family: Verdana, Arial, Helvetica, sans-serif; font-size: 12px; line-height: 22px; margin: 0px; padding: 0px;">To open the macros dialog box</strong><span style="background-color: white; color: #333333; font-family: Verdana, Arial, Helvetica, sans-serif; font-size: 12px; line-height: 22px;">, press alt+F8</span><br style="background-color: white; border: none; color: #333333; font-family: Verdana, Arial, Helvetica, sans-serif; font-size: 12px; line-height: 22px; margin: 0px; padding: 0px;" /><span style="background-color: white; color: #333333; font-family: Verdana, Arial, Helvetica, sans-serif; font-size: 12px; line-height: 22px;">22. </span><strong style="background-color: white; border: none; color: #333333; font-family: Verdana, Arial, Helvetica, sans-serif; font-size: 12px; line-height: 22px; margin: 0px; padding: 0px;">To copy value from above cell</strong><span style="background-color: white; color: #333333; font-family: Verdana, Arial, Helvetica, sans-serif; font-size: 12px; line-height: 22px;">, press ctrl+’</span><br style="background-color: white; border: none; color: #333333; font-family: Verdana, Arial, Helvetica, sans-serif; font-size: 12px; line-height: 22px; margin: 0px; padding: 0px;" /><span style="background-color: white; color: #333333; font-family: Verdana, Arial, Helvetica, sans-serif; font-size: 12px; line-height: 22px;">23. </span><strong style="background-color: white; border: none; color: #333333; font-family: Verdana, Arial, Helvetica, sans-serif; font-size: 12px; line-height: 22px; margin: 0px; padding: 0px;">To format current cell with comma formats</strong><span style="background-color: white; color: #333333; font-family: Verdana, Arial, Helvetica, sans-serif; font-size: 12px; line-height: 22px;">, press ctrl+shift+1</span><br style="background-color: white; border: none; color: #333333; font-family: Verdana, Arial, Helvetica, sans-serif; font-size: 12px; line-height: 22px; margin: 0px; padding: 0px;" /><span style="background-color: white; color: #333333; font-family: Verdana, Arial, Helvetica, sans-serif; font-size: 12px; line-height: 22px;">24. </span><strong style="background-color: white; border: none; color: #333333; font-family: Verdana, Arial, Helvetica, sans-serif; font-size: 12px; line-height: 22px; margin: 0px; padding: 0px;">To go to the next worksheet</strong><span style="background-color: white; color: #333333; font-family: Verdana, Arial, Helvetica, sans-serif; font-size: 12px; line-height: 22px;">, press ctrl+shift+pg down</span><br style="background-color: white; border: none; color: #333333; font-family: Verdana, Arial, Helvetica, sans-serif; font-size: 12px; line-height: 22px; margin: 0px; padding: 0px;" /><span style="background-color: white; color: #333333; font-family: Verdana, Arial, Helvetica, sans-serif; font-size: 12px; line-height: 22px;">25. </span><strong style="background-color: white; border: none; color: #333333; font-family: Verdana, Arial, Helvetica, sans-serif; font-size: 12px; line-height: 22px; margin: 0px; padding: 0px;">To go to the previous worksheet</strong><span style="background-color: white; color: #333333; font-family: Verdana, Arial, Helvetica, sans-serif; font-size: 12px; line-height: 22px;">, press ctrl+shift+pg up</span></div>
Unknownnoreply@blogger.com0Bangalore, Karnataka, India12.9715987 77.59456269999998312.4737417 76.946369199999978 13.4694557 78.242756199999988tag:blogger.com,1999:blog-901396199506264276.post-44211890802206158722013-08-01T00:57:00.001-07:002013-08-01T00:57:51.260-07:00Power Pivot<div dir="ltr" style="text-align: left;" trbidi="on">
<h2 style="background-color: white; border: none; color: #c82222; font-family: 'Helvetica Neue', Helvetica, Arial, sans-serif; font-size: 20pt; margin: 0px 0px 10px; padding: 0px; text-shadow: rgb(221, 221, 221) 0px 1pt 1pt; word-spacing: 2pt;">
What is Power Pivot?</h2>
<div style="background-color: white; border: none; color: #333333; font-family: Verdana, Arial, Helvetica, sans-serif; font-size: 12px; line-height: 22px; margin-bottom: 16px; padding: 0px;">
<strong style="border: none; margin: 0px; padding: 0px;"><em style="border: none; margin: 0px; padding: 0px;">Power Pivot is an Excel add-in to connect, analyze & visualize massive amounts of data.</em></strong></div>
<div style="background-color: white; border: none; color: #333333; font-family: Verdana, Arial, Helvetica, sans-serif; font-size: 12px; line-height: 22px; margin-bottom: 16px; padding: 0px;">
Lets take a closer look at the definition.</div>
<div style="background-color: white; border: none; color: #333333; font-family: Verdana, Arial, Helvetica, sans-serif; font-size: 12px; line-height: 22px; margin-bottom: 16px; padding: 0px;">
<strong style="border: none; margin: 0px; padding: 0px;">Connect: </strong>You can use multiple tables of data & set up relationships between them using Power Pivot. For example, you can connect customer details to sales transactions so that you can summarize sales by customer location or gender easily.</div>
<div style="background-color: white; border: none; color: #333333; font-family: Verdana, Arial, Helvetica, sans-serif; font-size: 12px; line-height: 22px; margin-bottom: 16px; padding: 0px;">
<strong style="border: none; margin: 0px; padding: 0px;">Analyze: </strong>You can create simple pivot table style reports or create something exceedingly complex by defining your own calculated fields for values area of the pivot. There is a whole set of formulas defined for exactly this purpose, called as DAX formulas.</div>
<div style="background-color: white; border: none; color: #333333; font-family: Verdana, Arial, Helvetica, sans-serif; font-size: 12px; line-height: 22px; margin-bottom: 16px; padding: 0px;">
<strong style="border: none; margin: 0px; padding: 0px;">Visualize: </strong>Instantly filter your reports using slicers, time lines (Excel 2013 or above only), conditional formats, pivot charts etc. You can even define KPIs and see the performance in bands.</div>
<div style="background-color: white; border: none; color: #333333; font-family: Verdana, Arial, Helvetica, sans-serif; font-size: 12px; line-height: 22px; margin-bottom: 16px; padding: 0px;">
<strong style="border: none; margin: 0px; padding: 0px;">Massive Amounts of Data:</strong> Although your typical Excel worksheet contains a million rows, if you tried to load even half of those with any data, Excel would quickly become slow & lazy. Power Pivot can take a million rows for breakfast and would be hungry for more. It can processes millions of rows of data very quickly and easily, all from the comfort of a standard desktop or laptop.</div>
<div style="background-color: white; border: none; color: #333333; font-family: Verdana, Arial, Helvetica, sans-serif; font-size: 12px; line-height: 22px; margin-bottom: 16px; padding: 0px;">
<strong style="border: none; margin: 0px; padding: 0px;">Excel Add-in: </strong>Power Pivot is an optional free add-in that works with Excel 2010 or above. If you are running Excel 2010, you need to download and install it. Users of Excel 2013 have it easy. Power Pivot comes pre-packaged with Excel, you just need to enable it.</div>
<div style="background-color: white; border: none; color: #333333; font-family: Verdana, Arial, Helvetica, sans-serif; font-size: 12px; line-height: 22px; margin-bottom: 16px; padding: 0px;">
You can do so much more by using & mixing various features of Excel & Power Pivot. Few possibilities are,</div>
<ul style="background-color: white; border: none; color: #333333; font-family: Verdana, Arial, Helvetica, sans-serif; font-size: 13px; line-height: 19px; list-style: none; margin: 0px 0px 10px; padding: 0px;">
<li style="background-image: url(http://cache.chandoo.org/css/images/bullet3.png); background-position: 0px 8px; background-repeat: no-repeat no-repeat; border: none; line-height: 24px; list-style: none; margin: 0px; padding: 3px 0px 3px 12px;"><strong style="border: none; margin: 0px; padding: 0px;">Define your own measures</strong> (calculated fields for power pivot reports) that can summarize data the way you want<ul style="border: none; line-height: 19px; list-style: outside; margin: 8px 0px 5px; padding: 0px;">
<li style="background-image: url(http://cache.chandoo.org/css/images/bullet3.png); background-position: 0px 5px; background-repeat: no-repeat no-repeat; border: none; line-height: 24px; list-style: none; margin: 0px 0px 5px; padding: 0px 0px 0px 12px;">Sum of sales YTD</li>
<li style="background-image: url(http://cache.chandoo.org/css/images/bullet3.png); background-position: 0px 5px; background-repeat: no-repeat no-repeat; border: none; line-height: 24px; list-style: none; margin: 0px 0px 5px; padding: 0px 0px 0px 12px;">Count of distinct customers per product</li>
<li style="background-image: url(http://cache.chandoo.org/css/images/bullet3.png); background-position: 0px 5px; background-repeat: no-repeat no-repeat; border: none; line-height: 24px; list-style: none; margin: 0px 0px 5px; padding: 0px 0px 0px 12px;">Ratio of sales made to single moms vs. single dads</li>
<li style="background-image: url(http://cache.chandoo.org/css/images/bullet3.png); background-position: 0px 5px; background-repeat: no-repeat no-repeat; border: none; line-height: 24px; list-style: none; margin: 0px 0px 5px; padding: 0px 0px 0px 12px;">% of products made by top 10 employees in all <em style="border: none; margin: 0px; padding: 0px;">etc.</em></li>
</ul>
</li>
<li style="background-image: url(http://cache.chandoo.org/css/images/bullet3.png); background-position: 0px 8px; background-repeat: no-repeat no-repeat; border: none; line-height: 24px; list-style: none; margin: 0px; padding: 3px 0px 3px 12px;"><strong style="border: none; margin: 0px; padding: 0px;">Slice and dice data anyway you want</strong><ul style="border: none; line-height: 19px; list-style: outside; margin: 8px 0px 5px; padding: 0px;">
<li style="background-image: url(http://cache.chandoo.org/css/images/bullet3.png); background-position: 0px 5px; background-repeat: no-repeat no-repeat; border: none; line-height: 24px; list-style: none; margin: 0px 0px 5px; padding: 0px 0px 0px 12px;">Filter all reports & charts based on multiple linked slicers</li>
<li style="background-image: url(http://cache.chandoo.org/css/images/bullet3.png); background-position: 0px 5px; background-repeat: no-repeat no-repeat; border: none; line-height: 24px; list-style: none; margin: 0px 0px 5px; padding: 0px 0px 0px 12px;">Slice reports based on values in another (non) related table</li>
</ul>
</li>
<li style="background-image: url(http://cache.chandoo.org/css/images/bullet3.png); background-position: 0px 8px; background-repeat: no-repeat no-repeat; border: none; line-height: 24px; list-style: none; margin: 0px; padding: 3px 0px 3px 12px;"><strong style="border: none; margin: 0px; padding: 0px;">Fetch data from different data sources & integrate</strong></li>
<li style="background-image: url(http://cache.chandoo.org/css/images/bullet3.png); background-position: 0px 8px; background-repeat: no-repeat no-repeat; border: none; line-height: 24px; list-style: none; margin: 0px; padding: 3px 0px 3px 12px;"><strong style="border: none; margin: 0px; padding: 0px;">Set up Power View sheets to create rich visualizations of your data</strong> (Excel 2013 only)<ul style="border: none; line-height: 19px; list-style: outside; margin: 8px 0px 5px; padding: 0px;">
<li style="background-image: url(http://cache.chandoo.org/css/images/bullet3.png); background-position: 0px 5px; background-repeat: no-repeat no-repeat; border: none; line-height: 24px; list-style: none; margin: 0px 0px 5px; padding: 0px 0px 0px 12px;">Create maps, rich interactive charts & more</li>
</ul>
</li>
<li style="background-image: url(http://cache.chandoo.org/css/images/bullet3.png); background-position: 0px 8px; background-repeat: no-repeat no-repeat; border: none; line-height: 24px; list-style: none; margin: 0px; padding: 3px 0px 3px 12px;"><strong style="border: none; margin: 0px; padding: 0px;">Integrate with Excel features to create stunning reports & dashboards</strong><ul style="border: none; line-height: 19px; list-style: outside; margin: 8px 0px 5px; padding: 0px;">
<li style="background-image: url(http://cache.chandoo.org/css/images/bullet3.png); background-position: 0px 5px; background-repeat: no-repeat no-repeat; border: none; line-height: 24px; list-style: none; margin: 0px 0px 5px; padding: 0px 0px 0px 12px;">Combine Power Pivot outputs with Excel features like sparklines, conditional formats, charts & form controls</li>
<li style="background-image: url(http://cache.chandoo.org/css/images/bullet3.png); background-position: 0px 5px; background-repeat: no-repeat no-repeat; border: none; line-height: 24px; list-style: none; margin: 0px 0px 5px; padding: 0px 0px 0px 12px;">Use references to fetch pivot table values & present them in dashboards</li>
</ul>
</li>
</ul>
</div>
Unknownnoreply@blogger.com0Bangalore, Karnataka, India12.9715987 77.59456269999998312.4737417 76.946369199999978 13.4694557 78.242756199999988tag:blogger.com,1999:blog-901396199506264276.post-68778689387818664502013-07-27T05:26:00.000-07:002013-07-27T05:26:00.737-07:003 things that are not so impressive in Excel 2013<div dir="ltr" style="text-align: left;" trbidi="on">
<h3 style="background-color: white; border: none; color: #c82222; font-family: 'Helvetica Neue', Helvetica, Arial, sans-serif; font-size: 16pt; margin: 0px 0px 10px; padding: 0px; text-shadow: rgb(221, 221, 221) 0px 1pt 1pt; word-spacing: 2pt;">
The whole cloud thing:</h3>
<div style="background-color: white; border: none; color: #333333; font-family: Verdana, Arial, Helvetica, sans-serif; font-size: 12px; line-height: 22px; margin-bottom: 16px; padding: 0px;">
While it is understandable that Microsoft wants us all to purchase shiny new Surface tablets and use spreadsheets on the go, it seems like a bad idea. It annoys me that when I want to save a file, the first option I see is <em style="border: none; margin: 0px; padding: 0px;">Kokula Krishna Hari K’s sky drive.</em> The process of saving files to sky drive and later viewing them in browsers is very slow and often results in errors or warnings. Instead, for desktop versions, why not make My computer as first preference?!?</div>
<h3 style="background-color: white; border: none; color: #c82222; font-family: 'Helvetica Neue', Helvetica, Arial, sans-serif; font-size: 16pt; margin: 0px 0px 10px; padding: 0px; text-shadow: rgb(221, 221, 221) 0px 1pt 1pt; word-spacing: 2pt;">
Sharing & Social features:</h3>
<div style="background-color: white; border: none; color: #333333; font-family: Verdana, Arial, Helvetica, sans-serif; font-size: 12px; line-height: 22px; margin-bottom: 16px; padding: 0px;">
Share to Facebook?!? seriously! Why would anyone want to share their spreadsheets on twitter or facebook? Do we really want facebook to know our annual budget & appraisal ratings (so that they can show us ads that say –<em style="border: none; margin: 0px; padding: 0px;">Buy our scissors and cut your budget in half </em>)?</div>
<h3 style="background-color: white; border: none; color: #c82222; font-family: 'Helvetica Neue', Helvetica, Arial, sans-serif; font-size: 16pt; margin: 0px 0px 10px; padding: 0px; text-shadow: rgb(221, 221, 221) 0px 1pt 1pt; word-spacing: 2pt;">
Power Pivot is not for masses:</h3>
<div style="background-color: white; border: none; color: #333333; font-family: Verdana, Arial, Helvetica, sans-serif; font-size: 12px; line-height: 22px; margin-bottom: 16px; padding: 0px;">
Microsoft positioned Power Pivot as BI for masses, offered it for free in Excel 2010. Then in Excel 2013, they went ahead and implemented a licensing policy that looks just as complicated as my lawyer’s invoice. Why would a for-profit company like MS want to not offer powerful tools like Power Pivot to masses for a fee? Why sell it only to corporate customers through volume licensing program? beats me.</div>
<h2 style="background-color: white; border: none; color: #c82222; font-family: 'Helvetica Neue', Helvetica, Arial, sans-serif; font-size: 20pt; margin: 0px 0px 10px; padding: 0px; text-shadow: rgb(221, 221, 221) 0px 1pt 1pt; word-spacing: 2pt;">
Bottom line</h2>
<div style="background-color: white; border: none; color: #333333; font-family: Verdana, Arial, Helvetica, sans-serif; font-size: 12px; line-height: 22px; margin-bottom: 16px; padding: 0px;">
Despite these minor annoyances, I think Excel 2013 is a well designed, solid & powerful software ready to make more people awesome in their work. With features like tablet compatibility, data model, slicers & timelines, improved UI & color schemes it has quickly become my first choice when I want to use a spreadsheet (I run Excel 2010 & 2013 on same computer).</div>
</div>
Unknownnoreply@blogger.com0Bangalore, Karnataka, India12.9715987 77.59456269999998312.4737417 76.946369199999978 13.4694557 78.242756199999988tag:blogger.com,1999:blog-901396199506264276.post-8342568154018371832013-07-26T05:23:00.000-07:002013-07-26T05:23:00.372-07:00Easier Charting<div dir="ltr" style="text-align: left;" trbidi="on">
<div style="background-color: white; border: none; color: #333333; line-height: 22px; margin-bottom: 16px; padding: 0px;">
<span style="font-family: Times, Times New Roman, serif;">In Excel 2013, there are massive changes in charting. Now you can create combination charts, add secondary axis, set up smart data labels, format the chart or switch styles with ease. Microsoft revamped the default formats too so that when you make a chart from data, it is ready for presentation (with out too many tweaks).</span></div>
<div style="background-color: white; border: none; color: #333333; line-height: 22px; margin-bottom: 16px; padding: 0px;">
<span style="font-family: Times, Times New Roman, serif;">Some of favorite charting features are,</span></div>
<ul style="background-color: white; border: none; color: #333333; line-height: 19px; list-style: none; margin: 0px 0px 10px; padding: 0px;">
<li style="background-image: url(http://cache.chandoo.org/css/images/bullet3.png); background-position: 0px 8px; background-repeat: no-repeat no-repeat; border: none; line-height: 24px; list-style: none; margin: 0px; padding: 3px 0px 3px 12px;"><span style="font-family: Times, Times New Roman, serif;">Recommended charts feature that tells you which charts go well with your data.</span></li>
<li style="background-image: url(http://cache.chandoo.org/css/images/bullet3.png); background-position: 0px 8px; background-repeat: no-repeat no-repeat; border: none; line-height: 24px; list-style: none; margin: 0px; padding: 3px 0px 3px 12px;"><span style="font-family: Times, Times New Roman, serif;">A screen where you can change the chart type for each series easily.</span></li>
<li style="background-image: url(http://cache.chandoo.org/css/images/bullet3.png); background-position: 0px 8px; background-repeat: no-repeat no-repeat; border: none; line-height: 24px; list-style: none; margin: 0px; padding: 3px 0px 3px 12px;"><span style="font-family: Times, Times New Roman, serif;">Common chart customizations are a click away</span></li>
<li style="background-image: url(http://cache.chandoo.org/css/images/bullet3.png); background-position: 0px 8px; background-repeat: no-repeat no-repeat; border: none; line-height: 24px; list-style: none; margin: 0px; padding: 3px 0px 3px 12px;"><span style="font-family: Times, Times New Roman, serif;">Ability to create scatter plots based on a variety of input data layouts.</span></li>
</ul>
<div style="background-color: white; border: none; color: #333333; line-height: 22px; margin-bottom: 16px; padding: 0px; text-align: center;">
<span style="font-family: Times, Times New Roman, serif;"><img alt="Creating a combination chart in Excel 2013 is very easy" class="alignnone" height="417" src="http://img.chandoo.org/excel2013/creating-combo-charts-is-easy-excel-2013.png" style="border: none; margin: 0px; padding: 0px;" width="478" /></span></div>
<div style="background-color: white; border: none; color: #333333; line-height: 22px; margin-bottom: 16px; padding: 0px;">
<span style="font-family: Times, Times New Roman, serif;"><em style="border: none; margin: 0px; padding: 0px;">That said not everything is rosy with 2013 charting</em>. For example, I do not like that we have to go through sidebar pane to customize charts (formatting etc.) instead of dialog box.</span></div>
</div>
Unknownnoreply@blogger.com0Bangalore, Karnataka, India12.9715987 77.59456269999998312.4737417 76.946369199999978 13.4694557 78.242756199999988tag:blogger.com,1999:blog-901396199506264276.post-63946527985761942622013-07-25T05:23:00.001-07:002013-07-25T05:23:21.117-07:00Distinct Counts & more in your Pivot<div dir="ltr" style="text-align: left;" trbidi="on">
<div style="background-color: white; border: none; color: #333333; font-family: Verdana, Arial, Helvetica, sans-serif; font-size: 12px; line-height: 22px; margin-bottom: 16px; padding: 0px;">
If you are really quiet, you can hear an analyst in your company screaming with joy once they realize that in Excel 2013, you can get <em style="border: none; margin: 0px; padding: 0px;"><strong style="border: none; margin: 0px; padding: 0px;">distinct count</strong></em><strong style="border: none; margin: 0px; padding: 0px;"> </strong>of values in pivot reports!!!</div>
<div style="background-color: white; border: none; color: #333333; font-family: Verdana, Arial, Helvetica, sans-serif; font-size: 12px; line-height: 22px; margin-bottom: 16px; padding: 0px; text-align: center;">
<img alt="Distinct counts in Excel 2013 pivot tables" class="alignnone" src="http://img.chandoo.org/excel2013/distinct-count-in-excel-2013-pivot-tables.png" style="border: none; margin: 0px; padding: 0px;" /></div>
<div style="background-color: white; border: none; color: #333333; font-family: Verdana, Arial, Helvetica, sans-serif; font-size: 12px; line-height: 22px; margin-bottom: 16px; padding: 0px;">
That is right, using Excel 2013 pivot reports, you can find out distinct counts. No extra formulas or no arrays or no VBA.</div>
</div>
Unknownnoreply@blogger.com0Bangalore, Karnataka, India12.9715987 77.59456269999998312.4737417 76.946369199999978 13.4694557 78.242756199999988tag:blogger.com,1999:blog-901396199506264276.post-63659617111402501312013-07-22T03:02:00.002-07:002013-07-22T03:02:32.958-07:00Timelines - New Feature in Excel 2013<div dir="ltr" style="text-align: left;" trbidi="on">
<div style="text-align: justify;">
These days everybody boasts of a massive spreadsheet. But almost no one needs all the data at same time. We are always filtering data for latest quarter, 6 months starting Mother’s day or 8 weeks from November 1st etc. Of course, you can use auto-filter and select all the dates. But it is a pain.</div>
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEh568gyQeBNYDO3S_esBW1HrPd5GzvqR__vj-mklsgR-NZ0WtnSUmp-p_0fTilvq_swYgGJ0HwDRD60ARtwde0cdfwxnpc7kMK3aR0gUq9rPeD4Etjxocrx_T5rkW17kNxMwvvwFjWnCBA/s1600/Timelines.gif" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEh568gyQeBNYDO3S_esBW1HrPd5GzvqR__vj-mklsgR-NZ0WtnSUmp-p_0fTilvq_swYgGJ0HwDRD60ARtwde0cdfwxnpc7kMK3aR0gUq9rPeD4Etjxocrx_T5rkW17kNxMwvvwFjWnCBA/s1600/Timelines.gif" /></a></div>
<br />
<br />
<div style="text-align: justify;">
Thanks to Timelines, filtering for dates is a breeze. You can add timelines for any date column in a pivot table / pivot chart. I am sure your clients & bosses will love it.</div>
</div>
Unknownnoreply@blogger.com0Bangalore, Karnataka, India12.9715987 77.59456269999998312.4737417 76.946369199999978 13.4694557 78.242756199999988tag:blogger.com,1999:blog-901396199506264276.post-65319176872759228662013-07-22T02:57:00.000-07:002013-07-22T02:57:34.726-07:00Flash Fill - Microsoft Office Excel 2013<div dir="ltr" style="text-align: left;" trbidi="on">
<div style="text-align: justify;">
Imagine Flash (the super hero, not browser add-in) is using Excel to extract the middle names of all his villains. Now, flash being flash, do you think he will slowly type out the middle names one at a time? Of course, he can learn Excel formulas and do it in one stroke. But he is too busy running around & saving earth. So, obviously he would use Flash Fill.</div>
<br />
<div style="text-align: justify;">
Flash Fill works almost like magic. It looks at what you are typing and sees if there is any pattern in it (based on adjacent columns etc.) and then suggests a fill down option. See this demo.</div>
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhVJsGtlw-tBGLgdwZuoeSdrHWb8zUeLzjwpfr-yMy1k4fHKhomVZ8aMysvbj77C_1s4AH6vdfXxgvFVhJNfs7ocZRuaPxkDCnD0_SqjWyX9H2boNd40WRvuxpehyrdu8jK9biBAcVNC5Q/s1600/Excel+Flash+Fill.gif" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhVJsGtlw-tBGLgdwZuoeSdrHWb8zUeLzjwpfr-yMy1k4fHKhomVZ8aMysvbj77C_1s4AH6vdfXxgvFVhJNfs7ocZRuaPxkDCnD0_SqjWyX9H2boNd40WRvuxpehyrdu8jK9biBAcVNC5Q/s1600/Excel+Flash+Fill.gif" /></a></div>
<div class="separator" style="clear: both; text-align: center;">
<br /></div>
<div style="text-align: center;">
<b>Shortcut Key : Press CTRL+E to activate flash fill.</b></div>
</div>
Unknownnoreply@blogger.com0Bangalore, Karnataka, India12.9715987 77.59456269999998312.4737417 76.946369199999978 13.4694557 78.242756199999988tag:blogger.com,1999:blog-901396199506264276.post-18324933038577651872013-07-20T20:18:00.000-07:002013-07-20T20:18:44.659-07:00Autonumbering in Excel 2013<div dir="ltr" style="text-align: left;" trbidi="on">
<div class="MsoNormal" style="text-align: justify;">
<span style="font-family: Times, Times New Roman, serif;">It is often useful to number the
rows in an Excel spreadsheet as a means to help organize your data, much in the
same way that column heading are added.<o:p></o:p></span></div>
<div class="MsoNormal" style="text-align: justify;">
<span style="font-family: Times, Times New Roman, serif;"><br /></span></div>
<div class="MsoNormal" style="text-align: justify;">
<span style="font-family: Times, Times New Roman, serif;">There is very quick and simple
way to number rows, and it doesn’t involve any math formulas or special tricks.
Let’s begin with the example shown in Picture One.<o:p></o:p></span></div>
<div class="MsoNormal" style="text-align: justify;">
<span style="font-family: Times, Times New Roman, serif;"><br /></span></div>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhSWjOC78kBVnGVdqgTW167xAMEEOipcjpA6D_yhgQnxqBQd1dBF7ZH5jjzZ_hwAVS2UxF-PKmHIfZ7XplBgiIusqEUb55YzC0BLUwj-Zn5YYj5L78KGqRb6Tml9GOo1k9cuH6cvJG7JWk/s1600/img1.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><span style="font-family: Times, Times New Roman, serif;"><img border="0" height="320" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhSWjOC78kBVnGVdqgTW167xAMEEOipcjpA6D_yhgQnxqBQd1dBF7ZH5jjzZ_hwAVS2UxF-PKmHIfZ7XplBgiIusqEUb55YzC0BLUwj-Zn5YYj5L78KGqRb6Tml9GOo1k9cuH6cvJG7JWk/s320/img1.png" width="176" /></span></a></div>
<div class="MsoNormal" style="text-align: justify;">
<span style="font-family: Times, Times New Roman, serif;"><br /></span></div>
<div align="center" class="MsoNormal" style="page-break-after: avoid; text-align: center;">
<!--[if gte vml 1]><v:shapetype id="_x0000_t75"
coordsize="21600,21600" o:spt="75" o:preferrelative="t" path="m@4@5l@4@11@9@11@9@5xe"
filled="f" stroked="f">
<v:stroke joinstyle="miter"/>
<v:formulas>
<v:f eqn="if lineDrawn pixelLineWidth 0"/>
<v:f eqn="sum @0 1 0"/>
<v:f eqn="sum 0 0 @1"/>
<v:f eqn="prod @2 1 2"/>
<v:f eqn="prod @3 21600 pixelWidth"/>
<v:f eqn="prod @3 21600 pixelHeight"/>
<v:f eqn="sum @0 0 1"/>
<v:f eqn="prod @6 1 2"/>
<v:f eqn="prod @7 21600 pixelWidth"/>
<v:f eqn="sum @8 21600 0"/>
<v:f eqn="prod @7 21600 pixelHeight"/>
<v:f eqn="sum @10 21600 0"/>
</v:formulas>
<v:path o:extrusionok="f" gradientshapeok="t" o:connecttype="rect"/>
<o:lock v:ext="edit" aspectratio="t"/>
</v:shapetype><v:shape id="Picture_x0020_1" o:spid="_x0000_i1025" type="#_x0000_t75"
style='width:133.5pt;height:239.25pt;visibility:visible;mso-wrap-style:square'
o:bordertopcolor="yellow pure" o:borderleftcolor="yellow pure"
o:borderbottomcolor="yellow pure" o:borderrightcolor="yellow pure">
<v:imagedata src="file:///C:\Users\kkhari\AppData\Local\Temp\msohtmlclip1\01\clip_image001.png"
o:title=""/>
<w:bordertop type="single" width="8"/>
<w:borderleft type="single" width="8"/>
<w:borderbottom type="single" width="8"/>
<w:borderright type="single" width="8"/>
</v:shape><![endif]--><!--[if !vml]--><span style="font-family: Times, Times New Roman, serif;"><img src="file:///C:\Users\kkhari\AppData\Local\Temp\msohtmlclip1\01\clip_image002.jpg" v:shapes="Picture_x0020_1" /><!--[endif]--><o:p></o:p></span></div>
<div align="center" class="MsoCaption" style="text-align: center;">
<span style="font-family: Times, Times New Roman, serif;">Figure <!--[if supportFields]><span
style='mso-element:field-begin'></span><span style='mso-spacerun:yes'> </span>SEQ
Figure \* ARABIC <span style='mso-element:field-separator'></span><![endif]-->1<!--[if supportFields]><span style='mso-element:
field-end'></span><![endif]--> - Excel with Filled Series<o:p></o:p></span></div>
<div class="MsoNormal">
<span style="font-family: Times, Times New Roman, serif;"><br /></span></div>
<div class="MsoNormal" style="text-align: center;">
<span style="font-family: Times, Times New Roman, serif;"><b>Guidelines</b></span></div>
<div class="MsoNormal">
<span style="font-family: Times, Times New Roman, serif;"><br /></span></div>
<div class="MsoNormal">
<span style="font-family: Times, Times New Roman, serif;">Step #1) – Enter a “1” cell A1 and a “2’ in cell A2.<o:p></o:p></span></div>
<div class="MsoNormal">
<span style="font-family: Times, Times New Roman, serif;">Step #2) – Highlight cells A1 and A2. A small black square will
appear in the lower right corner of the highlight area.<o:p></o:p></span></div>
<div class="MsoNormal">
<span style="font-family: Times, Times New Roman, serif;">Step #3) – Set your cursor on this small black square, and a
black plus sign (┼) will appear.
Drag the plus sign down to highlight the cells below cell A2 in column A.<o:p></o:p></span></div>
<div class="MsoNormal">
<span style="font-family: Times, Times New Roman, serif;"><br /></span></div>
<div class="MsoNormal">
<span style="font-family: Times, Times New Roman, serif;">When you release the cursor, the selected cells will be
automatically numbered.<o:p></o:p></span></div>
<div class="MsoNormal">
<span style="font-family: Times, Times New Roman, serif;"><br /></span></div>
<div class="MsoNormal">
<span style="font-family: Times, Times New Roman, serif;">The autonumber feature has can be used in several other
ways. For example, you can number your rows in increments of any number. To
begin, just number cell A1 as 5 and A2 as 10, and then repeat steps 2 and 3. <o:p></o:p></span></div>
<br />
<div class="MsoNormal">
<span style="font-family: Times, Times New Roman, serif;"><br /></span></div>
<div class="MsoNormal">
<span style="font-family: Times, Times New Roman, serif;">In addition, you can use the autonumber feature with dates. Let’s
assume you want to create a reminder list for a report that is due once a week.
Type the first date in cell A1 and the second date in cell A2. Repeat Steps 2
and 3, and you will get a result.</span><o:p></o:p></div>
</div>
Unknownnoreply@blogger.com0Bangalore, Karnataka, India12.9715987 77.59456269999998312.4737417 76.946369199999978 13.4694557 78.242756199999988