tag:blogger.com,1999:blog-25839183250032688792024-02-20T23:23:09.074+00:00THE EXCEL PRO COURSE20 years' experience of advanced Excel methodologies in UK Finance, distilled into a unique course for Accountants & AnalystsThe Excel Prohttp://www.blogger.com/profile/11889517726526664089noreply@blogger.comBlogger7125tag:blogger.com,1999:blog-2583918325003268879.post-84837841077690168642020-06-11T14:45:00.000+01:002020-07-26T18:34:30.535+01:00Quick Tip - Removing Error DisplaysWhen we are writing formulae we are often interested in ensuring that the result does not end in an error. But sometimes we also want to remove any errors being displayed in the formula result.<br />
<br />
<strong><em><span style="color: red;">We can do this by combining the IF and ISERROR functions.</span></em></strong><br />
<br />
For example, the following formula will result in a blank if cell B1 has a value of zero in it:<br />
<br />
=IF(ISERROR(A1/B1),"",A1/B1)<br />
<br />
An alternative to the ISERROR function is the ISERR function.<br />
<br />
Similar to the ISERROR function, the ISERR function will remove most error messages (such as #VALUE!, #REF!, #NUM!, #NAME?) but it will still display the #N/A error message.The Excel Prohttp://www.blogger.com/profile/11889517726526664089noreply@blogger.comtag:blogger.com,1999:blog-2583918325003268879.post-15625204976771965302020-05-14T10:04:00.000+01:002020-07-26T18:35:26.763+01:00Quick Tip - Auto Drop-downsSometimes we can't escape having to manually populate cells in a particular column.<br />
<br />
The text auto-complete function sometimes picks-up on what we want half-way through what we're typing, and at other times it just annoys us!<br />
<br />
<strong><em><span style="color: red;">But next time you're preparing a list in a column with repeat entries above it, try the following combination of keys: Alt + Down Arrow</span></em></strong><br />
<br />
It will give you a list of relevant items you've already entered above to choose from, and as a bonus it's alphabetically-ordered too:<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjkmoWagko77e017HDNWiYoV90H9OnJXMuo4JgupG7B-W9xmB-V3RjS8BiQGRDj855JYef4ThJUtDNpaDXjzeMG3wsb4QlpRv0seRVvavQl9MAYcUwZKHvZYMfvfuno00Y9_MX4dRdjd6A/s1600/Book1.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="358" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjkmoWagko77e017HDNWiYoV90H9OnJXMuo4JgupG7B-W9xmB-V3RjS8BiQGRDj855JYef4ThJUtDNpaDXjzeMG3wsb4QlpRv0seRVvavQl9MAYcUwZKHvZYMfvfuno00Y9_MX4dRdjd6A/s640/Book1.png" width="640" /></a></div>
<br />The Excel Prohttp://www.blogger.com/profile/11889517726526664089noreply@blogger.comtag:blogger.com,1999:blog-2583918325003268879.post-42509118792015103132020-04-23T13:53:00.000+01:002020-07-26T18:35:55.723+01:00Quick Tip - Default WorksheetsWhen we create a new workbook in Excel, it always defaults to offering us 3 blank worksheets. Often one of the first things we then do is go in and delete worksheets 2 & 3 !<br />
<br />
<strong><em><span style="color: red;">We can change the default number of worksheets given to us in a new workbook as follows: select File/Options/General and then amend the setting for sheets in new workbook...</span></em></strong><br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhVvW2E4IholY_ehMf-BiqJiutbzhyphenhyphenqj0-liVCqAUOm6EOPEdN4dl95bDW_IrjVFzrxtWKGJvTlEbxQfjfKAKfRPcqXeNg6OOm8Rx8TCFqqvNySsBHPSMyXO8nxBdheVRjTV5nC8xVJGx0/s1600/Book5.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="360" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhVvW2E4IholY_ehMf-BiqJiutbzhyphenhyphenqj0-liVCqAUOm6EOPEdN4dl95bDW_IrjVFzrxtWKGJvTlEbxQfjfKAKfRPcqXeNg6OOm8Rx8TCFqqvNySsBHPSMyXO8nxBdheVRjTV5nC8xVJGx0/s640/Book5.png" width="640" /></a></div>
<br />The Excel Prohttp://www.blogger.com/profile/11889517726526664089noreply@blogger.comtag:blogger.com,1999:blog-2583918325003268879.post-24969178351755993712020-03-10T12:40:00.000+00:002020-07-26T18:36:25.624+01:00Quick Tip - Skipping through WorksheetsAccountants and analysts are often working on files containing many worksheets. And at times it can become a little tiresome to navigate between individual worksheets using only the mouse or mouse pad.<br />
<br />
<em><strong><span style="color: red;">So try using the following keystroke shortcuts instead: Ctrl + PgUp, Ctrl + PgDn</span></strong></em><br />
<br />
Say we were initially looking at Sheet 7 in our workbook.<br />
<br />
Ctrl + PgUp will move you to the next worksheet immediately to the left of the one you are on...<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgCC1zykJm8xJWgV159JqgibKitGxibPwwyjikRXP5Bk08me3x-Z_kLfgRxn4iEamJGWX2qbzZjtJFvwCarNyVLHxR4qZvI42Nd1QoQyHruhkLzS5V-QLhd1uxwsUJEzccHjsubEBifX20/s1600/Book3.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="360" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgCC1zykJm8xJWgV159JqgibKitGxibPwwyjikRXP5Bk08me3x-Z_kLfgRxn4iEamJGWX2qbzZjtJFvwCarNyVLHxR4qZvI42Nd1QoQyHruhkLzS5V-QLhd1uxwsUJEzccHjsubEBifX20/s640/Book3.png" width="640" /></a></div>
<br />
...whilst Ctrl + PgDn will move you to the immediate right:<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEigAKRHMa5zP-281ay7Xv7XxoAYXE-lQgVkjNyMhSvD4gJulw_qQ_3pAhiG60RQudIRSmPMhjbl-2DPxRALI1h6rlMCUmTL9EeL6XXRphsFIPZh3RYaLz-wmHV7RnGf7PlMRcilX6RABVI/s1600/Book4.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="360" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEigAKRHMa5zP-281ay7Xv7XxoAYXE-lQgVkjNyMhSvD4gJulw_qQ_3pAhiG60RQudIRSmPMhjbl-2DPxRALI1h6rlMCUmTL9EeL6XXRphsFIPZh3RYaLz-wmHV7RnGf7PlMRcilX6RABVI/s640/Book4.png" width="640" /></a></div>
The Excel Prohttp://www.blogger.com/profile/11889517726526664089noreply@blogger.comtag:blogger.com,1999:blog-2583918325003268879.post-36377565752982666562020-02-17T10:34:00.000+00:002020-07-26T18:36:48.627+01:00Quick Tip - Format PainterCopying formats with the 'Format Painter' icon is a great time-saver.<br />
<br />
It's that little icon way over on the top left of your screen in Excel 2010 on the Home tab, with the picture of a paintbrush next to it:<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgca1u2gk1tHM_VbGbzbTNUQrbvRc-k1ppvoqfyZ39ZXcx6pnQIX9VU6QnDdmWj58Fkf8jekyCyoprGPW4Wqn3QQXMtp0LXmDuKEMlYTddOxq4mAGs1edQPDy_jE014Ur-friDHzdVhn4k/s1600/Picture1.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="168" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgca1u2gk1tHM_VbGbzbTNUQrbvRc-k1ppvoqfyZ39ZXcx6pnQIX9VU6QnDdmWj58Fkf8jekyCyoprGPW4Wqn3QQXMtp0LXmDuKEMlYTddOxq4mAGs1edQPDy_jE014Ur-friDHzdVhn4k/s400/Picture1.png" width="400" /></a></div>
<div class="separator" style="clear: both; text-align: center;">
<br /></div>
I even like the paintbrush idea - it's sort of saying "take a bit of whatever paint we've used over here and then dab it over there for the same effect". Brilliant.<br />
<br />
<strong><em><span style="color: red;">But did you know that you can repeat-apply your chosen format to any number of cells or areas in your worksheet (or entire workbook in fact) simply by firstly double-clicking the icon?</span></em></strong><br />
<br />
Try it for yourself - but remember to click the icon again or hit 'Escape' to stop the effect, or you could find yourself clicking 'Undo' for some time...The Excel Prohttp://www.blogger.com/profile/11889517726526664089noreply@blogger.comtag:blogger.com,1999:blog-2583918325003268879.post-4608686714310105572020-01-30T09:58:00.000+00:002020-07-26T18:37:07.877+01:00Quick Tip - Number SummariesThere are of course many ways of summing numbers and data in Excel - it is a spreadsheet package after all.<br />
<br />
But sometimes it's useful to have a way of quickly seeing what the total is for a range of cells with numbers in them, or the average, or maybe how many cells are populated in the range:<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEj9wOXCJAYaOcmQDLO4FgdqNKxr0iwarn0LKh0sd3u80dT17puOWmiqkdLCn8QlHHuwZy6rAEF7LGtiNnGRx4BXepaM0LX9uiLGMWMWLhIZY0cGo5s8QE0A3RychRa5aZZQLNn7SFbWbJM/s1600/Book1.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="356" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEj9wOXCJAYaOcmQDLO4FgdqNKxr0iwarn0LKh0sd3u80dT17puOWmiqkdLCn8QlHHuwZy6rAEF7LGtiNnGRx4BXepaM0LX9uiLGMWMWLhIZY0cGo5s8QE0A3RychRa5aZZQLNn7SFbWbJM/s640/Book1.png" width="640" /></a></div>
<br />
If there's nothing else on this worksheet, then we can find all the information we need in just one click. <strong><em><span style="color: red;">Click on the top left hand area of the grid</span></em></strong> (between the column A header and row number 1):<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEi-nDT7ZSDn-HyDeeAz0HKaVaGVevmp5g9fwBmvnw3CJZkD1Drh63BsIIKxQJIQy-Mvx_YATEF6oaRf3skj0xdPyPnZzCf4C8kwWJrRCMfm2Py59HUDw9fJgruQVwXhi3DtO1ZIOoNSB-s/s1600/Book1.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="358" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEi-nDT7ZSDn-HyDeeAz0HKaVaGVevmp5g9fwBmvnw3CJZkD1Drh63BsIIKxQJIQy-Mvx_YATEF6oaRf3skj0xdPyPnZzCf4C8kwWJrRCMfm2Py59HUDw9fJgruQVwXhi3DtO1ZIOoNSB-s/s640/Book1.png" width="640" /></a></div>
<br />
Have a look at the Status Bar down the bottom right of the screen - it shows you all the data you were after, and not an =SUM or Autosum or other formula in sight. Simple!<br />
<br />The Excel Prohttp://www.blogger.com/profile/11889517726526664089noreply@blogger.comtag:blogger.com,1999:blog-2583918325003268879.post-44347758811208039432019-12-19T17:30:00.000+00:002020-07-26T18:37:54.896+01:00Quick Tip - Hiding & Unhiding SheetsYou can easily hide and unhide any chosen worksheet(s) in a workbook in Excel.<br />
<br />
<strong><em><span style="color: red;">Simply right-click your chosen worksheet tab and select 'Hide'</span></em></strong>:<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgSefAOWwBcPbycmzPBeUqQBB5Sl618p0t89o5P1LwqPiCxgjsMmldASZ1ChsxhoqgnhbeydkZcaDjTNaOaAXCP08AhDMEUVDsrxMg4xKZaD_-Z-xMF1-lrN4FwJzktgmop3gkfIXWkJNo/s1600/Book1.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="358" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgSefAOWwBcPbycmzPBeUqQBB5Sl618p0t89o5P1LwqPiCxgjsMmldASZ1ChsxhoqgnhbeydkZcaDjTNaOaAXCP08AhDMEUVDsrxMg4xKZaD_-Z-xMF1-lrN4FwJzktgmop3gkfIXWkJNo/s640/Book1.png" width="640" /></a></div>
<br />
To unhide, again right-click on any worksheet tab and then select the sheet you want to unhide:<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEi45zo6PZpARMDFYzsozJ7mqLs0nc9cpKwHYlNmrKJvKDvdMLfD4v2nc0wvy-7ZORWFyBVoGWUPeuoXf5aW4E3zBc2OYqaJoMspAGbiUCzXPT5cGcJXxeKkBegtO6Sw9IEQt_y3s3a30j0/s1600/Book1.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="358" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEi45zo6PZpARMDFYzsozJ7mqLs0nc9cpKwHYlNmrKJvKDvdMLfD4v2nc0wvy-7ZORWFyBVoGWUPeuoXf5aW4E3zBc2OYqaJoMspAGbiUCzXPT5cGcJXxeKkBegtO6Sw9IEQt_y3s3a30j0/s640/Book1.png" width="640" /></a></div>
The Excel Prohttp://www.blogger.com/profile/11889517726526664089noreply@blogger.com