Overview It was back in 1985 that Microsoft launched their first spreadsheet program, introducing the Excel brand name to the world. And in the years since then, few could deny that Excel has lived handsomely up to its name, with many hundreds of millions of satisfied users around the globe. But although Excel is undoubtedly one of the most powerful end user tools in the Microsoft Office Suite, how many of this huge army of users can honestly say that there’s nothing more that they can learn? The truth is that all too often, business users are not aware of many of the time-saving functions and shortcuts within Excel, or how simply a host of productivity-boosting tasks can be performed. All designed to help you achieve more. This invaluable Self help Business Intelligence book or Self help BI for short, not only covers the basics, but also takes the user on a journey into the powerful new world of PowerPivot, and its potential to model data, turn it into information and visualize the results in new ways with Slicers and Enhanced graphing. PowerPivot allows users to work with massive amounts of data from many sources, combine these information sources and build models to visualize, right on your desktop! In short, it simultaneously demystifies perceived complexities and empowers all Excel users to stretch their own capabilities – to achieve more. iSPartners is eminently qualified to be the content provider for all these handy tips and tricks, being a company focused on delivering solutions around data and information, driving business change and impacting how our customers run their business. We are Gold Certified and multiple award winning Microsoft partners, including being rated by Microsoft as their top business intelligence partner in South Africa. We are passionate about what Excel 2010 can do for our clients and we hope you enjoy this book as much as we enjoyed compiling the content, playing with the functions and changing the way we work.
Go ahead – Achieve More! For more information or to join the Self help BI community – please visit www.ispartners.co.za For more copies of this book go to www.moneyweb.co.za or www.ispartners.co.za It’s a fun find. With great dividends. Like being able to spend more time amusing the next generation. Alec Hogg: Moneyweb
ISBN 978-0-620-48743-6
> +27 11 463 8155 > www.ispartners.co.za > info@ispartners.co.za
9 780620 487436 Follow us on Twitter and Linkedin
Self Help BI Book
SELF HELP BI
| www.ispartners.co.za <<
ACHIEVING MORE
More accolades for iSPartners at the 2010 Microsoft Partner Network Awards. > +27 11 463 8155 > www.ispartners.co.za > info@ispartners.co.za
Introduction to this project It was back in 1985 that Microsoft launched their first spreadsheet program, introducing the Excel brand name to the world. And in the twenty five years since then, few could deny that Excel has lived handsomely up to its name, with many hundreds of millions of satisfied users around the globe. But although Excel is undoubtedly one of the most powerful end user tools in the Microsoft Office Suite, how many of this huge army of users can honestly say that there’s nothing more that they can learn? The truth is that all too often, business users are not aware of many of the time-saving functions and shortcuts within Excel, or how simply a host of productivity-boosting tasks can be performed. All designed to help you achieve more. The book you are holding in your hands is targeted squarely at that challenge, and covers a range of Excel tasks, from the simple to the complex. Some of these have been around since the earliest Excel versions, others appeared only more recently, while many functions are completely new to Excel 2010. In this latest edition, the new Excel 2010 boasts extensive customization and flexibility, allowing greater access to functions that were typically hidden, and you can even create your own custom Ribbon for your most commonly used functions. It’s as if the interface had been especially designed for your own particular business needs and your constant ambition to achieve more. This invaluable self-help Business Intelligence book not only covers the basics, but also takes the user on a journey into the powerful new world of PowerPivot, and its potential to model data, turn it into information and visualize the results in new ways with Slicers and Enhanced graphing. PowerPivot allows users to work with massive amounts of data from many sources, combine these information sources and build models to visualize, right on your desktop! In short, it simultaneously demystifies perceived complexities and empowers all Excel users to stretch their own capabilities – to achieve more. Knowing it would be impossible to show it all, there is much to discover, even more to leverage when combined with other Microsoft technologies like SQL Server 2008 R2 and SharePoint 2010. With Excel 2010, you have a platform for building smart applications, solving issues, the ability to visualize information and share this with others. iSPartners is eminently qualified to be the content provider for all these handy tips and tricks, being a company focused on delivering solutions around data and information, driving business change and impacting how our customers run their business. We are Gold Certified and multiple award winning Microsoft partners, including being rated by Microsoft as their top business intelligence partner in South Africa. We are passionate about what Excel 2010 can do for our clients and we hope you enjoy this book as much as we enjoyed compiling the content, playing with the functions and changing the way we work.
Go ahead – Achieve More! Disclaimer The information within this publication remains the property of Information Systems Partners (Pty) Ltd. The associated Self help BI tools were written for and on behalf of Information Systems Partners (Pty) Ltd. The Publisher shall not be liable for any damage, loss or liability of whatsoever nature arising from the use or inability to use the services or content provided from and through this book. IP Protection statement All rights reserved. Illegal reproductions of this book or part if this book without the written consent of the publisher is a criminal offence. No part of this publication may be reproduced, republished, reused, photocopied, reprinted without the prior written consent of the publisher - Information Systems Partners (Pty) Ltd. Should you wish to use part of the publication or to reproduce this book please contact the publisher. Copyright reserved 2010 iSPartners Tel: +27 11 463 8155 Email: info@ispartners.co.za Web: www.ispartners.co.za
SELF HELP BI
| www.ispartners.co.za <<
Does the world of investments puzzle you? za . o c eb. w y e n o m
SAfm RSG LotusFM
CitiBusine ss in Citizen
Moneyweb puts all the pieces together so that you can make an informed decision.
www.moneyweb.co.za
South Africaâ&#x20AC;&#x2122;s leading source for independent investment information
SAfm 6pm (104-107fm)
RSG 5pm (100-104fm)
Business pages in the Citizen
LotusFM 5:45pm (87.7-106.8fm)
Foreword Over the years, my children have enjoyed hours of mirth at my expense as each new victory on the technology front was followed by loud claims of their father finally becoming a “power user” – even though they wonder when he’ll ever leave computer kindergarten. But after having worked through the Self help BI Book, I’m sure even they will cock an eyebrow at the new David Copperfield of the Excel Spreadsheet. My graduation from spreadsheet on my Apple IIE to Lotus 123 and then Excel came many years ago. But I’ve always had the feeling that my knowledge hardly scratched the surface; feeling that there were hundreds of hours to be saved if I could one day find the time to properly study the manual. Surely there was a better way of capturing data? Why did I need to write out the month and year every time a new spreadsheet was created? And there must be an easier way than cutting and pasting data from different spreadsheets? Of course there is. Much more, too, as you’ll discover in your journey through this book. Like so many others who interpret numbers for a living, much of my working day is spent with at least one open Excel spreadsheet on my laptop. Being pre-occupied with the daily tasks, though, means less investment in learning how to work smarter. This little book breaks that cycle. My approach has been to work through each tip systematically. Focusing on only one a day and practising it enough to create a permanent (I hope!) imprint on the brain. But as there’s no exam at the end of the exercise, we can work at our own pace. For me, it’s a treasure chest of time-saving suggestions. Excel is today such a sophisticated product that I’ll bet even the true “power users” can learn from what’s between these covers. It’s a fun find. With great dividends. Like being able to spend more time amusing the next generation. Enjoy! Alec Hogg Moneyweb Holdings October 2010
SELF HELP BI
| www.ispartners.co.za <<
Contents Self Help Excel 1. 2. 3. 4. 5. 6. 7. 8. 9. 10. 11. 12. 13. 14. 15. 16. 17. 18. 19. 20. 21. 22. 23. 24. 25. 26. 27. 28. 29. 30. 31. 32. 33. 34. 35. 36. 37. 38. 39. 40. 41. 42. 43. 44. 45.
How do I create and customise my own Ribbon Tab? Most useful Short Cut Keys Function Keys How do I use the Fill Handle? How do I use the Auto Fill Options button? How do I wrap text? How do I quickly adjust column width or row heights to fit the data? How do I force a line break in a cell? How do I set a default shape? How do I copy data so that it remains linked to the source data (paste link)? How do I keep track of whatâ&#x20AC;&#x2122;s happening to my data when I make changes (paste as linked picture)? How do I paste Transpose? How do I access data in a cell? How do I copy and paste data from another source so that it has the default formatting of my workbook? How do I navigate my workbook (Hyperlinks)? How do I only select what is visible? How do I quickly create a named range? How can I quickly navigate using named ranges? How do I use a named range in a formula? How do I create a custom list? How do I insert the current date? How do I calculate the week number of a particular date? How do I calculate the number of Working Days? How do you ensure that a cell always has the current date? How do I restrict the data that can be entered in a cell to a date period? How do I restrict the data that can be entered in a cell to a list (Data Validation)? How do I prevent data from being entered into a cell until requisite data has been added? How do I prevent data from being entered into a cell unless other data meets certain requirements? How to pick from a drop-down list? How do I convert a text list of data into columns? How do I highlight duplicate data? How do I delete duplicate data? How do I consolidate my data? How do I turn off the grid lines? How do I Maximise workspace? How do I freeze headings so I can see them when I scroll down (Freeze Panes)? How can I see more than one workbook at a time (Arrange)? How can I compare the content of two workbooks (Side by Side and Synchronous scrolling)? How can I see different parts of one worksheet at the same time (split screen)? How can I see two worksheets from one workbook at the same time? How do I save the current screen layout (Save workspace)? How do I save a certain view of the data that I use often (Custom Views)? How can I quickly see the sum of a set of data without adding a Sum function to the worksheet? How do I group my data? How do I add totals and subtotals to my data without having to manually insert Sum functions?
>> SELF HELP BI
| www.ispartners.co.za
1-2 2-3 3 3 4 5 6 6 6 7 8 9 9 9 9-10 11 11 12 12 12-14 14 14 14 15 15 15 15 16 16 16 17 18 19 19 20 20 20 20 20 21 21 21 22 22 23
46. 47. 48. 49. 50. 51. 52. 53. 54. 55. 56. 57. 58. 59. 60. 61.
How do I automatically highlight data that is greater than or equal to a value (Conditional Formatting)? How do I automatically highlight the top 3 data records in a data set (Conditional Formatting)? How do I automatically add graphical indicators to a data set (Conditional Formatting)? How do I sort by the Icon Set? How do I lock cells? How do I protect my worksheet so that certain data or calculations cannot be changed? How do I use logical functions IF, AND, OR? How do I sort by Month and not alphabetically? How do I sort by more than one field? How do I compare different scenarios of data (What if)? How can I quickly record macros? How do I create a macro and run a macro? How do I assign a macro to a button? How do I assign a macro to a ribbon? How do I create a Pivot Table? How do I use Slicers in a Pivot Table?
24 25 26 27 28 29 29-30 31 32 33-34 35 35 36 37 38-39 40
Self Help Power Pivot 1. 2. 3. 4. 5. 6. 7. 8. 9. 10. 11. 12. 13. 14. 15. 16. 17. 18. 19. 20. 21.
How do I obtain an aggregated measure in a related table? How do I hide columns from my PowerPivot PivotTable? How do I determine the distinct count of a specific entity? How can I determine when last my PowerPivot data was refreshed? How can I create a subset of items in my PowerPivot PivotTable? How can I sort the month names in my Calendar dimension in my pivot table so that they appear in the correct order and not alphabetically? I have written a SQL query to import details for one of my PowerPivot tables. How can I modify the original query? How can I determine the moving annual total (cumulated value of the last 12 months) for a particular measure? My database does not contain a date / time table. What is the easiest way to include one in PowerPivot? Where can I find more information on the amount of space my PowerPivot model is occupying? How do I add a Pivot Table / Chart to an existing sheet How do I calculate a Percentage Contribution? How do I improve the Visual Impact of my Charts 1 – Secondary Axis? How do I improve the Visual Impact of my Charts 2 – Smooth Lines? How do I improve the Visual Impact of my Charts 3 – Borders and Fill? How do I improve the Visual Impact of my Charts 4 – Mixed Chart Types? Where did my Field List go? How do I display a related column in a table? How do I reference a particular Dimension member value? How do I compare a measure to a particular Dimension member? How can I get the transactions that occurred on the First / Last day of any Time Period?
SELF HELP BI
1 1 1-2 2 3 3 3 4 4 5 5-6 6-7 7-8 8 9 9-10 10-11 11 11-12 12 13-14
| www.ispartners.co.za <<
>> EXCEL TIPS AND TRICKS
1. How do I create and customise my own Ribbon Tab? In Excel 2003 and earlier versions, you had the ability create or customise a toolbar. With the introduction of the MS Ribbon in 2007 you were no longer able to do this. With Excel 2010, you can create and customise your own toolbar, including all your most commonly used functions. • Right Click anywhere on the MS Ribbon • Select Customise the Ribbon from the drop down list
• Select New Tab to create a new ribbon tab • Select the New Tab in the list and rename (either Right Click and select rename or select the rename button below) e.g. My Ribbon
• Select New Group in the list and rename e.g. Formatting (Use this group for all your formatting functions)
1
>> SELF HELP BI
| www.ispartners.co.za
>> EXCEL TIPS AND TRICKS
• Select the command from the list of commands on the left that you want to Add, and click Add. You can display additional options by using the selection arrow on the Choose commands from: dialogue box
• • •
Keep similar types of commands in the same group and add new groups as required You can export your customisation and distribute to other team members by clicking Import/Export Click OK to exit customisation
Example:
2. Most useful Short Cut Keys File Functions • Save • Print • New workbook Editing • Copy • Paste • Paste Special • Repeat Last Command • Cut • Undo • Find/Replace • Go To • Fill Down • Fill Right Formatting • Bold • Italics • Underline Other • Select table
Ctrl + S Ctrl + P Ctrl + N
Ctrl + C Ctrl + V CTRL+ALT+V Ctrl + Y or F4 Ctrl + X Ctrl + Z Ctrl + F Ctrl + G Ctrl + D Ctrl + R
Ctrl + B Ctrl + I Ctrl + U
CTRL+SHIFT+* SELF HELP BI
| www.ispartners.co.za <<
2
>> EXCEL TIPS AND TRICKS
Other • Select table • Select worksheet
CTRL+SHIFT+* Ctrl + A
Or click here
• Enter Current Date • Hide Rows • Hide Columns
CTRL+; (semi-colon) Ctrl + 9 Ctrl + 0
For a complete list of shortcut keys, go to Excel Help and type “Shortcut keys” in the search box
3. Function Keys F1 = Help F2 = Edit Cell content F3 = Display Named Ranges for pasting F4 = Repeats previous command F5 = Go To
4. How do I use the Fill Handle?
The fill handle is the small solid black box in the lower right corner of the selected cell. The fill handle enables rapid autofill of data in a series. • Select the cell that contains the data • Move the cursor over the bottom right hand corner of the selected cell until the cursor becomes a solid black cross
• Press down the left button on the mouse and drag down or across to the region you want to fill
3
>> SELF HELP BI
| www.ispartners.co.za
>> EXCEL TIPS AND TRICKS
5. How do I use the Auto Fill Options button? The Fill Handle offers a variety of fill options. Once you have used the default auto fill function, an Auto Fill Options button appears just below the fill handle. Use this to adjust the auto fill. • • • •
Auto fill as normal Click the Auto Fill Options button Select the type of fill you would like to use As soon as you move away from the selected range, the Auto Fill Options button will disappear
SELF HELP BI
| www.ispartners.co.za <<
4
>> EXCEL TIPS AND TRICKS
6. How do I wrap text?
When data exceeds the width of a cell, you may wish to wrap the text, so that the text that exceeds the width flows onto a new line. • Select the cells, column or row you wish the wrapping to apply to • Select Wrap Text on the Home tab of the ribbon
• Result:
5
>> SELF HELP BI
| www.ispartners.co.za
>> EXCEL TIPS AND TRICKS
7. How do I quickly adjust column width or row heights to fit the data? • Double click the column or row edge in the Excel row or column header (the cursor turns into a dark cross with arrows on the horizontal bar) Double clicke here to fit columns Double clicke here to fit rows • You can adjust a range of columns and rows by selecting the range of columns and rows and double clicking on the border of any of the columns and rows in the range Double clicke any of these
8. How do I force a line break in a cell? To force a line break in a cell, Press Alt + Enter Line break
9. How do I set a default shape? • Select Shapes On the Insert tab of the Home Ribbon
• • • • •
Select the shape Draw the shape Edit the shape Right Click on the shape and select Set as Default Shape Whenever you select this shape in future, this default shape you have just created will be inserted
SELF HELP BI
| www.ispartners.co.za <<
6
>> EXCEL TIPS AND TRICKS
10. How do I copy data so that it remains linked to the source data (paste link)? Paste link allows you to paste data while retaining a link to the source data. When the original data is updated, the copied data is also updated. • Select the data • Copy data • Select the top left position of where you want to copy • Right Click
• Select the paste link icon
7
>> SELF HELP BI
| www.ispartners.co.za
>> EXCEL TIPS AND TRICKS
11. How do I keep track of what’s happening to my data when I make changes (paste as linked picture)? This is useful if you are changing data on one sheet that affects the results on another sheet, preventing you from paging backwards and forwards to see the changes. • Select the data range and/or graph you want to keep track of • Copy • Go to the place you want to be able to see it • Select the Paste drop down arrow and select the Linked Picture icon (or Right Click, select paint special and select the Linked Picture icon)
• The data and/or graph is pasted as a picture that changes as the data changes. While the pasted picture cannot be accessed or edited, it will change as the source changes. It can be formatted like any picture.
Original Data Pasted linked picture (formatted)
Occasional Excel formatting errors
Note: You cannot use paste picture link if you have formatted your data as a Table using the Style. However you can paste the picture link and then apply the Table Style formatting. The pasted picture will update to the table style. SELF HELP BI
| www.ispartners.co.za <<
8
>> EXCEL TIPS AND TRICKS
12. How do I paste Transpose? • • • •
Select the data Copy the data Select the new copy position Right Click and select the copy transpose icon
13. How do I access data in a cell? There are three ways to access the content of a cell • Double click • Select the cell and press F2 • Select the cell and access the formula bar The formula bar can edit cell content from there
14. How do I copy and paste data from another source so that it has the default formatting of my workbook? If you are copying text from another source such as Word • Copy the text • Select the cell in Excel • Press F2 • Paste
15. How do I navigate my workbook (Hyperlinks)? Hyperlinks can be used in Excel to help you navigate your workbook. Hyperlink takes you to a set specified place. You can create an index page that takes you to different places in your workbook. Click January and it navigates you to the January sales figures, click the green arrow and it navigates you back to the Sales Figure Index.
9
>> SELF HELP BI
| www.ispartners.co.za
>> EXCEL TIPS AND TRICKS
Cell • Select the cell you will hyperlink from (Note: this cannot be a cell where you need to enter data because as soon as you click the cell you will activate the hyperlink) • Right Click and select Hyperlink
• Select Place in this document • Select the Cell Reference on the cell of the sheet you are navigating to (Note: Unfortunately this cannot be browsed but you will need the cell reference) • Select the Place in the workbook (this can also refer to a named range) • Click OK
2 1
3
When you click this cell it will take you to the place specified Object • • • • •
Insert a shape (On the Insert tab of the Ribbon) and format Right Click on the shape Select Hyperlink Complete the form as above Click OK
When this object is selected you will be directed to the place specified
SELF HELP BI
| www.ispartners.co.za <<
10
>> EXCEL TIPS AND TRICKS
16. How do I only select what is visible? This is useful if you have hidden data that you want to copy or if you need to send someone only the visible unhidden data. • Hide the columns or rows • Select the data • Press Ctrl + G (or Select Find & Select on the Home Tab on the ribbon and select Go to Special)
• Click Special • Select Visible Cells only and click OK
• Copy and paste as normal
17. How do I quickly create a named range? • Select the range or cell • Type in the new name in the name box on the formula bar (Note: the name must be unique and contain no spaces)
Name Box
• Press Enter (Note: this process is very important as it saves the name) 11
>> SELF HELP BI
| www.ispartners.co.za
>> EXCEL TIPS AND TRICKS
18. How can I quickly navigate using named ranges? • Select the down arrow on the Name Box on the formula bar • Select the range you want to go to
19. How do I use a named range in a formula? This is useful when you need to change a figure that is used in a number of calculations over several worksheets in a workbook. It also helps in understanding complex calculations. • Name the cell/range • Type in the formula, when you get to the point where you need to use the figure, either type in the name or press F3 to list all the named ranges and select the required name
• Complete the formula and press enter
• Click OK
20. How do I create a custom list? This is useful when you have a list of data that you regularly use in a variety of workbooks, such as a staff list or a cost code list. You can add this as a custom list in Excel which is then used to auto fill ranges. • Create the list on a blank worksheet or open a workbook that contains the list
• Select the list excluding headings • Right Click anywhere on the Ribbon
SELF HELP BI
| www.ispartners.co.za <<
12
>> EXCEL TIPS AND TRICKS
• In the Options dialogue select Advanced
• Scroll down and click the Edit Custom List button • Click on Import to import your list (it should be listed in the Import list from cells box):
13
>> SELF HELP BI
| www.ispartners.co.za
>> EXCEL TIPS AND TRICKS
• The list appears in the Custom lists and can be edited in List entries
• Click OK • When you need to use the list, type in the first list entry then autofill. The list will be used to autofill.
21. How do I insert the current date? • Hold down shift + ; (semi-colon)
22. How do I calculate the week number of a particular date? • In the cell, type =WEEKNUM(Date)
Note: The cell must be have a date format
23. How do I calculate the number of Working Days? • In the cell type =Networkdays(Start Date,End Date, Holidays)
Note: The cell must have a date format.
SELF HELP BI
| www.ispartners.co.za <<
14
>> EXCEL TIPS AND TRICKS
24. How do you ensure that a cell always has the current date? • Type =TODAY() in the cell and press enter • Format the cell to have date only
25. How do I restrict the data that can be entered in a cell to a date period? • Select the cell(s) where you want to restrict the data • Click Data Validation on the Data tab of the Ribbon to open the Data Validation box
• • • •
Select Date in the Allow drop down box In the Data drop down box you can select greater than, less than etc. Default is between Enter the relevant data restrictions in the date boxes Select OK
26. How do I restrict the data that can be entered in a cell to a list (Data Validation)? • • • • • • • • •
Create a list of items to include in the drop down Name the list Select the cell(s) to add the drop down list On the Data tab of the Ribbon, click Data Validation to open the Data Validation box In the allow drop down select list Click in the Source box Press F3 Select the Name of the list Select OK
Note: You can also create Input messages and Error alerts
27. How do I prevent data from being entered into a cell until requisite data has been added? • • • •
Select the cell(s) where you want to restrict the data On the Data tab of the Ribbon, click Data Validation to open the Data Validation box In the allow drop down select custom In the Formula box type in the restriction e.g. if A2, B2 and C2 all have to be greater that 0 to enter data then type =And(A2>0,B2>0,C2>0), make sure Ignore blank is not ticked • Click OK 15
>> SELF HELP BI
| www.ispartners.co.za
>> EXCEL TIPS AND TRICKS
28. How do I prevent data from being entered into a cell unless other data meets certain requirements? • • • •
Select the cell(s) where you want to restrict the data Click Data Validation on the Data tab of the Ribbon, to open the Data Validation box Select Custom in the Allow drop down In the Formula box type in the restriction e.g. if A2 must be greater than 10 000, B2 must be equal to 2 and C2 less than R20 000 or greater than R100 000, then type =And(A2>10 000,B2=2,OR(C2<20 000,C2>100 000). (Note: ensure that Ignore blank is not ticked) • Select OK
29. How to pick from a drop-down list? The drop-down list is generated from the text in the existing column that you are adding data to • Select the cell where you want to enter the data (column already has data and the information you want to enter is already in the column) • Right Click • Select Pick from dropdown list • A list now appears listing all unique items in that column. Select the data you want
30. How do I convert a text list of data into columns? • • • • • • • • •
Copy the list into an Excel cell Select the cell On the Data Tab of the ribbon select Text to Columns Select how the list is separated e.g. comma, space, tab Click Next Select the Delimiter Click Next Select ata format Click Finish
SELF HELP BI
| www.ispartners.co.za <<
16
>> EXCEL TIPS AND TRICKS
31. How do I highlight duplicate data? • Select the range you want to check • On the Home Tab select Conditional Formatting
• • • •
17
Select Highlight Cells Rules Select Duplicate Values Select your required formatting Click OK
>> SELF HELP BI
| www.ispartners.co.za
>> EXCEL TIPS AND TRICKS
32. How do I delete duplicate data? • Select the entire data list
• Select Remove Duplicates on the Data tab of the ribbon • In the Remove Duplicates dialogue select the column that has the potential duplicates
•
Click OK. You will be notified as to the number of duplicates that will be deleted.
SELF HELP BI
| www.ispartners.co.za <<
18
>> EXCEL TIPS AND TRICKS
33. How do I consolidate my data? If you have data from several sources, you can consolidate the data using the consolidate data function. The data can be in several worksheets or workbooks. • Go to the worksheet where you want the consolidated data • Select Consolidate on the Data Tab of the ribbon
Select how the data will be consolodated Browse and select the first set of data Select if you want headers from rows and/or columns
Select if you want the data to be linked and listed in the groups
Headings generated from source data
Row groupings generated from source data
34. How do I turn off the grid lines? • Unselect Gridlines on the View Tab of the Ribbon
19
>> SELF HELP BI
| www.ispartners.co.za
Consolodated Figure
>> EXCEL TIPS AND TRICKS
35. How do I Maximise workspace? • Turn off the Formula bar by unselecting on the View Tab of the Ribbon • Turn off the ABC123 Headings by unselecting Headings on the View Tab • Minimise the Ribbon: Right Clicking on the ribbon and select Minimise the Ribbon
36. How do I freeze headings so I can see them when I scroll down (Freeze Panes)? • Select the cell one below and to the right of the position you would like to freeze your headings • On the View tab of the Ribbon, select Freeze Panes
37. How can I see more than one workbook at a time (Arrange)? • • • •
Open two workbooks Select Arrange All on the View Tab of the Ribbon Select how you want the windows arranged Click OK
38. How can I compare the content of two workbooks (Side by Side and Synchronous scrolling)? • Open two workbooks and make sure you are on the correct worksheets • Select Side by Side on the View tab of the ribbon. (Note: by default Synchronous scrolling will turn on)
39. How can I see different parts of one worksheet at the same time (split screen)? • Select a cell in the worksheet (if you select in row 1 a vertical split is created, if you select in column A, a horizontal split is created, if you select any other a four window split is created) • Select Split on the View tab of the Ribbon • You can resize the split by moving the cursor over the grey divide line until the resize icon appears, drag to the required size
Reposition by dragging grey line • You can scroll to different positions in the worksheet in each window
SELF HELP BI
| www.ispartners.co.za <<
20
>> EXCEL TIPS AND TRICKS
40. How can I see two worksheets from one workbook at the same time? • Select New Window on the View tab of the Ribbon (Note: this opens a copy of the current workbook that is linked to the original i.e. changing data on one, updates the other) • Select Arrange All on the View tab • Select Vertically • Click OK • In one of the two windows select the alternate worksheet
41. How do I save the current screen layout (Save workspace)? • Once you have arranged the workbook, select Save Workspace on the View tab of the ribbon • Enter a filename and select Save • This new file is linked to the original but contains the screen layout, Any changes made will be saved in the original file as well
42. How do I save a certain view of the data that I use often (Custom Views)? • Filter the data and/or hidden rows/columns required • Select Custom Views on the View tab of the ribbon • Select Add
• Enter view name and click OK
• Repeat these steps to create alternative views • Select Custom Views on the View tab of the ribbon • Select the required View and select Show
(Note: If any table in the workbook is formatted as an Excel table, you will not be able to use Custom Views) 21
>> SELF HELP BI
| www.ispartners.co.za
>> EXCEL TIPS AND TRICKS
43. How can I quickly see the sum of a set of data without adding a Sum function to the worksheet? • Select the data cells calculate • Look on the Status bar on the bottom of the screen
• You can also view other functions such as count and average • You can add additional functions to the status bar by Right Clicking on the status bar and selecting Minimum and/or Maximum
44. How do I group my data? Grouping your data allows you to expand and collapse data to see more or less data as required. It does not insert totals, that you have to do manually or use the Data>Subtotal functionality. • • • •
Select the rows or columns you want to group Select Group on the Data tab of the Ribbon Select Rows/Columns Click OK Click here to expand/ collapse all levels
Click here to collapse a specific level
Click here to expand a specific level
• To remove, select the region and then select Ungroup on the Data Tab of the Ribbon
SELF HELP BI
| www.ispartners.co.za <<
22
>> EXCEL TIPS AND TRICKS
45. How do I add totals and subtotals to my data without having to manually insert Sum functions? • Select anywhere in the data list (ensure there are no blank rows or columns and that the headings are formatted differently to the data e.g. Bold)
• Select Subtotal on the Data tab of the Ribbon • At each change in is where the subtotal will be added • Use Function is the type of total e.g. Sum, Average, etc.
• Add subtotal to is what will be added • Click OK
(Note : Groupings are automatically added) • You can add additional Subtotals by repeating the process but ensure Replace current subtotals is unselected • To remove, select Remove All 23
>> SELF HELP BI
| www.ispartners.co.za
>> EXCEL TIPS AND TRICKS
46. How do I automatically highlight data that is greater than or equal to a value (Conditional Formatting)? • • • •
Select the range of data you want the conditional formatting to apply to Select Conditional Formatting on the Home Tab of the ribbon Select Highlight Cells Rules Select the cell rule you need e.g. Greater Than…
• Enter the value and highlight formatting (Note: when working with a % you need to enter fractions i.e. 20%=0.2)
• Click OK
SELF HELP BI
| www.ispartners.co.za <<
24
>> EXCEL TIPS AND TRICKS
47. How do I automatically highlight the top 3 data records in a data set (Conditional Formatting)? • • • •
Select the range of data you want the conditional formatting to apply to Select Conditional Formatting on the Home tab of the ribbon Select Top/Bottom Rules Select the cell rule you need e.g. Top 10 Items…
• Enter the number of top items and highlight formatting
• Click OK
25
>> SELF HELP BI
| www.ispartners.co.za
>> EXCEL TIPS AND TRICKS
48. How do I automatically add graphical indicators to a data set (Conditional Formatting)? • • • •
Select the range of data you want the conditional formatting to apply to Select Conditional Formatting on the Home Tab of the ribbon Select Data Bars, Colour sets or Icon Sets Select the type
Data Bars
Colour Scales
Icon Sets
SELF HELP BI
| www.ispartners.co.za <<
26
>> EXCEL TIPS AND TRICKS
49. How do I sort by the Icon Set? • Apply the conditional formatting icons • Select anywhere in the data range • Select the Custom Sort icon on the Data tab of the ribbon
• In the Sort by drop down box select the column you want to sort by • In the Sort On drop down box select Cell Icon • In the Order drop down box select the icon you want on top
• Select Add Level and repeat for the icon you want on the bottom
27
>> SELF HELP BI
| www.ispartners.co.za
>> EXCEL TIPS AND TRICKS
• Click OK
50. How do I lock cells? If you want to protect a worksheet (you don’t want other users to change certain calculations, etc.) you first need to specify which cells can be edited and which are locked. By default, Excel sets all cells to Locked so you need to unlock cells that can be edited. • Select the cells that can be edited • Select Format on the Home tab of the ribbon
(Note that the Lock Cell is currently On. Select Lock Cell to unlock the cells)
SELF HELP BI
| www.ispartners.co.za <<
28
>> EXCEL TIPS AND TRICKS
51. How do I protect my worksheet so that certain data or calculations cannot be changed? Once you have unlocked the cells that can be edited: • On the Home tab of the ribbon, select Format • Select Protect Sheet • Enter a password (optional, you can leave blank and have no password). Remember that you will need to know this password if you want to make any changes in the future so keep a record of your password
• If you do not want users to be able to select cells that are locked then unselect this (Note: this is recommended if you want to prevent the intellectual capital from being copied onto another worksheet ) • Click OK
52. How do I use logical functions IF, AND, OR? Logical functions return true or false results if certain conditions are met. Logical statements compare arguments. If you use text it must be placed in quotation marks. You can have up to 255 logical arguments in AND and OR functions. Examples of logical statements: A1=B1, A2>10000, C3=”Yes” AND Function
If all arguments in an AND function are met, then the result will be TRUE. If any one is not met, then the result will be FALSE Example: See formula here
The current month’s order must be greater than the previous month AND the future month’s order must equal or exceed the current month AND the current order must be greater than10 to qualify for a discount 29
>> SELF HELP BI
| www.ispartners.co.za
>> EXCEL TIPS AND TRICKS
OR Function
If any one argument in an OR function is met, then the result will be TRUE. If none is met, then the result will be FALSE Example: See formula here
The Discount is TRUE or if Top Client is “Yes” then they qualify for a discount. Note: TRUE does not need to be in quotation marks as Excel sees it as a logical argument result. “Yes” needs to be in quotation marks as it is text typed into a cell IF Function
If the logical test is TRUE then the Value if True is returned, else the Value if False is returned as the cell value Example: See formula here
The Discount Overwrite is TRUE then give a Discount of 10%, else no discount. Imbedded Functions Once you have mastered the logical functions, you can imbed them in one another so that the above three steps can be achieved in one formula as follows:
SELF HELP BI
| www.ispartners.co.za <<
30
>> EXCEL TIPS AND TRICKS
53. How do I sort by Month and not alphabetically? • Select the column that contains the month names • Select the Custom Sort icon on the Data tab of the ribbon
• In the Order drop down box select Custom List…
• Select the Custom list you want to sort by
• Click OK This will now be the default for this column until this sort option is deleted, so even if you click the quick sort it will sort by month and not alphabetically. To remove: • • • • 31
Select the Custom Sort icon on the Data tab of the ribbon Select the Month sort by level Select Delete Level Click OK
>> SELF HELP BI
| www.ispartners.co.za
>> EXCEL TIPS AND TRICKS
54. How do I sort by more than one field? • Select anywhere in the data range • Select the Custom Sort icon on the Data tab of the ribbon
• In the Sort by drop down box select the first column you want to sort by and specify how you want the data sorted
Select Add Level • In the Then by drop down box select the second column you want to sort by and specify how you want the data sorted
• Click OK
SELF HELP BI
| www.ispartners.co.za <<
32
>> EXCEL TIPS AND TRICKS
55. How do I compare different scenarios of data (What if)? • Create the data table with the required calculation • On the Data tab click What-If Analysis
• Select Scenario Manager… • Select Add
• Name the Scenario, select which cells will change, enter a comment
• Click OK 33
>> SELF HELP BI
| www.ispartners.co.za
>> EXCEL TIPS AND TRICKS
• Enter the values for this scenario
• Select Add to add new scenarios • Repeat for all scenarios then click OK To view the different scenarios: • • • •
On the Data tab click What-If Analysis Select Scenario Manager Select the Case Select Show
SELF HELP BI
| www.ispartners.co.za <<
34
>> EXCEL TIPS AND TRICKS
56. How can I quickly record macros? The Macro function is located on the View tab of the Ribbon. You can pin the Record Macro button to the Status bar for quick recording: • Right Click on the Status bar on the bottom of the screen
Status bar • Select Macro Recording • The Macro Record button will be permanently pinned to the Status Bar
57. How do I create a macro and run a macro? Macros are commonly used for repeated functions such a formatting of a Heading. Plan the steps ahead as once you start recording all steps are recorded, including any errors • Select a cell in the worksheet you want to format as the Header • Select the Macro Record button on the Status Bar • Enter a name and optional short cut key (as most Ctrl functions are used by Excel, Hold down Shift while selecting the letter, your shortcut is then Ctrl+Shift+ Letter)
• Click OK • Perform the steps you want recorded • Select the Stop Recording Macro button on the Status Bar
Stop recording
35
>> SELF HELP BI
| www.ispartners.co.za
>> EXCEL TIPS AND TRICKS
58. How do I assign a macro to a button? • Create the Macro • Insert a shape from the Insert tab
• Customise the shape to look like a button • Right Click on the button and select Edit Text, add text to the shape
• • • • •
Right Click and select Assign Macro Select the Macro Click OK When you select this button it will run the macro If you want to change the button Right Click on the button
SELF HELP BI
| www.ispartners.co.za <<
36
>> EXCEL TIPS AND TRICKS
59. How do I assign a macro to a ribbon? • • • • •
Create the Macro Right Click anywhere on the MS Ribbon From the drop down list Select Customise the Ribbon Select your tab or create a new one (see tip 1) Select New Group
• Rename the new group and make sure it stays selected (grey) • In the Choose commands from drop down box select Macros
• • • •
37
Select the macro Select Add Click OK The macro can be run by clicking the Icon on the Ribbon
>> SELF HELP BI
| www.ispartners.co.za
>> EXCEL TIPS AND TRICKS
60. How do I create a Pivot Table? • How Select anywhere in the data • Select Pivot Table on the Insert tab of the Ribbon • Click OK
• A new worksheet is created linked to this data with the Pivot Table framework as follows:
SELF HELP BI
| www.ispartners.co.za <<
38
>> EXCEL TIPS AND TRICKS
• Drag and drop the fields into the four Pivot area
Drag and drop
Report Filter allows you to filter your data to particular items • Select the Report Filter button • Select the items you want to see • Click OK
Filter button
39
>> SELF HELP BI
| www.ispartners.co.za
>> EXCEL TIPS AND TRICKS
61. How do I use Slicers in a Pivot Table? Slicers are a way of filtering data in a Pivot Table. Using the Pivot Table Report Filter is limiting, as you cannot see what data has been filtered. This is overcome with slicers. • Select the Pivot Table • Select Insert Slicer on the Pivot Table Tools Ribbon, Option tab • Select Insert Slicer • Select the field/s you want slicers for
• Click OK • Slicers can be resized and moved and formatted using the Slicer Tools Ribbon • Select the items you want to see in the pivot table (they will be highlighted)
SELF HELP BI
| www.ispartners.co.za <<
40
>> EXCEL TIPS AND TRICKS
41
>> SELF HELP BI
| www.ispartners.co.za
>> EXCEL TIPS AND TRICKS
CGF
RESEARCH INSTITUTE (PTY) LTD
Corporate Governance Framework
CGF Research Institute (Pty) Ltd presents the most comprehensive information, guidelines and training of its kind to help South African businesses create their own Corporate Governance Frameworks. Clients as diverse as Altron, Massmart, Vodacom, Gold Fields, African Rainbow Minerals, IBM SA, the National Prosecuting Authority, SITA, Denel, Telkom SA, the Social Housing Foundation, the Development Bank of SA and many more have benefited from our governance report programmes and extended services.
“An innovative compilation of Corporate Governance global policy and compliance trends, recommendations and emerging best practices…”
Our governance reports, policy templates and induction programmes are highly adaptable to ideally suit Company Directors, Executive & Senior Management, Governance Officers, Compliance Officers, Company Secretaries and Risk Management Professionals across all sizes of business. Director’s Fast Track programmes are also available. What our client’s say: …very impressed with the quality of the CGF product and think it provides an excellent service to companies, especially in these days of heightened attention to governance issues …a successful model to systematically analyze the complex subject of corporate governance The updates reflect the latest information both locally and internationally, and enable us to keep pace with best practice in that regard… …they are very comprehensive and allow us to access a wide range of materials without having to spend long hours on the research ourselves… I find the material extremely concise and user friendly. It is always a pleasure to know that I can consult a single, well researched and presented source that will give the information I need without any detracting background noise CGF provides a valuable tool for the company secretary who wishes to not only stay abreast of new developments in the area of corporate governance and related areas, but also to expand his/her knowledge base in a variety of highly relevant topics and to widely circulate this information, in a number of ways, to the benefit of the company, its directors, management and staff
For additional information please visit our websites at www.cgf.co.za | www.corporategovernance.co.za or contact +27 11 476 8264/1/0
Governance Beyond Boards CGF Honorary Patrons : Dr Mathews Phosa | Prof. Shirley Zinn | Devi Sankaree Govender CGF Patrons: ContinuitySA (Platinum) | Spescom DataVoice (Silver)
CGF is a Strategic Partner of ProudlySA
SELF HELP BI
| www.ispartners.co.za <<
42
>> EXCEL TIPS AND TRICKS
Improve Organisational Effectiveness Subheading to go here
Microsoft Business Intelligence solutions are designed to evolve with your business and build on your existing technology investments. With continuous innovation since initial release, Microsoft Business Intelligence solutions are built on open, industry-wide standards - making integration with your other systems easier and less costly than the alternatives. The strengths of SQL Server, SharePoint and Office are integrated on a single platform, improving workplace efficiency and empowering informed decision-making that moves your business forward.
http://www.microsoft.com/bi/
43
>> SELF HELP BI
| www.ispartners.co.za
>> EXCEL TIPS AND TRICKS
ACCELERATED BI AND FINANCIAL WAREHOUSING
Time to value in Business Intelligence projects is critical. iSPartners has developed flexible, user friendly pre-built Business Intelligence for your ERP solutions. Forget about long term, large scale BI-projects or ERP centric BI solutions that cannot change or adapt to the needs of the business. Accelerated BI brings you useful up-dated information collected from your various data sources in just 1 day from systems such as Dynamics AX and Dynamics NAV.
+27 11 463 8155 | www.ispartners.co.za | info@ispartners.co.za
SELF HELP BI
| www.ispartners.co.za <<
44
In the words of a CA(SA) … “The CA(SA) designation offers business the highest ethical standards, up-to-date and solid accounting methods, best practice business insight and the ability to implement real change. As a CA(SA), I have the ability to look at the particulars of a business and understand the detail of its circumstances. Based on the facts, change can then be implemented, both as a concept and as a process. To take a set of business intangibles and deliver concrete solutions that will aid the business in navigating the marketplace, optimising performance and finding the perfect balance between the facts and the philosophy. This is why many of the top companies listed on the JSE are led by CAs(SA). It provides the balanced foundation which makes passion for the business possible.” Vuyo Jack CA(SA) Co-founder and Chairman, Empowerdex
D03714/V/CM
THE DESIGNATION THAT BRINGS BALANCE INTO YOUR REPORTING AND PASSION INTO YOUR BUSINESS.
Visit www.findacasa.co.za for details.
>> EXCEL TIPS AND TRICKS
SELF HELP BI
| www.ispartners.co.za <<
46
>> EXCEL TIPS AND TRICKS
The Platform Improve Organisational for your Effectiveness IT Infrastructure Subheading to go here
Windows Server 2008 R2 expands on the award-winning technology of Windows Server 2008 to increase the reliability and flexibility of their server infrastructures. Improved features include new virtualization tools, better web resources, management enhancements, dynamic scalability and superior Windows 7 integration. Windows Server 2008 R2 tools give customers greater control, increased efficiency and the ability to react to front-line business needs faster than ever before.
http://www.microsoft.com/windowsserver2008/en/us/default.aspx
47
>> SELF HELP BI
| www.ispartners.co.za
MANAGING WITH ROLLING FORECASTS The idea of budgeting for a year ahead was developed in a time of stable markets, static costs, and predictable inflation. In today’s rapidly changing world managers can’t run their business with a one year fixed plan. Many leading organisations are changing to rolling forecasts to inspire and lead their organisations to better performance. Rolling forecasts direct management’s attention towards the future, and ensure that planning is ongoing, as opposed to an annual exercise. This book explains in practical terms how to design and implement a rolling forecast system. Many diagrams, practical examples and case studies illustrate how rolling forecasts work in practice, and how to use forecasts in management meetings. About the author John Stretch runs a consultancy specialising in world class management systems, operating in Southern Africa, the United Kingdom and the Middle East. He is a frequent visiting lecturer and Programme Director at Wits Business School, where he received an award for Best Executive Education lecturer in 2006/2007. He also teaches on executive courses for Henley Management College, Manchester Business School, and the London Institute for International Research. He has experience in many industries including mining, energy, retailing, financial services and telecommunications, and has worked with public sector organisations including SARS, ESKOM, the Namibian Port Authority, UNISA and the Reserve Bank of Botswana. He has authored two other publications How business strategy works in practice and Make your budget work.
+27 82 900 6065 | info@johnstretch.com | www.johnstretch.com
>> POWER PIVOT TIPS AND TRICKS
Give yourOrganisational Improve Data Meaning Effectiveness Subheading to go here
PowerPivot for Excel is a data analysis tool that delivers unmatched computational power directly within Microsoft Excel, the application users already know and love. Perform data analysis using familiar Excel features such as the Office Fluent user interface, PivotTable and, PivotChart views and slicers. PowerPivot is the fast way to generate rich and interactive analysis tools, easily mistaken for IT solutions after weeks of effort. Get the answers you need, gain deeper insight into any business aspect and shorten your decision cycles.
www.powerpivot.com http://www.powerpivot.com/learn.aspx
49
>> SELF HELP BI
| www.ispartners.co.za
>> POWER PIVOT TIPS AND TRICKS
Power Pivot PowerPivot for Excel is an authoring tool that you use to create PowerPivot data in an Excel workbook. You use Excel data visualisation objects such as PivotTables and PivotCharts to present the PowerPivot data that you embed or reference in an Excel workbook (.xlsx) file. PowerPivot for Excel lets you import, filter, sort many millions of rows of data, far beyond the one million row limit in Excel.
SELF HELP BI
| www.ispartners.co.za <<
50
>> POWER PIVOT TIPS AND TRICKS
1. How do I obtain an aggregated measure in a related table? • Ensure that a Relationship is created between your two tables • Created a new Column in your summary table. E.g. Customer Sales
• In the Formula Bar, type = and then select the appropriate aggregation function: SUMX, AVER AGEX, COUNTAX, MINX or MAXX • Choose the RELATEDTABLE function and relevant table where the granular measure exists • Specify the relevant column containing the measure. E.g. vw_FactSales[TotalSales] • Press the Enter key
2. How do I hide columns from my PowerPivot PivotTable? The hiding of columns can be accomplished in two ways: • • • •
Open the PowerPivot window Right click on the column that you want to hide Select the Hide Columns option Select the From PivotTable option
• • • •
Open the PowerPivot window Click on the Design tab Click on the Hide and Unhide icon Deselect the columns you wish to hide
3. How do I determine the distinct count of a specific entity? • In the PowerPivot Field List, right click on the selected table and select Add New Measure… • Specify a Measure Name. E.g. Distinct Customers • Identify the field that will be counted and then type in the formula using the COUNTROWS and DISTINCT functions (see below) 1
>> SELF HELP BI
| www.ispartners.co.za
>> POWER PIVOT TIPS AND TRICKS
• Click OK
4. How can I determine when last my PowerPivot data was refreshed? • • • •
Open the PowerPivot window Select the applicable table that you would like to check Click on the Design tab Click on the Table Properties icon
•
The refresh information is available in the bottom right hand corner of the Table Properties dialog
SELF HELP BI
| www.ispartners.co.za <<
2
>> POWER PIVOT TIPS AND TRICKS
5. How can I create a subset of items in my PowerPivot PivotTable? • Add the field to Row Labels or Column Labels • Click on the Options menu item in the ribbon • Click on the Fields, Items, & Sets icon and select either Create Set Based on Row Items… or Create Set based on Column Items… • All items will be added by default • Specify a name for the set • Click on the Delete Row button to remove unwanted items • Click on the OK button to create the set • Your newly created set will appear in the PowerPivot Field List
6. How can I sort the month names in my Calendar dimension in my pivot table so that they appear in the correct order and not alphabetically? • Right click on the month name • Select Sort -> Sort A to Z • Note: this technique will only work when the language of the month names in PowerPivot matches the selected Excel language
7. I have written a SQL query to import details for one of my PowerPivot tables. How can I modify the original query? • • • • • 3
Open the PowerPivot window Click on the applicable table and select the Design menu item in the ribbon Click on the Table Properties icon Edit the SQL statement Click on the Save button
>> SELF HELP BI
| www.ispartners.co.za
>> POWER PIVOT TIPS AND TRICKS
8. How can I determine the moving annual total (accumulated value of the last 12 months) for a particular measure? • In the PowerPivot Field List, right click on the table that contains the relevant measure and select Add New Measure… • Specify a measure name e.g. Last 12 Months • Type in the following calculation (note: green text is for illustration purposes only and must be excluded): CALCULATE( SUM(vw_FactSales[TotalSales]), DATESBETWEEN( DimDate[Date], NEXTDAY( SAMEPERIODLASTYEAR( LASTDATE(DimDate[Date]))), LASTDATE(DimDate[Date])))
-- Measure name -- Date column -- Start Date -- End Date
• The calculation will include a sum of the selected measure for all dates between the specified date range
9. My database does not contain a date / time table. What is the easiest way to include one in PowerPivot? • • • •
Open a new Excel workbook In the first cell (A1), type the name of the date field e.g. Date In the second cell, type the earliest date you wish to include in the date table e.g. 01/01/2010 Use standard Excel functions to define additional date attributes: o Year: YEAR(A2) o Quarter Number: FLOOR((MONTH(A7)-1)/3,1)+1 o Quarter Name: “Q” & TEXT(FLOOR((MONTH(A7)-1)/3,1)+1,”#”) & “-” & RIGHT(YEAR(A2),2) o Month Number: MONTH(A2) o Month Name: TEXT(DATE(2000,C2,1),”mmmm”) o Week Day: WEEKDAY(A2,1) o Week Number: WEEKNUM(A2) o Day Name: TEXT(A2,”dddd”) o Day In Month: DAY(A2)
• Highlight all the columns and drag the formulas down according to the number of dates required
• Save the Excel workbook and import it into your PowerPivot model
SELF HELP BI
| www.ispartners.co.za <<
4
>> POWER PIVOT TIPS AND TRICKS
10. Where can I find more information on the amount of space my PowerPivot model is occupying? • Open Windows Explorer and browse to: C:\Users\<user name>\AppData\Local\Temp\VertiPaq_<GUID>
Note: There could be multiple VertiPaq folders. Find the folder that has a timestamp that matches the workbook opening time
• To determine the amount of memory that the PowerPivot model is using, right click on the applicable VertiPaq folder and select Properties • The Size property will indicate the amount of memory used • The VertiPaq folder also contains other useful information: o Open the folder that ends with a .db o This folder contains a list of data sources (.ds), dimensions (.dim), cubes (.cub) and corresponding XML files o To relate a GUID to a particular entity, open the XML file and search for the name – see below
o Once you have related a GUID to a table, check the related folder size to find out how much memory that specific table is using in PowerPivot
11. How do I add a Pivot Table / Chart to an existing sheet PowerPivot supports a number of report layouts when creating reports from a PowerPivot model.
However, there are cases where the report layout required is different to one of these templates. In this case, we can begin with a report layout and extend that sheet to include additional elements.
• From an existing report, Click New PivotTable from the PowerPivot ribbon. Insert the Pivot table into an existing cell in the sheet. 5
>> SELF HELP BI
| www.ispartners.co.za
>> POWER PIVOT TIPS AND TRICKS
• This will allow you to create and structure a new PivotTable as per normal. o However, this PivotTable will not be linked to existing Slicers in the sheet. • To attach the existing slicers to the new PivotTable, Right click on the relevant Slicer, and select PivotTable Connections
• This will allow you to attach the slicer to the relevant Pivot Tables.
Note: this technique can be used to attach slicers to any PivotTable or chart in the spreadsheet.
12. How do I calculate a Percentage Contribution? • In the PowerPivot Field List, right click on the table that contains the relevant measure and select Add New Measure… • Specify a measure name e.g. Sales Quantity Percentage Contribution • Type in the following calculation: o Sum(FactSales[SalesQuantity]) / CALCULATE(Sum(FactSales[SalesQuantity]), ALL(DimProduct[BrandName])) SELF HELP BI
| www.ispartners.co.za <<
6
>> POWER PIVOT TIPS AND TRICKS
• This technique can be used along any attribute as required.
13. How do I improve the Visual Impact of my Charts 1 - Secondary Axis? Excel provides a number of techniques which can be used to improve the Visual Impact of their charts and graphs. A Standard line chart which compares two measures over time, such as Sales Amount and Return Amount would begin by looking similar to the screenshot below:
• These two measures are orders of magnitude different, and so the Sales Amount is causing the Return Amount to be hidden. • Right Click on the Sum of ReturnAmount line, and select Format Data Series • Under Series Options, select Plot Series on Secondary Axis
• This allows the Return Amount trend to be viewed alongside the Sales Amount.
7
>> SELF HELP BI
| www.ispartners.co.za
>> POWER PIVOT TIPS AND TRICKS
14. How do I improve the Visual Impact of my Charts 2 - Smooth Lines? By default, line charts will join data points together, causing a jagged curve. It is possible to smooth these curves out by doing the following: • Right Click on the Sum of ReturnAmount line, and select Format Data Series • Under Line Style, Check the Smoothed Line option.
• This can be done for all lines where necessary.
SELF HELP BI
| www.ispartners.co.za <<
8
>> POWER PIVOT TIPS AND TRICKS
15. How do I improve the Visual Impact of my Charts 3 - Borders and Fill? • Right Click on the chart area, and select Format Chart Area… • Under Fill, Select Gradient Fill • Under Border Styles, Select Rounded Corners
16. How do I improve the Visual Impact of my Charts 4 - Mixed Chart Types? • Right Click on the Sum of Sales Amount line and select Change Series Chart Type… • Select a Column / Area Chart Type
9
>> SELF HELP BI
| www.ispartners.co.za
>> POWER PIVOT TIPS AND TRICKS
17. Where did my Field List go? PowerPivot has introduced a particular Field List which provides additional functionality over the standard Excel Pivot Table Field List. There may be situations where the original Pivot Table Field list is displayed, where the ability to create measures is hidden.
To display the PowerPivot Field List, ensure that the Field List button is selected in the PowerPivot Ribbon.
SELF HELP BI
| www.ispartners.co.za <<
10
>> POWER PIVOT TIPS AND TRICKS
18. How do I display a related column in a table? • Ensure that a Relationship is created between your two tables • Created a new Column in your detail table. E.g. Country Name
• In the Formula Bar, type the following expression o RELATED(DimGeography[RegionCountryName])
19. How do I reference a particular Dimension member value? • In the PowerPivot Field List, right click on the table that contains the relevant measure and select Add New Measure… • Specify a measure name e.g. Contoso Sales • Type in the following calculation: o CALCULATE(Sum(FactSales[SalesQuantity]), DimProduct[BrandName]=”Contoso”)
11
>> SELF HELP BI
| www.ispartners.co.za
>> POWER PIVOT TIPS AND TRICKS
• This technique can be used to reference any Dimension member and measure.
20. How do I compare a measure to a particular Dimension member? There may be cases where we wish to compare brand sales against a particular brand, for cases where we are benchmarking against particular entities. The following example will represent a particular brand’s sales as a percentage of the Litware brand. • In the PowerPivot Field List, right click on the table that contains the relevant measure and select Add New Measure… • Specify a measure name e.g. Percentage of Litware Sales • Type in the following calculation: o Sum(FactSales[SalesQuantity])/CALCULATE(Sum(FactSales[SalesQuantity]), DimProduct[Brand Name]=”Litware”)
SELF HELP BI
| www.ispartners.co.za <<
12
>> POWER PIVOT TIPS AND TRICKS
21. How can I get the transactions that occurred on the First / Last day of any Time Period? It is a common requirement in Inventory solutions to isolate those transactions that occur on the last day of the month. This is often used to determine closing stock figures. • In the PowerPivot Field List, Right click on the Fact table name and select Add New Measure… • We can then use a combination of the CALCULATE function and the LASTDATE function as follows: o =CALCULATE(SUM(FactInventory[OnHandQuantity]), LASTDATE(DimDate[DateKey]))
13
>> SELF HELP BI
| www.ispartners.co.za
>> POWER PIVOT TIPS AND TRICKS
This is a dynamic approach which will evaluate the values at the end of any period, as it evaluates according to the filters at the time of calculation. i.e. it is not specifically a Month end figure; it will also determine end of Day, Week, Quarter and Year figures if that is the level that is currently being reported on. The following screenshot highlights this measure in action at various levels.
SELF HELP BI
| www.ispartners.co.za <<
14
Notes
SO YOU THINK YOU CAN TRADE? PUT YOUR MONEY WHERE YOUR MOUSE IS, AND YOU COULD MAKE A MILLION! MAM (otherwise known as Make a Million) is a JSE trading game for independent online investors. For the 6th year MAM will award the best performing portfolio at the end of the 3-month competition period with R1 million. The R1 million is awarded to the trader who has grown his total portfolio percentage return to the highest on the last day of the competition.
www.makeamillion.co.za
Specialists in
IT RECRUITMENT Vision: Our business philosophy is based on the belief that our candidates are not merely employees filling positions, but critical building blocks of our client’s business strategies, driving business growth and solving business issues. Introduction: Over the past two decades our clients’ staffing needs have evolved along with their technological advancements and the inception of globalisation. We pride ourselves on our ability to develop and adapt our specialist Talent Solutions to keep up with the times and grow in line with market trends and requirements. We attract top talent as a result of our excellent reputation, with candidates seeking us out particularly in the Microsoft (MS) Applications space. Our industry is highly dynamic and requires us to keep abreast of local and international developments, consistently applying industry Best Practices to our staffing solutions. Placing the right people in the right positions presents a number of challenges, including finding and developing the right skill sets, while companies are facing new challenges, greater needs, higher expectations and extensive choices. We take a personalised and pro-active approach to staffing needs and placements. We only recruit within our specialised fields, assuring clients and candidates of expert attention, knowledge and advice within their niche areas.
skilled at recruiting and assessing higher level, specialist candidates. Client and candidate expectations increase with the level of position being filled, which typically involves greater customisation for interim and permanent placement. Candidates also expect to interact with consultants who understand their specialised fields and are able to provide solid career advice. These capabilities extend to individualised recruitment solutions for all customers, with each solution made to fit both client and candidate needs. We have a personalised and proactive recruitment process. We are further known for a quick turnaround time, both in response to job specifications and candidate applications. Standard and advanced candidate checking form part of our approach, from references to thorough background checks, using external service providers. Industry best practices are applied to all staffing solutions. A thorough recruitment process ensures that each client’s core business processes and activities are discussed and investigated; and the best possible candidates introduced to suit your corporate structure and culture. Memberships: APSO – Association of Personnel Service Organisations Many social networking groups that are used by dedicated IT professionals – follow us on Twitter: Its_About_People
We believe in quality over quantity and match skills and culture accurately. This ensures that only the best candidates are presented, thereby simplifying the selection process and providing a thorough and professional short list. Differentiators: Leaders in MS Applications space, albeit, not exclusively, with a solid track record and market reputation that attracts Top Calibre Candidates. Our clients expect us to have access to and be
+27 11 463 0120 | info@itsaboutpeople.co.za | www.itsaboutpeople.co.za
CORPORATE PERFORMANCE MANAGEMENT (CPM) Progressive IT solutions for the Office of Finance
The iSPartners CPM unit was established to provide specialist financial consulting services to the Office of Finance. iSPartners CPM brings together finance and technology to transform the way finance departments operate. Our expertise ensures optimisation of the finance function and enables more efficient and effective financial processes. Our innovative solutions allow the Office of Finance to focus on strategic financial analysis and rapidly adapt to constantly changing business conditions. iSPartners CPM brings its experience in providing solutions for finance best practices to: > Annual Budgets > Driver Based Rolling Forecasts > Fast Close Management Reporting > Statutory & Regulatory Reporting > Statutory Consolidations > Business Process Automation > Document Management and Collaboration
+27 11 463 8155 | www.ispartners.co.za | info@ispartners.co.za