tag:blogger.com,1999:blog-3290909630015579982024-03-12T18:36:15.021-07:00Excel tipsA blog about Microsoft Excel tweaks and tipsKishorehttp://www.blogger.com/profile/08971014751730405491noreply@blogger.comBlogger6125tag:blogger.com,1999:blog-329090963001557998.post-58279076912760977072016-04-18T00:06:00.000-07:002016-04-18T00:06:41.254-07:00Top Excel Keyboard shortcuts to save yourself daily (Version: 2013):<div dir="ltr" style="text-align: left;" trbidi="on">
<div style="margin: 0in 0in 0pt;">
<b style="mso-bidi-font-weight: normal;"><span style="font-family: "calibri";"> </span></b></div>
<table border="1" cellpadding="0" cellspacing="0" style="border-collapse: collapse; border-image: none; border: currentColor; mso-border-alt: solid white .5pt; mso-border-themecolor: background1; mso-padding-alt: 0in 5.4pt 0in 5.4pt; mso-yfti-tbllook: 1184;">
<tbody>
<tr style="mso-yfti-firstrow: yes; mso-yfti-irow: -1; mso-yfti-lastfirstrow: yes;">
<td style="background: rgb(91, 155, 213); border-color: white rgb(0, 0, 0) white white; border-image: none; border-style: solid none solid solid; border-width: 1pt 0px 1pt 1pt; mso-background-themecolor: accent1; mso-border-bottom-alt: solid white .5pt; mso-border-bottom-themecolor: background1; mso-border-left-alt: solid white .5pt; mso-border-left-themecolor: background1; mso-border-themecolor: background1; mso-border-top-alt: solid white .5pt; mso-border-top-themecolor: background1; padding: 0in 5.4pt; width: 37.75pt;" valign="top" width="50"><div style="line-height: normal; margin: 0in 0in 0pt; mso-yfti-cnfc: 5;">
<span style="color: white; mso-bidi-font-weight: bold; mso-themecolor: background1;"><span style="font-family: "calibri";">Sl.No.</span></span></div>
</td>
<td style="background: rgb(91, 155, 213); border-color: white rgb(0, 0, 0); border-style: solid none; border-width: 1pt 0px; mso-background-themecolor: accent1; mso-border-bottom-alt: solid white .5pt; mso-border-bottom-themecolor: background1; mso-border-top-alt: solid white .5pt; mso-border-top-themecolor: background1; padding: 0in 5.4pt; width: 187.85pt;" valign="top" width="250"><div style="line-height: normal; margin: 0in 0in 0pt; mso-yfti-cnfc: 1;">
<span style="color: white; mso-bidi-font-weight: bold; mso-themecolor: background1;"><span style="font-family: "calibri";">Shortcut used for </span></span></div>
</td>
<td style="background: rgb(91, 155, 213); border-color: white rgb(0, 0, 0); border-style: solid none; border-width: 1pt 0px; mso-background-themecolor: accent1; mso-border-bottom-alt: solid white .5pt; mso-border-bottom-themecolor: background1; mso-border-top-alt: solid white .5pt; mso-border-top-themecolor: background1; padding: 0in 5.4pt; width: 122.3pt;" valign="top" width="163"><div style="line-height: normal; margin: 0in 0in 0pt; mso-yfti-cnfc: 1;">
<span style="color: white; mso-bidi-font-weight: bold; mso-themecolor: background1;"><span style="font-family: "calibri";">Keyboard Shortcut1</span></span></div>
</td>
<td style="background: rgb(91, 155, 213); border-color: white white white rgb(0, 0, 0); border-image: none; border-style: solid solid solid none; border-width: 1pt 1pt 1pt 0px; mso-background-themecolor: accent1; mso-border-bottom-alt: solid white .5pt; mso-border-bottom-themecolor: background1; mso-border-right-alt: solid white .5pt; mso-border-right-themecolor: background1; mso-border-themecolor: background1; mso-border-top-alt: solid white .5pt; mso-border-top-themecolor: background1; padding: 0in 5.4pt; width: 119.6pt;" valign="top" width="159"><div style="line-height: normal; margin: 0in 0in 0pt; mso-yfti-cnfc: 1;">
<span style="color: white; mso-bidi-font-weight: bold; mso-themecolor: background1;"><span style="font-family: "calibri";">Keyboard Shortcut2</span></span></div>
</td>
</tr>
<tr style="mso-yfti-irow: 0;">
<td style="background: rgb(91, 155, 213); border-color: rgb(0, 0, 0) white white; border-image: none; border-style: none solid solid; border-width: 0px 1pt 1pt; mso-background-themecolor: accent1; mso-border-alt: solid white .5pt; mso-border-themecolor: background1; mso-border-top-alt: solid white .5pt; mso-border-top-themecolor: background1; padding: 0in 5.4pt; width: 37.75pt;" valign="top" width="50"><div style="line-height: normal; margin: 0in 0in 0pt; mso-yfti-cnfc: 68;">
<b><span style="color: white; mso-themecolor: background1;"><span style="font-family: "calibri";">1</span></span></b></div>
</td>
<td style="background: rgb(189, 214, 238); border-color: rgb(0, 0, 0) white white rgb(0, 0, 0); border-style: none solid solid none; border-width: 0px 1pt 1pt 0px; mso-background-themecolor: accent1; mso-background-themetint: 102; mso-border-alt: solid white .5pt; mso-border-bottom-themecolor: background1; mso-border-left-alt: solid white .5pt; mso-border-left-themecolor: background1; mso-border-right-themecolor: background1; mso-border-themecolor: background1; mso-border-top-alt: solid white .5pt; mso-border-top-themecolor: background1; padding: 0in 5.4pt; width: 187.85pt;" valign="top" width="250"><div style="line-height: normal; margin: 0in 0in 0pt; mso-yfti-cnfc: 64;">
<span style="font-family: "calibri";">Applying auto filter</span></div>
</td>
<td style="background: rgb(189, 214, 238); border-color: rgb(0, 0, 0) white white rgb(0, 0, 0); border-style: none solid solid none; border-width: 0px 1pt 1pt 0px; mso-background-themecolor: accent1; mso-background-themetint: 102; mso-border-alt: solid white .5pt; mso-border-bottom-themecolor: background1; mso-border-left-alt: solid white .5pt; mso-border-left-themecolor: background1; mso-border-right-themecolor: background1; mso-border-themecolor: background1; mso-border-top-alt: solid white .5pt; mso-border-top-themecolor: background1; padding: 0in 5.4pt; width: 122.3pt;" valign="top" width="163"><div style="line-height: normal; margin: 0in 0in 0pt; mso-yfti-cnfc: 64;">
<span style="font-family: "calibri";">Alt+D+F+F</span></div>
</td>
<td style="background: rgb(189, 214, 238); border-color: rgb(0, 0, 0) white white rgb(0, 0, 0); border-style: none solid solid none; border-width: 0px 1pt 1pt 0px; mso-background-themecolor: accent1; mso-background-themetint: 102; mso-border-alt: solid white .5pt; mso-border-bottom-themecolor: background1; mso-border-left-alt: solid white .5pt; mso-border-left-themecolor: background1; mso-border-right-themecolor: background1; mso-border-themecolor: background1; mso-border-top-alt: solid white .5pt; mso-border-top-themecolor: background1; padding: 0in 5.4pt; width: 119.6pt;" valign="top" width="159"><div style="line-height: normal; margin: 0in 0in 0pt; mso-yfti-cnfc: 64;">
<span style="font-family: "calibri";">Alt+A+T</span></div>
</td>
</tr>
<tr style="height: 3.5pt; mso-yfti-irow: 1;">
<td style="background: rgb(91, 155, 213); border-color: rgb(0, 0, 0) white white; border-image: none; border-style: none solid solid; border-width: 0px 1pt 1pt; height: 3.5pt; mso-background-themecolor: accent1; mso-border-alt: solid white .5pt; mso-border-themecolor: background1; mso-border-top-alt: solid white .5pt; mso-border-top-themecolor: background1; padding: 0in 5.4pt; width: 37.75pt;" valign="top" width="50"><div style="line-height: normal; margin: 0in 0in 0pt; mso-yfti-cnfc: 4;">
<b><span style="color: white; mso-themecolor: background1;"><span style="font-family: "calibri";">2</span></span></b></div>
</td>
<td style="background: rgb(222, 234, 246); border-color: rgb(0, 0, 0) white white rgb(0, 0, 0); border-style: none solid solid none; border-width: 0px 1pt 1pt 0px; height: 3.5pt; mso-background-themecolor: accent1; mso-background-themetint: 51; mso-border-alt: solid white .5pt; mso-border-bottom-themecolor: background1; mso-border-left-alt: solid white .5pt; mso-border-left-themecolor: background1; mso-border-right-themecolor: background1; mso-border-themecolor: background1; mso-border-top-alt: solid white .5pt; mso-border-top-themecolor: background1; padding: 0in 5.4pt; width: 187.85pt;" valign="top" width="250"><div style="line-height: normal; margin: 0in 0in 0pt;">
<span style="font-family: "calibri";">Insert pivot table</span></div>
</td>
<td style="background: rgb(222, 234, 246); border-color: rgb(0, 0, 0) white white rgb(0, 0, 0); border-style: none solid solid none; border-width: 0px 1pt 1pt 0px; height: 3.5pt; mso-background-themecolor: accent1; mso-background-themetint: 51; mso-border-alt: solid white .5pt; mso-border-bottom-themecolor: background1; mso-border-left-alt: solid white .5pt; mso-border-left-themecolor: background1; mso-border-right-themecolor: background1; mso-border-themecolor: background1; mso-border-top-alt: solid white .5pt; mso-border-top-themecolor: background1; padding: 0in 5.4pt; width: 122.3pt;" valign="top" width="163"><div style="line-height: normal; margin: 0in 0in 0pt;">
<span style="font-family: "calibri";">Alt+N+V</span></div>
</td>
<td style="background: rgb(222, 234, 246); border-color: rgb(0, 0, 0) white white rgb(0, 0, 0); border-style: none solid solid none; border-width: 0px 1pt 1pt 0px; height: 3.5pt; mso-background-themecolor: accent1; mso-background-themetint: 51; mso-border-alt: solid white .5pt; mso-border-bottom-themecolor: background1; mso-border-left-alt: solid white .5pt; mso-border-left-themecolor: background1; mso-border-right-themecolor: background1; mso-border-themecolor: background1; mso-border-top-alt: solid white .5pt; mso-border-top-themecolor: background1; padding: 0in 5.4pt; width: 119.6pt;" valign="top" width="159"><div style="line-height: normal; margin: 0in 0in 0pt;">
<span style="font-family: "calibri";">NA</span></div>
</td>
</tr>
<tr style="mso-yfti-irow: 2;">
<td style="background: rgb(91, 155, 213); border-color: rgb(0, 0, 0) white white; border-image: none; border-style: none solid solid; border-width: 0px 1pt 1pt; mso-background-themecolor: accent1; mso-border-alt: solid white .5pt; mso-border-themecolor: background1; mso-border-top-alt: solid white .5pt; mso-border-top-themecolor: background1; padding: 0in 5.4pt; width: 37.75pt;" valign="top" width="50"><div style="line-height: normal; margin: 0in 0in 0pt; mso-yfti-cnfc: 68;">
<b><span style="color: white; mso-themecolor: background1;"><span style="font-family: "calibri";">3</span></span></b></div>
</td>
<td style="background: rgb(189, 214, 238); border-color: rgb(0, 0, 0) white white rgb(0, 0, 0); border-style: none solid solid none; border-width: 0px 1pt 1pt 0px; mso-background-themecolor: accent1; mso-background-themetint: 102; mso-border-alt: solid white .5pt; mso-border-bottom-themecolor: background1; mso-border-left-alt: solid white .5pt; mso-border-left-themecolor: background1; mso-border-right-themecolor: background1; mso-border-themecolor: background1; mso-border-top-alt: solid white .5pt; mso-border-top-themecolor: background1; padding: 0in 5.4pt; width: 187.85pt;" valign="top" width="250"><div style="line-height: normal; margin: 0in 0in 0pt; mso-yfti-cnfc: 64;">
<span style="font-family: "calibri";">Align middle</span></div>
</td>
<td style="background: rgb(189, 214, 238); border-color: rgb(0, 0, 0) white white rgb(0, 0, 0); border-style: none solid solid none; border-width: 0px 1pt 1pt 0px; mso-background-themecolor: accent1; mso-background-themetint: 102; mso-border-alt: solid white .5pt; mso-border-bottom-themecolor: background1; mso-border-left-alt: solid white .5pt; mso-border-left-themecolor: background1; mso-border-right-themecolor: background1; mso-border-themecolor: background1; mso-border-top-alt: solid white .5pt; mso-border-top-themecolor: background1; padding: 0in 5.4pt; width: 122.3pt;" valign="top" width="163"><div style="line-height: normal; margin: 0in 0in 0pt; mso-yfti-cnfc: 64;">
<span style="font-family: "calibri";">Alt+H+AC</span></div>
</td>
<td style="background: rgb(189, 214, 238); border-color: rgb(0, 0, 0) white white rgb(0, 0, 0); border-style: none solid solid none; border-width: 0px 1pt 1pt 0px; mso-background-themecolor: accent1; mso-background-themetint: 102; mso-border-alt: solid white .5pt; mso-border-bottom-themecolor: background1; mso-border-left-alt: solid white .5pt; mso-border-left-themecolor: background1; mso-border-right-themecolor: background1; mso-border-themecolor: background1; mso-border-top-alt: solid white .5pt; mso-border-top-themecolor: background1; padding: 0in 5.4pt; width: 119.6pt;" valign="top" width="159"><div style="line-height: normal; margin: 0in 0in 0pt; mso-yfti-cnfc: 64;">
<span style="font-family: "calibri";">NA</span></div>
</td>
</tr>
<tr style="mso-yfti-irow: 3;">
<td style="background: rgb(91, 155, 213); border-color: rgb(0, 0, 0) white white; border-image: none; border-style: none solid solid; border-width: 0px 1pt 1pt; mso-background-themecolor: accent1; mso-border-alt: solid white .5pt; mso-border-themecolor: background1; mso-border-top-alt: solid white .5pt; mso-border-top-themecolor: background1; padding: 0in 5.4pt; width: 37.75pt;" valign="top" width="50"><div style="line-height: normal; margin: 0in 0in 0pt; mso-yfti-cnfc: 4;">
<b><span style="color: white; mso-themecolor: background1;"><span style="font-family: "calibri";">4</span></span></b></div>
</td>
<td style="background: rgb(222, 234, 246); border-color: rgb(0, 0, 0) white white rgb(0, 0, 0); border-style: none solid solid none; border-width: 0px 1pt 1pt 0px; mso-background-themecolor: accent1; mso-background-themetint: 51; mso-border-alt: solid white .5pt; mso-border-bottom-themecolor: background1; mso-border-left-alt: solid white .5pt; mso-border-left-themecolor: background1; mso-border-right-themecolor: background1; mso-border-themecolor: background1; mso-border-top-alt: solid white .5pt; mso-border-top-themecolor: background1; padding: 0in 5.4pt; width: 187.85pt;" valign="top" width="250"><div style="line-height: normal; margin: 0in 0in 0pt;">
<span style="font-family: "calibri";">Align Left</span></div>
</td>
<td style="background: rgb(222, 234, 246); border-color: rgb(0, 0, 0) white white rgb(0, 0, 0); border-style: none solid solid none; border-width: 0px 1pt 1pt 0px; mso-background-themecolor: accent1; mso-background-themetint: 51; mso-border-alt: solid white .5pt; mso-border-bottom-themecolor: background1; mso-border-left-alt: solid white .5pt; mso-border-left-themecolor: background1; mso-border-right-themecolor: background1; mso-border-themecolor: background1; mso-border-top-alt: solid white .5pt; mso-border-top-themecolor: background1; padding: 0in 5.4pt; width: 122.3pt;" valign="top" width="163"><div style="line-height: normal; margin: 0in 0in 0pt;">
<span style="font-family: "calibri";">Alt+H+AL</span></div>
</td>
<td style="background: rgb(222, 234, 246); border-color: rgb(0, 0, 0) white white rgb(0, 0, 0); border-style: none solid solid none; border-width: 0px 1pt 1pt 0px; mso-background-themecolor: accent1; mso-background-themetint: 51; mso-border-alt: solid white .5pt; mso-border-bottom-themecolor: background1; mso-border-left-alt: solid white .5pt; mso-border-left-themecolor: background1; mso-border-right-themecolor: background1; mso-border-themecolor: background1; mso-border-top-alt: solid white .5pt; mso-border-top-themecolor: background1; padding: 0in 5.4pt; width: 119.6pt;" valign="top" width="159"><div style="line-height: normal; margin: 0in 0in 0pt;">
<span style="font-family: "calibri";">NA</span></div>
</td>
</tr>
<tr style="mso-yfti-irow: 4;">
<td style="background: rgb(91, 155, 213); border-color: rgb(0, 0, 0) white white; border-image: none; border-style: none solid solid; border-width: 0px 1pt 1pt; mso-background-themecolor: accent1; mso-border-alt: solid white .5pt; mso-border-themecolor: background1; mso-border-top-alt: solid white .5pt; mso-border-top-themecolor: background1; padding: 0in 5.4pt; width: 37.75pt;" valign="top" width="50"><div style="line-height: normal; margin: 0in 0in 0pt; mso-yfti-cnfc: 68;">
<b><span style="color: white; mso-themecolor: background1;"><span style="font-family: "calibri";">5</span></span></b></div>
</td>
<td style="background: rgb(189, 214, 238); border-color: rgb(0, 0, 0) white white rgb(0, 0, 0); border-style: none solid solid none; border-width: 0px 1pt 1pt 0px; mso-background-themecolor: accent1; mso-background-themetint: 102; mso-border-alt: solid white .5pt; mso-border-bottom-themecolor: background1; mso-border-left-alt: solid white .5pt; mso-border-left-themecolor: background1; mso-border-right-themecolor: background1; mso-border-themecolor: background1; mso-border-top-alt: solid white .5pt; mso-border-top-themecolor: background1; padding: 0in 5.4pt; width: 187.85pt;" valign="top" width="250"><div style="line-height: normal; margin: 0in 0in 0pt; mso-yfti-cnfc: 64;">
<span style="font-family: "calibri";">Align Right</span></div>
</td>
<td style="background: rgb(189, 214, 238); border-color: rgb(0, 0, 0) white white rgb(0, 0, 0); border-style: none solid solid none; border-width: 0px 1pt 1pt 0px; mso-background-themecolor: accent1; mso-background-themetint: 102; mso-border-alt: solid white .5pt; mso-border-bottom-themecolor: background1; mso-border-left-alt: solid white .5pt; mso-border-left-themecolor: background1; mso-border-right-themecolor: background1; mso-border-themecolor: background1; mso-border-top-alt: solid white .5pt; mso-border-top-themecolor: background1; padding: 0in 5.4pt; width: 122.3pt;" valign="top" width="163"><div style="line-height: normal; margin: 0in 0in 0pt; mso-yfti-cnfc: 64;">
<span style="font-family: "calibri";">Alt+H+AR</span></div>
</td>
<td style="background: rgb(189, 214, 238); border-color: rgb(0, 0, 0) white white rgb(0, 0, 0); border-style: none solid solid none; border-width: 0px 1pt 1pt 0px; mso-background-themecolor: accent1; mso-background-themetint: 102; mso-border-alt: solid white .5pt; mso-border-bottom-themecolor: background1; mso-border-left-alt: solid white .5pt; mso-border-left-themecolor: background1; mso-border-right-themecolor: background1; mso-border-themecolor: background1; mso-border-top-alt: solid white .5pt; mso-border-top-themecolor: background1; padding: 0in 5.4pt; width: 119.6pt;" valign="top" width="159"><div style="line-height: normal; margin: 0in 0in 0pt; mso-yfti-cnfc: 64;">
<span style="font-family: "calibri";">NA</span></div>
</td>
</tr>
<tr style="mso-yfti-irow: 5;">
<td style="background: rgb(91, 155, 213); border-color: rgb(0, 0, 0) white white; border-image: none; border-style: none solid solid; border-width: 0px 1pt 1pt; mso-background-themecolor: accent1; mso-border-alt: solid white .5pt; mso-border-themecolor: background1; mso-border-top-alt: solid white .5pt; mso-border-top-themecolor: background1; padding: 0in 5.4pt; width: 37.75pt;" valign="top" width="50"><div style="line-height: normal; margin: 0in 0in 0pt; mso-yfti-cnfc: 4;">
<b><span style="color: white; mso-themecolor: background1;"><span style="font-family: "calibri";">6</span></span></b></div>
</td>
<td style="background: rgb(222, 234, 246); border-color: rgb(0, 0, 0) white white rgb(0, 0, 0); border-style: none solid solid none; border-width: 0px 1pt 1pt 0px; mso-background-themecolor: accent1; mso-background-themetint: 51; mso-border-alt: solid white .5pt; mso-border-bottom-themecolor: background1; mso-border-left-alt: solid white .5pt; mso-border-left-themecolor: background1; mso-border-right-themecolor: background1; mso-border-themecolor: background1; mso-border-top-alt: solid white .5pt; mso-border-top-themecolor: background1; padding: 0in 5.4pt; width: 187.85pt;" valign="top" width="250"><div style="line-height: normal; margin: 0in 0in 0pt;">
<span style="font-family: "calibri";">Format paint</span></div>
</td>
<td style="background: rgb(222, 234, 246); border-color: rgb(0, 0, 0) white white rgb(0, 0, 0); border-style: none solid solid none; border-width: 0px 1pt 1pt 0px; mso-background-themecolor: accent1; mso-background-themetint: 51; mso-border-alt: solid white .5pt; mso-border-bottom-themecolor: background1; mso-border-left-alt: solid white .5pt; mso-border-left-themecolor: background1; mso-border-right-themecolor: background1; mso-border-themecolor: background1; mso-border-top-alt: solid white .5pt; mso-border-top-themecolor: background1; padding: 0in 5.4pt; width: 122.3pt;" valign="top" width="163"><div style="line-height: normal; margin: 0in 0in 0pt;">
<span style="font-family: "calibri";">Alt+H+FP</span></div>
</td>
<td style="background: rgb(222, 234, 246); border-color: rgb(0, 0, 0) white white rgb(0, 0, 0); border-style: none solid solid none; border-width: 0px 1pt 1pt 0px; mso-background-themecolor: accent1; mso-background-themetint: 51; mso-border-alt: solid white .5pt; mso-border-bottom-themecolor: background1; mso-border-left-alt: solid white .5pt; mso-border-left-themecolor: background1; mso-border-right-themecolor: background1; mso-border-themecolor: background1; mso-border-top-alt: solid white .5pt; mso-border-top-themecolor: background1; padding: 0in 5.4pt; width: 119.6pt;" valign="top" width="159"><div style="line-height: normal; margin: 0in 0in 0pt;">
<span style="font-family: "calibri";">NA</span></div>
</td>
</tr>
<tr style="mso-yfti-irow: 6;">
<td style="background: rgb(91, 155, 213); border-color: rgb(0, 0, 0) white white; border-image: none; border-style: none solid solid; border-width: 0px 1pt 1pt; mso-background-themecolor: accent1; mso-border-alt: solid white .5pt; mso-border-themecolor: background1; mso-border-top-alt: solid white .5pt; mso-border-top-themecolor: background1; padding: 0in 5.4pt; width: 37.75pt;" valign="top" width="50"><div style="line-height: normal; margin: 0in 0in 0pt; mso-yfti-cnfc: 68;">
<b><span style="color: white; mso-themecolor: background1;"><span style="font-family: "calibri";">7</span></span></b></div>
</td>
<td style="background: rgb(189, 214, 238); border-color: rgb(0, 0, 0) white white rgb(0, 0, 0); border-style: none solid solid none; border-width: 0px 1pt 1pt 0px; mso-background-themecolor: accent1; mso-background-themetint: 102; mso-border-alt: solid white .5pt; mso-border-bottom-themecolor: background1; mso-border-left-alt: solid white .5pt; mso-border-left-themecolor: background1; mso-border-right-themecolor: background1; mso-border-themecolor: background1; mso-border-top-alt: solid white .5pt; mso-border-top-themecolor: background1; padding: 0in 5.4pt; width: 187.85pt;" valign="top" width="250"><div style="line-height: normal; margin: 0in 0in 0pt; mso-yfti-cnfc: 64;">
<span style="font-family: "calibri";">Autosum</span></div>
</td>
<td style="background: rgb(189, 214, 238); border-color: rgb(0, 0, 0) white white rgb(0, 0, 0); border-style: none solid solid none; border-width: 0px 1pt 1pt 0px; mso-background-themecolor: accent1; mso-background-themetint: 102; mso-border-alt: solid white .5pt; mso-border-bottom-themecolor: background1; mso-border-left-alt: solid white .5pt; mso-border-left-themecolor: background1; mso-border-right-themecolor: background1; mso-border-themecolor: background1; mso-border-top-alt: solid white .5pt; mso-border-top-themecolor: background1; padding: 0in 5.4pt; width: 122.3pt;" valign="top" width="163"><div style="line-height: normal; margin: 0in 0in 0pt; mso-yfti-cnfc: 64;">
<span style="font-family: "calibri";">Alt+=</span></div>
</td>
<td style="background: rgb(189, 214, 238); border-color: rgb(0, 0, 0) white white rgb(0, 0, 0); border-style: none solid solid none; border-width: 0px 1pt 1pt 0px; mso-background-themecolor: accent1; mso-background-themetint: 102; mso-border-alt: solid white .5pt; mso-border-bottom-themecolor: background1; mso-border-left-alt: solid white .5pt; mso-border-left-themecolor: background1; mso-border-right-themecolor: background1; mso-border-themecolor: background1; mso-border-top-alt: solid white .5pt; mso-border-top-themecolor: background1; padding: 0in 5.4pt; width: 119.6pt;" valign="top" width="159"><div style="line-height: normal; margin: 0in 0in 0pt; mso-yfti-cnfc: 64;">
<span style="font-family: "calibri";">NA</span></div>
</td>
</tr>
<tr style="mso-yfti-irow: 7;">
<td style="background: rgb(91, 155, 213); border-color: rgb(0, 0, 0) white white; border-image: none; border-style: none solid solid; border-width: 0px 1pt 1pt; mso-background-themecolor: accent1; mso-border-alt: solid white .5pt; mso-border-themecolor: background1; mso-border-top-alt: solid white .5pt; mso-border-top-themecolor: background1; padding: 0in 5.4pt; width: 37.75pt;" valign="top" width="50"><div style="line-height: normal; margin: 0in 0in 0pt; mso-yfti-cnfc: 4;">
<b><span style="color: white; mso-themecolor: background1;"><span style="font-family: "calibri";">8</span></span></b></div>
</td>
<td style="background: rgb(222, 234, 246); border-color: rgb(0, 0, 0) white white rgb(0, 0, 0); border-style: none solid solid none; border-width: 0px 1pt 1pt 0px; mso-background-themecolor: accent1; mso-background-themetint: 51; mso-border-alt: solid white .5pt; mso-border-bottom-themecolor: background1; mso-border-left-alt: solid white .5pt; mso-border-left-themecolor: background1; mso-border-right-themecolor: background1; mso-border-themecolor: background1; mso-border-top-alt: solid white .5pt; mso-border-top-themecolor: background1; padding: 0in 5.4pt; width: 187.85pt;" valign="top" width="250"><div style="line-height: normal; margin: 0in 0in 0pt;">
<span style="font-family: "calibri";">Merge & Center</span></div>
</td>
<td style="background: rgb(222, 234, 246); border-color: rgb(0, 0, 0) white white rgb(0, 0, 0); border-style: none solid solid none; border-width: 0px 1pt 1pt 0px; mso-background-themecolor: accent1; mso-background-themetint: 51; mso-border-alt: solid white .5pt; mso-border-bottom-themecolor: background1; mso-border-left-alt: solid white .5pt; mso-border-left-themecolor: background1; mso-border-right-themecolor: background1; mso-border-themecolor: background1; mso-border-top-alt: solid white .5pt; mso-border-top-themecolor: background1; padding: 0in 5.4pt; width: 122.3pt;" valign="top" width="163"><div style="line-height: normal; margin: 0in 0in 0pt;">
<span style="font-family: "calibri";">Alt+H+M</span></div>
</td>
<td style="background: rgb(222, 234, 246); border-color: rgb(0, 0, 0) white white rgb(0, 0, 0); border-style: none solid solid none; border-width: 0px 1pt 1pt 0px; mso-background-themecolor: accent1; mso-background-themetint: 51; mso-border-alt: solid white .5pt; mso-border-bottom-themecolor: background1; mso-border-left-alt: solid white .5pt; mso-border-left-themecolor: background1; mso-border-right-themecolor: background1; mso-border-themecolor: background1; mso-border-top-alt: solid white .5pt; mso-border-top-themecolor: background1; padding: 0in 5.4pt; width: 119.6pt;" valign="top" width="159"><div style="line-height: normal; margin: 0in 0in 0pt;">
<span style="font-family: "calibri";">NA</span></div>
</td>
</tr>
<tr style="mso-yfti-irow: 8;">
<td style="background: rgb(91, 155, 213); border-color: rgb(0, 0, 0) white white; border-image: none; border-style: none solid solid; border-width: 0px 1pt 1pt; mso-background-themecolor: accent1; mso-border-alt: solid white .5pt; mso-border-themecolor: background1; mso-border-top-alt: solid white .5pt; mso-border-top-themecolor: background1; padding: 0in 5.4pt; width: 37.75pt;" valign="top" width="50"><div style="line-height: normal; margin: 0in 0in 0pt; mso-yfti-cnfc: 68;">
<b><span style="color: white; mso-themecolor: background1;"><span style="font-family: "calibri";">9</span></span></b></div>
</td>
<td style="background: rgb(189, 214, 238); border-color: rgb(0, 0, 0) white white rgb(0, 0, 0); border-style: none solid solid none; border-width: 0px 1pt 1pt 0px; mso-background-themecolor: accent1; mso-background-themetint: 102; mso-border-alt: solid white .5pt; mso-border-bottom-themecolor: background1; mso-border-left-alt: solid white .5pt; mso-border-left-themecolor: background1; mso-border-right-themecolor: background1; mso-border-themecolor: background1; mso-border-top-alt: solid white .5pt; mso-border-top-themecolor: background1; padding: 0in 5.4pt; width: 187.85pt;" valign="top" width="250"><div style="line-height: normal; margin: 0in 0in 0pt; mso-yfti-cnfc: 64;">
<span style="font-family: "calibri";">Insert new worksheet</span></div>
</td>
<td style="background: rgb(189, 214, 238); border-color: rgb(0, 0, 0) white white rgb(0, 0, 0); border-style: none solid solid none; border-width: 0px 1pt 1pt 0px; mso-background-themecolor: accent1; mso-background-themetint: 102; mso-border-alt: solid white .5pt; mso-border-bottom-themecolor: background1; mso-border-left-alt: solid white .5pt; mso-border-left-themecolor: background1; mso-border-right-themecolor: background1; mso-border-themecolor: background1; mso-border-top-alt: solid white .5pt; mso-border-top-themecolor: background1; padding: 0in 5.4pt; width: 122.3pt;" valign="top" width="163"><div style="line-height: normal; margin: 0in 0in 0pt; mso-yfti-cnfc: 64;">
<span style="font-family: "calibri";">Shift+F11</span></div>
</td>
<td style="background: rgb(189, 214, 238); border-color: rgb(0, 0, 0) white white rgb(0, 0, 0); border-style: none solid solid none; border-width: 0px 1pt 1pt 0px; mso-background-themecolor: accent1; mso-background-themetint: 102; mso-border-alt: solid white .5pt; mso-border-bottom-themecolor: background1; mso-border-left-alt: solid white .5pt; mso-border-left-themecolor: background1; mso-border-right-themecolor: background1; mso-border-themecolor: background1; mso-border-top-alt: solid white .5pt; mso-border-top-themecolor: background1; padding: 0in 5.4pt; width: 119.6pt;" valign="top" width="159"><div style="line-height: normal; margin: 0in 0in 0pt; mso-yfti-cnfc: 64;">
<span style="font-family: "calibri";">NA</span></div>
</td>
</tr>
<tr style="mso-yfti-irow: 9;">
<td style="background: rgb(91, 155, 213); border-color: rgb(0, 0, 0) white white; border-image: none; border-style: none solid solid; border-width: 0px 1pt 1pt; mso-background-themecolor: accent1; mso-border-alt: solid white .5pt; mso-border-themecolor: background1; mso-border-top-alt: solid white .5pt; mso-border-top-themecolor: background1; padding: 0in 5.4pt; width: 37.75pt;" valign="top" width="50"><div style="line-height: normal; margin: 0in 0in 0pt; mso-yfti-cnfc: 4;">
<b><span style="color: white; mso-themecolor: background1;"><span style="font-family: "calibri";">10</span></span></b></div>
</td>
<td style="background: rgb(222, 234, 246); border-color: rgb(0, 0, 0) white white rgb(0, 0, 0); border-style: none solid solid none; border-width: 0px 1pt 1pt 0px; mso-background-themecolor: accent1; mso-background-themetint: 51; mso-border-alt: solid white .5pt; mso-border-bottom-themecolor: background1; mso-border-left-alt: solid white .5pt; mso-border-left-themecolor: background1; mso-border-right-themecolor: background1; mso-border-themecolor: background1; mso-border-top-alt: solid white .5pt; mso-border-top-themecolor: background1; padding: 0in 5.4pt; width: 187.85pt;" valign="top" width="250"><div style="line-height: normal; margin: 0in 0in 0pt;">
<span style="font-family: "calibri";">Select entire column</span></div>
</td>
<td style="background: rgb(222, 234, 246); border-color: rgb(0, 0, 0) white white rgb(0, 0, 0); border-style: none solid solid none; border-width: 0px 1pt 1pt 0px; mso-background-themecolor: accent1; mso-background-themetint: 51; mso-border-alt: solid white .5pt; mso-border-bottom-themecolor: background1; mso-border-left-alt: solid white .5pt; mso-border-left-themecolor: background1; mso-border-right-themecolor: background1; mso-border-themecolor: background1; mso-border-top-alt: solid white .5pt; mso-border-top-themecolor: background1; padding: 0in 5.4pt; width: 122.3pt;" valign="top" width="163"><div style="line-height: normal; margin: 0in 0in 0pt;">
<span style="font-family: "calibri";">Ctrl+Space</span></div>
</td>
<td style="background: rgb(222, 234, 246); border-color: rgb(0, 0, 0) white white rgb(0, 0, 0); border-style: none solid solid none; border-width: 0px 1pt 1pt 0px; mso-background-themecolor: accent1; mso-background-themetint: 51; mso-border-alt: solid white .5pt; mso-border-bottom-themecolor: background1; mso-border-left-alt: solid white .5pt; mso-border-left-themecolor: background1; mso-border-right-themecolor: background1; mso-border-themecolor: background1; mso-border-top-alt: solid white .5pt; mso-border-top-themecolor: background1; padding: 0in 5.4pt; width: 119.6pt;" valign="top" width="159"><div style="line-height: normal; margin: 0in 0in 0pt;">
<span style="font-family: "calibri";">NA</span></div>
</td>
</tr>
<tr style="mso-yfti-irow: 10;">
<td style="background: rgb(91, 155, 213); border-color: rgb(0, 0, 0) white white; border-image: none; border-style: none solid solid; border-width: 0px 1pt 1pt; mso-background-themecolor: accent1; mso-border-alt: solid white .5pt; mso-border-themecolor: background1; mso-border-top-alt: solid white .5pt; mso-border-top-themecolor: background1; padding: 0in 5.4pt; width: 37.75pt;" valign="top" width="50"><div style="line-height: normal; margin: 0in 0in 0pt; mso-yfti-cnfc: 68;">
<b><span style="color: white; mso-themecolor: background1;"><span style="font-family: "calibri";">11</span></span></b></div>
</td>
<td style="background: rgb(189, 214, 238); border-color: rgb(0, 0, 0) white white rgb(0, 0, 0); border-style: none solid solid none; border-width: 0px 1pt 1pt 0px; mso-background-themecolor: accent1; mso-background-themetint: 102; mso-border-alt: solid white .5pt; mso-border-bottom-themecolor: background1; mso-border-left-alt: solid white .5pt; mso-border-left-themecolor: background1; mso-border-right-themecolor: background1; mso-border-themecolor: background1; mso-border-top-alt: solid white .5pt; mso-border-top-themecolor: background1; padding: 0in 5.4pt; width: 187.85pt;" valign="top" width="250"><div style="line-height: normal; margin: 0in 0in 0pt; mso-yfti-cnfc: 64;">
<span style="font-family: "calibri";">Select entire Row</span></div>
</td>
<td style="background: rgb(189, 214, 238); border-color: rgb(0, 0, 0) white white rgb(0, 0, 0); border-style: none solid solid none; border-width: 0px 1pt 1pt 0px; mso-background-themecolor: accent1; mso-background-themetint: 102; mso-border-alt: solid white .5pt; mso-border-bottom-themecolor: background1; mso-border-left-alt: solid white .5pt; mso-border-left-themecolor: background1; mso-border-right-themecolor: background1; mso-border-themecolor: background1; mso-border-top-alt: solid white .5pt; mso-border-top-themecolor: background1; padding: 0in 5.4pt; width: 122.3pt;" valign="top" width="163"><div style="line-height: normal; margin: 0in 0in 0pt; mso-yfti-cnfc: 64;">
<span style="font-family: "calibri";">Shift+Space</span></div>
</td>
<td style="background: rgb(189, 214, 238); border-color: rgb(0, 0, 0) white white rgb(0, 0, 0); border-style: none solid solid none; border-width: 0px 1pt 1pt 0px; mso-background-themecolor: accent1; mso-background-themetint: 102; mso-border-alt: solid white .5pt; mso-border-bottom-themecolor: background1; mso-border-left-alt: solid white .5pt; mso-border-left-themecolor: background1; mso-border-right-themecolor: background1; mso-border-themecolor: background1; mso-border-top-alt: solid white .5pt; mso-border-top-themecolor: background1; padding: 0in 5.4pt; width: 119.6pt;" valign="top" width="159"><div style="line-height: normal; margin: 0in 0in 0pt; mso-yfti-cnfc: 64;">
<span style="font-family: "calibri";">NA</span></div>
</td>
</tr>
<tr style="mso-yfti-irow: 11; mso-yfti-lastrow: yes;">
<td style="background: rgb(91, 155, 213); border-color: rgb(0, 0, 0) white white; border-image: none; border-style: none solid solid; border-width: 0px 1pt 1pt; mso-background-themecolor: accent1; mso-border-alt: solid white .5pt; mso-border-themecolor: background1; mso-border-top-alt: solid white .5pt; mso-border-top-themecolor: background1; padding: 0in 5.4pt; width: 37.75pt;" valign="top" width="50"><div style="line-height: normal; margin: 0in 0in 0pt; mso-yfti-cnfc: 4;">
<b><span style="color: white; mso-themecolor: background1;"><span style="font-family: "calibri";">12</span></span></b></div>
</td>
<td style="background: rgb(222, 234, 246); border-color: rgb(0, 0, 0) white white rgb(0, 0, 0); border-style: none solid solid none; border-width: 0px 1pt 1pt 0px; mso-background-themecolor: accent1; mso-background-themetint: 51; mso-border-alt: solid white .5pt; mso-border-bottom-themecolor: background1; mso-border-left-alt: solid white .5pt; mso-border-left-themecolor: background1; mso-border-right-themecolor: background1; mso-border-themecolor: background1; mso-border-top-alt: solid white .5pt; mso-border-top-themecolor: background1; padding: 0in 5.4pt; width: 187.85pt;" valign="top" width="250"><div style="line-height: normal; margin: 0in 0in 0pt;">
<span style="font-family: "calibri";">Navigate between worksheets</span></div>
</td>
<td style="background: rgb(222, 234, 246); border-color: rgb(0, 0, 0) white white rgb(0, 0, 0); border-style: none solid solid none; border-width: 0px 1pt 1pt 0px; mso-background-themecolor: accent1; mso-background-themetint: 51; mso-border-alt: solid white .5pt; mso-border-bottom-themecolor: background1; mso-border-left-alt: solid white .5pt; mso-border-left-themecolor: background1; mso-border-right-themecolor: background1; mso-border-themecolor: background1; mso-border-top-alt: solid white .5pt; mso-border-top-themecolor: background1; padding: 0in 5.4pt; width: 122.3pt;" valign="top" width="163"><div style="line-height: normal; margin: 0in 0in 0pt;">
<span style="font-family: "calibri";">Ctrl+Pgdn or Ctrl+Pgup</span></div>
</td>
<td style="background: rgb(222, 234, 246); border-color: rgb(0, 0, 0) white white rgb(0, 0, 0); border-style: none solid solid none; border-width: 0px 1pt 1pt 0px; mso-background-themecolor: accent1; mso-background-themetint: 51; mso-border-alt: solid white .5pt; mso-border-bottom-themecolor: background1; mso-border-left-alt: solid white .5pt; mso-border-left-themecolor: background1; mso-border-right-themecolor: background1; mso-border-themecolor: background1; mso-border-top-alt: solid white .5pt; mso-border-top-themecolor: background1; padding: 0in 5.4pt; width: 119.6pt;" valign="top" width="159"><div style="line-height: normal; margin: 0in 0in 0pt;">
<span style="font-family: "calibri";">NA</span></div>
</td>
</tr>
</tbody></table>
</div>
Kishorehttp://www.blogger.com/profile/08971014751730405491noreply@blogger.com0tag:blogger.com,1999:blog-329090963001557998.post-51543577030773791082016-03-22T04:07:00.000-07:002016-03-22T04:10:19.251-07:00Convert numbers into words in Excel<div dir="ltr" style="text-align: left;" trbidi="on">
How to you ask excel to convert a numerical value into a text interpretation. This is mostly handy in currency data.<br />
<br />
Here is how:<br />
<br />
Use the below formula and have the reference corrected.<br />
<br />
<b style="background-color: #f3f3f3;">=TEXT(A1,CHOOSE(INT(LOG10(A1)/3)+1,"#","#,","#,,","#,,,","#,,,,")) &CHOOSE(INT(LOG10(A1)/3)+1,""," Thousand"," Million"," Billion"," Trillion")</b><br />
<br />
Well, I hope you have enjoyed this. Let me know if you want to know something more or have any specific question (<span style="background-color: white; color: #555555; font-family: "arial" , sans-serif; font-size: 12.8px; white-space: nowrap;"><b>questions.aweexcel@gmail.com</b>)</span></div>
Kishorehttp://www.blogger.com/profile/08971014751730405491noreply@blogger.com0tag:blogger.com,1999:blog-329090963001557998.post-53839469354863990452016-03-21T04:03:00.001-07:002016-03-21T04:03:35.543-07:00How to filter pivot tables based on a cell value<div dir="ltr" style="text-align: left;" trbidi="on">
Ever wondered how convenient would it be if all the filters of all the pivot tables changed based on a cell value.<br />
<br />
Yes, you can do it using the below VBA code.<br />
<br />
The steps are as follows.<br />
<br />
1. Copy the below VBA code.<br />
<br />
<span style="background-color: #f3f3f3;"><span style="font-size: x-small;"><i>Private Sub Worksheet_Change(ByVal Target As Range)</i></span></span><br />
<span style="background-color: #f3f3f3;"><span style="font-size: x-small;"><i><br /></i></span></span>
<span style="background-color: #f3f3f3;"><span style="font-size: x-small;"><i>Application.ScreenUpdating = False</i></span></span><br />
<span style="background-color: #f3f3f3;"><span style="font-size: x-small;"><i><br /></i></span></span>
<span style="background-color: #f3f3f3;"><span style="font-size: x-small;"><i>Application.ScreenUpdating = False</i></span></span><br />
<span style="background-color: #f3f3f3;"><span style="font-size: x-small;"><i>Dim PT As PivotTable</i></span></span><br />
<span style="background-color: #f3f3f3;"><span style="font-size: x-small;"><i>Dim pi As PivotItem</i></span></span><br />
<span style="background-color: #f3f3f3;"><span style="font-size: x-small;"><i>Dim strField1 As String</i></span></span><br />
<span style="background-color: #f3f3f3;"><span style="font-size: x-small;"><i>Dim strField2 As String</i></span></span><br />
<span style="background-color: #f3f3f3;"><span style="font-size: x-small;"><i><br /></i></span></span>
<span style="background-color: #f3f3f3;"><span style="font-size: x-small;"><i><br /></i></span></span>
<span style="background-color: #f3f3f3;"><span style="font-size: x-small;"><i><b>strField1 = "School"</b></i></span></span><br />
<span style="background-color: #f3f3f3;"><span style="font-size: x-small;"><i><br /></i></span></span>
<span style="background-color: #f3f3f3;"><span style="font-size: x-small;"><i><br /></i></span></span>
<span style="background-color: #f3f3f3;"><span style="font-size: x-small;"><i>On Error Resume Next</i></span></span><br />
<span style="background-color: #f3f3f3;"><span style="font-size: x-small;"><i>Application.EnableEvents = False</i></span></span><br />
<span style="background-color: #f3f3f3;"><span style="font-size: x-small;"><i>Application.ScreenUpdating = False</i></span></span><br />
<span style="background-color: #f3f3f3;"><span style="font-size: x-small;"><i>a</i></span></span><br />
<span style="background-color: #f3f3f3;"><span style="font-size: x-small;"><i>If Target.Address = Range("B2").Address Then</i></span></span><br />
<span style="background-color: #f3f3f3;"><span style="font-size: x-small;"><i><br /></i></span></span>
<span style="background-color: #f3f3f3;"><span style="font-size: x-small;"><i>For Each PT In PivotTables</i></span></span><br />
<span style="background-color: #f3f3f3;"><span style="font-size: x-small;"><i>With PT.PageFields(strField1)</i></span></span><br />
<span style="background-color: #f3f3f3;"><span style="font-size: x-small;"><i>For Each pi In .PivotItems</i></span></span><br />
<span style="background-color: #f3f3f3;"><span style="font-size: x-small;"><i>If pi.Value = Target.Value Then</i></span></span><br />
<span style="background-color: #f3f3f3;"><span style="font-size: x-small;"><i>.CurrentPage = Target.Value</i></span></span><br />
<span style="background-color: #f3f3f3;"><span style="font-size: x-small;"><i>Exit For</i></span></span><br />
<span style="background-color: #f3f3f3;"><span style="font-size: x-small;"><i>Else</i></span></span><br />
<span style="background-color: #f3f3f3;"><span style="font-size: x-small;"><i>.CurrentPage = "(All)"</i></span></span><br />
<span style="background-color: #f3f3f3;"><span style="font-size: x-small;"><i>End If</i></span></span><br />
<span style="background-color: #f3f3f3;"><span style="font-size: x-small;"><i>Next pi</i></span></span><br />
<span style="background-color: #f3f3f3;"><span style="font-size: x-small;"><i>End With</i></span></span><br />
<span style="background-color: #f3f3f3;"><span style="font-size: x-small;"><i>Next PT</i></span></span><br />
<span style="background-color: #f3f3f3;"><span style="font-size: x-small;"><i>End If</i></span></span><br />
<span style="background-color: #f3f3f3;"><span style="font-size: x-small;"><i><br /></i></span></span>
<span style="background-color: #f3f3f3;"><span style="font-size: x-small;"><i>If Target.Address = Range("B2").Address Then</i></span></span><br />
<span style="background-color: #f3f3f3;"><span style="font-size: x-small;"><i><br /></i></span></span>
<span style="background-color: #f3f3f3;"><span style="font-size: x-small;"><i>For Each PT In PivotTables</i></span></span><br />
<span style="background-color: #f3f3f3;"><span style="font-size: x-small;"><i>With PT.PageFields(strField2)</i></span></span><br />
<span style="background-color: #f3f3f3;"><span style="font-size: x-small;"><i>For Each pi In .PivotItems</i></span></span><br />
<span style="background-color: #f3f3f3;"><span style="font-size: x-small;"><i>If pi.Value = Target.Value Then</i></span></span><br />
<span style="background-color: #f3f3f3;"><span style="font-size: x-small;"><i>.CurrentPage = Target.Value</i></span></span><br />
<span style="background-color: #f3f3f3;"><span style="font-size: x-small;"><i>Exit For</i></span></span><br />
<span style="background-color: #f3f3f3;"><span style="font-size: x-small;"><i>Else</i></span></span><br />
<span style="background-color: #f3f3f3;"><span style="font-size: x-small;"><i>.CurrentPage = "(All)"</i></span></span><br />
<span style="background-color: #f3f3f3;"><span style="font-size: x-small;"><i>End If</i></span></span><br />
<span style="background-color: #f3f3f3;"><span style="font-size: x-small;"><i>Next pi</i></span></span><br />
<span style="background-color: #f3f3f3;"><span style="font-size: x-small;"><i>End With</i></span></span><br />
<span style="background-color: #f3f3f3;"><span style="font-size: x-small;"><i>Next PT</i></span></span><br />
<span style="background-color: #f3f3f3;"><span style="font-size: x-small;"><i><br /></i></span></span>
<span style="background-color: #f3f3f3;"><span style="font-size: x-small;"><i>End If</i></span></span><br />
<span style="background-color: #f3f3f3;"><span style="font-size: x-small;"><i><br /></i></span></span>
<span style="background-color: #f3f3f3;"><span style="font-size: x-small;"><i>Application.EnableEvents = True</i></span></span><br />
<span style="background-color: #f3f3f3;"><span style="font-size: x-small;"><i>Application.ScreenUpdating = False</i></span></span><br />
<span style="background-color: #f3f3f3;"><span style="font-size: x-small;"><i><br /></i></span></span>
<span style="background-color: #f3f3f3;"><span style="font-size: x-small;"><i>End Sub</i></span></span><br />
<br />
2. Right click on the excel sheet where you have your pivot tables. Now click on 'View code'<br />
<br />
<div class="separator" style="clear: both; text-align: left;">
<a href="https://2.bp.blogspot.com/-yM_ez3pBfHs/Vu_Uf9lbfpI/AAAAAAAABh8/16v7jneSmLkgrundLYjArbTXIby7PfawA/s1600/pivot%2Brefresh.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" src="https://2.bp.blogspot.com/-yM_ez3pBfHs/Vu_Uf9lbfpI/AAAAAAAABh8/16v7jneSmLkgrundLYjArbTXIby7PfawA/s1600/pivot%2Brefresh.png" /></a></div>
<br />
<br />
3. Now paste the code you have copied and make the following changes as applicable in your case.<br />
<br />
<ul style="text-align: left;">
<li>Change the "Range(XX)" references in the code to the cell where you would input the filter value.</li>
</ul>
<br />
<ul style="text-align: left;">
<li>Change <b style="font-size: small; font-style: italic;"><span style="background-color: #f3f3f3;">strField1 = "XXXXXX"</span></b> to the actual field in the pivot able on which you would apply filter for.</li>
</ul>
<div>
<br /></div>
<div>
4. Save the file as a Macro-enable excel file. Re-open the file and enter any available value for the field you want to filter for in the Range you have specified .</div>
<div>
<br /></div>
<div>
<a href="https://www.dropbox.com/s/p765eak3zj86ab4/pivot%20table%20filter.xlsm?dl=0" target="_blank">Click here for the example excel file</a></div>
<div>
<br /></div>
<div>
Done!! You have done it. :)</div>
<div>
<br /></div>
<div>
Well, I hope you have enjoyed this. Let me know if you want to know something more or have any specific question (<span style="background-color: white; color: #555555; font-family: "arial" , sans-serif; font-size: 12.8px; white-space: nowrap;"><b>questions.aweexcel@gmail.com</b>)</span></div>
<div>
<br /></div>
</div>
Kishorehttp://www.blogger.com/profile/08971014751730405491noreply@blogger.com0tag:blogger.com,1999:blog-329090963001557998.post-73926899439516454022016-03-21T03:22:00.003-07:002016-03-21T04:14:54.326-07:00How to count unique values in Pivot table<div dir="ltr" style="text-align: left;" trbidi="on">
This is a very interesting problem where in many data analysts find pivot table worthless when it comes to counting distinct(or unique) occurrences of a value in a table.<br />
<br />
The solution is as below.<br />
<br />
Example:<br />
<br />
Basically you want to count unique occurrences of each value from the column 'List'<br />
<br />
1. Add an additional column next to it and input an array formula (for indexing) which would basically sort the values in a more meaningful order.<br />
<br />
<div class="separator" style="clear: both; text-align: left;">
<a href="https://3.bp.blogspot.com/-imqgjnPYLnA/Vu_JcJOKeKI/AAAAAAAABhc/0PqQUqdDevIt8gjPy34Wa3Y-d9_cRQazA/s1600/pv1.PNG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="320" src="https://3.bp.blogspot.com/-imqgjnPYLnA/Vu_JcJOKeKI/AAAAAAAABhc/0PqQUqdDevIt8gjPy34Wa3Y-d9_cRQazA/s320/pv1.PNG" width="284" /></a></div>
<div class="separator" style="clear: both; text-align: left;">
<br /></div>
<div class="separator" style="clear: both; text-align: left;">
Formula at cell C3 is: </div>
<div class="separator" style="clear: both; text-align: left;">
<b>=INDEX($B$3:$B$17,MATCH(SMALL(COUNTIF($B$3:$B$17,"<"&$B$3:$B$17),ROW(A1)),COUNTIF($B$3:$B$17,"<"&$B$3:$B$17),0))</b></div>
<div class="separator" style="clear: both; text-align: left;">
<b><br /></b></div>
<div class="separator" style="clear: both; text-align: left;">
Ctrl+Shift+Enter </div>
<div class="separator" style="clear: both; text-align: left;">
(to make it an array formula)</div>
<div class="separator" style="clear: both; text-align: left;">
<br /></div>
the above step is optional and can be skipped if concerned.<br />
<br />
2. Now add another column 'Count' with the following formula. This countif formula would divide the total occurrences to fractions that would some up to '1'.<br />
<br />
<div class="separator" style="clear: both; text-align: left;">
<a href="https://2.bp.blogspot.com/-64k9xe2IRkw/Vu_KEBVIVgI/AAAAAAAABhk/djCsnZ7ptqMwwJdhtpxoKjb-DPn_akdGQ/s1600/pv2.PNG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="282" src="https://2.bp.blogspot.com/-64k9xe2IRkw/Vu_KEBVIVgI/AAAAAAAABhk/djCsnZ7ptqMwwJdhtpxoKjb-DPn_akdGQ/s320/pv2.PNG" width="320" /></a></div>
<div class="separator" style="clear: both; text-align: left;">
<br /></div>
<div class="separator" style="clear: both; text-align: left;">
Formula at cell D3: </div>
<div class="separator" style="clear: both; text-align: left;">
<b>=1/COUNTIF($C$3:$C$17,C3)</b></div>
<div class="separator" style="clear: both; text-align: left;">
<b><br /></b></div>
<div class="separator" style="clear: both; text-align: left;">
3. A pivot of the table (B2:D17) with the column 'Count' in the values field will show the unique count of each values in either 'List' or 'Array formula' column.</div>
<div class="separator" style="clear: both; text-align: left;">
<br /></div>
<div class="separator" style="clear: both; text-align: left;">
<a href="https://3.bp.blogspot.com/-C6gvQgY5snA/Vu_KtHYm1KI/AAAAAAAABhs/KAYoZnWpBywrmaazpxjAuk1nWbZ2Rj8Lg/s1600/pv3.PNG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="108" src="https://3.bp.blogspot.com/-C6gvQgY5snA/Vu_KtHYm1KI/AAAAAAAABhs/KAYoZnWpBywrmaazpxjAuk1nWbZ2Rj8Lg/s320/pv3.PNG" width="320" /></a></div>
<div class="separator" style="clear: both; text-align: left;">
<br /></div>
<div class="separator" style="clear: both; text-align: left;">
Well, I hope you have enjoyed this. Let me know if you want to know something more or have any specific question (<span style="background-color: white; color: #555555; font-family: "arial" , sans-serif; font-size: 12.8px; white-space: nowrap;"><b>questions.aweexcel@gmail.com</b>)</span></div>
<div class="separator" style="clear: both; text-align: left;">
<br /></div>
<div class="separator" style="clear: both; text-align: left;">
Below is the link to the excel file I am talking about. </div>
<div class="separator" style="clear: both; text-align: left;">
<br /></div>
<div class="separator" style="clear: both; text-align: left;">
<a href="https://www.dropbox.com/s/de5ls2oxawpkmpc/count-unique-values-in-a-pivot-table.xlsx?dl=0" target="_blank">Click here for the excel file</a></div>
<div class="separator" style="clear: both; text-align: left;">
<br /></div>
<br />
<br /></div>
Kishorehttp://www.blogger.com/profile/08971014751730405491noreply@blogger.com0tag:blogger.com,1999:blog-329090963001557998.post-54031347140757837492016-03-18T08:34:00.000-07:002016-03-18T08:41:43.399-07:00INDEX MATCH versus VLOOKUP functions - Microsoft Excel<div dir="ltr" style="text-align: left;" trbidi="on">
Most of the folks who use Excel do not know that a alternate combination of functions is available for Vlookup. In this post I would explain:<br />
<i><br /></i>
<i>1. Drawbacks of Vlookup function</i><br />
<i>2. Best alternative for Vlookup function.</i><br />
<i>3. Usage of index and match function.</i><br />
<br />
Vlookup is a function which allows users to lookup for a particular value in a column and provide the related data of that value from the subsequent columns towards right side.<br />
<br />
<b>1. Drawbacks of Vlookup function:</b><br />
<br />
<ul style="text-align: left;">
<li>You cannot lookup for values towards the left of the table from where the lookup value column resides.</li>
<li>Generally Vlookup is not recommended if you are performing the lookup on a huge dataset. As this uses a lot of system's resources rendering the Excel application useless until the lookup calculation is completed.</li>
</ul>
<div>
<b>2. Best alternative for Vlookup function.</b></div>
<div>
<br /></div>
<div>
INDEX and MATCH functions are a good alternative for Vlookup as they overcome the drawbacks mentioned earlier.</div>
<div>
<br /></div>
<div>
<b>3. Usage of index and match function.</b></div>
<div>
<br /></div>
<div>
Index & Match example:</div>
<div>
<br /></div>
<div>
Example 1: Right hand side lookup from a table</div>
<div>
<br /></div>
<div class="separator" style="clear: both; text-align: left;">
<a href="https://3.bp.blogspot.com/-X845QUnkKFk/VuwfEsgiV4I/AAAAAAAABg4/Kr3wImL3JS0XW3x4-dJ31GVFj2bqXQxjA/s1600/Index.PNG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" src="https://3.bp.blogspot.com/-X845QUnkKFk/VuwfEsgiV4I/AAAAAAAABg4/Kr3wImL3JS0XW3x4-dJ31GVFj2bqXQxjA/s1600/Index.PNG" /></a></div>
<div class="separator" style="clear: both; text-align: left;">
<br /></div>
<div class="separator" style="clear: both; text-align: left;">
<br /></div>
<div class="separator" style="clear: both; text-align: left;">
Example 2: Left hand side lookup from a table</div>
<div class="separator" style="clear: both; text-align: left;">
<br /><a href="https://1.bp.blogspot.com/-rYsIXBtj_P4/VuwfEkJtU8I/AAAAAAAABg8/WOTUO0MiLIA8aJYW3CM2nAXRl4kXa2Yww/s1600/Index1.PNG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" src="https://1.bp.blogspot.com/-rYsIXBtj_P4/VuwfEkJtU8I/AAAAAAAABg8/WOTUO0MiLIA8aJYW3CM2nAXRl4kXa2Yww/s1600/Index1.PNG" /></a></div>
<div class="separator" style="clear: both; text-align: left;">
Hopefully you have enjoyed the tip. Let me know if you want to know something more or have any specific question (<span style="background-color: white; color: #555555; font-family: "arial" , sans-serif; font-size: 12.8px; white-space: nowrap;"><b>questions.aweexcel@gmail.com</b>)</span></div>
</div>
Kishorehttp://www.blogger.com/profile/08971014751730405491noreply@blogger.com0tag:blogger.com,1999:blog-329090963001557998.post-44957951916393397782016-03-18T07:13:00.000-07:002016-03-18T07:13:51.769-07:00First Post! - Welcome<div dir="ltr" style="text-align: left;" trbidi="on">
Welcome guys to my new blog focusing on learning aspects in Microsoft Excel.<br />
<br />
I will try and cover wide range of topics on Excel and hopefully this blog will serve as a good resource to you.<br />
<br />
You can send me your queries on anything related to Microsoft Excel at <b><span style="color: red;">questions.aweexcel@gmail.com</span></b></div>
Kishorehttp://www.blogger.com/profile/08971014751730405491noreply@blogger.com0