Karabina Solutions BI Book 2 by Karabina

Self Help BI Book ™©

™

SOLUTIONS

Connect with a single interface Karabina Solutions leverages SharePoint’s collaboration features to help our clients connect people to information and expertise within their organisations. SharePoint’s rich document management capabilities, together with its latest social features addresses the emerging need for organisations to provide virtual collaboration and innovation environments for employees.

Our strong partnerships with leading third party SharePoint Enterprise Content Management solution vendors enables us to extend native SharePoint functionality and include first class scanning, indexing and document generation capabilities in our solutions. With a strong focus on the end-user experience, SharePoint provides a way to present CRM, Business Intelligence and Line-Of-Business information to decision makers through a single interface. We assist our clients in using familiar Microsoft Office tools such as Microsoft Excel to share and interact with Business Intelligence information in SharePoint.

+27 11 463 8155 www.karabina.co.za info@karabina.co.za

™

Introduction 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. In short, it simultaneously demystifies perceived complexities and empowers all Excel users to stretch their own capabilities. 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 2013. In this latest edition, the new Excel boasts extensive customisation 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 become a power user. 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 Power Pivot and Power View with its potential to model data, turn it into information and visualise the results in new ways with Slicers and Enhanced graphing. Also look into the add-ins Power Query and Power Maps, which allow users to work with massive amounts of data from many sources, combine these information sources and build models to visualise, right on your desktop! These additional options only run on Office 2013 Professional Plus and O365 ProPlus. For more information, please visit (http://office.microsoft.com/en-us/excel/powerbi-download-add-in-FX104087144.aspx). 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 and SharePoint. With Excel 2013, you have a platform for building smart applications, solving issues, the ability to visualise information and share this with others. Karabina 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 for multiple years. We are passionate about what Excel 2013 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. For more information visit: www.karabina.co.za or contact us on +27 11 463 8155 To attend a course and further improve your skills, see our latest training programme on www.karabinaacademy.co.za Disclaimer

The information within this publication remains the property of iSPartners (Pty) Ltd and its company Karabina. The associated Self-help BI™ © tools were written for and on behalf of iSPartners (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 of this book without the written consent of the publisher will constitute a criminal offence. No part of this publication may be reproduced, republished, reused, photocopied or reprinted without the prior written consent of the publisher - iSPartners (Pty) Ltd. Should you wish to use part of the publication or to reproduce this book please contact the publisher. © Copyright reserved 2013 iSPartners Tel: +27 11 463 8155 Email: info@ispartners.co.za Web: www.ispartners.co.za

CRM - Rapid Engagement Plan Waiting for the perfect time to start your CRM implementation can lead to procrastination. Now your business does not have to put off starting that CRM project with the launch of Karabina’s new suite of pre-packaged Rapid CRM Engagement solutions. Our extensive experience and IP we have built over the years has enabled us to define the balance between effort and cost. Scope and effort are grouped as pre-packed consulting offerings to our customers to ensure the delivery of core functionality. A series of consulting and analytical templates are used in this approach, which assist in keeping costs down and aids in keeping the solution focussed. Our Rapid Engagements packages allow our customers to choose the option that is suited to their business needs and are in line with their budget.

The benefits include: • Higher Return on Investment • Shorter development cycles • Higher guarantee on deliverables and outcomes • Focus on core system functionality • Low Risk • Higher user adoption rate • Leveraging core built-in CRM functionality minimising custom development Tel: +27 11 463 8155 | Web: www.karabina.co.za | Email: info@karabina.co.za A N I S PA R T N E R S C O M PA N Y

™

Specialists in IT Recruitment

www.itsaboutpeople.co.za

People are central to our business. We believe that our candidates are not merely employees filling positions, but critical building blocks of our clientsâ&#x20AC;&#x2122; business strategies thereby driving business growth and solving real business issues. Accordingly, we are passionate about matching the best candidates to the best jobs! We are focussed on results and pride ourselves on our ability to develop and adapt our specialist IT Talent Solutions in a dynamic market environment. We form strong partnerships with both our clients and candidates to ensure we achieve a perfect match of career aspirations and job goals. With our background and specialist knowledge â&#x20AC;&#x201C; we can actively contribute to your strategy and future success as we align to your unique talent goals, culture and resource model.

CONTACT IAP

PHYSICAL ADDRESS

SOCIAL MEDIA

Tel: +27 11 463 8155

iSPartners Building,

Twitter: its_about_peopl

Fax: 086 538 6810

Design Quarter District,

Email: info@itsaboutpeople.co.za

Leslie Ave east,

Facebook: itsaboutpeople

Skype: its_about_people

Fourways, Johannesburg,

LinkedIn: company/its-about-people YouTube: IAPza

South Africa A N I S PA R T N E R S C O M PA N Y

Contents Excel Tips & Tricks 1.

Quick Analysis of data

2. Which tab to use in the Quick Analysis Gallery?

2-3

3. Fill out columns of data with Flash Fill

4-5

4. Connection to new data sources

5. Use a timeline to show data for different time periods

6-7

6. Drill Down, Drill Up to see different levels of detail

7-9

7. Using OLAPâ&#x20AC;&#x2122;s calculated measures and members

8. How do I create and customise my own Ribbon?

10-11

9. Most useful Short Cut Keys

11-12

10. Function Keys

11. How do I use the Fill Handle?

12-13

12. How do I use the Auto Fill Options button?

13. How do I wrap text?

14. How do I quickly adjust column width or row heights to fit the data?

15. How do I force a line break in a cell?

16. How do I set a default shape?

15-16

17. How do I copy data so that it remains linked to the source data (paste link)?

18. How do I keep track of whatâ&#x20AC;&#x2122;s happening to my data when I make changes (paste as linked picture)?

6-17

19. How do I Paste Transpose?

20. How do I access data in a cell?

21. How do I copy and paste data from another source so that it has the default formatting of my workbook? 8 22. How do I only select what is visible?

23. How do I quickly create a named range?

24. How do I use a named range in a formula?

20-21

25. How do I create a custom list?

21-22

26. How do I calculate the week number of a particular date?

27. How do I calculate the number of working days?

28. How do you ensure that a cell always has the current date?

29. How do I restrict the data that can be entered in a cell to a date period?

30. How do I restrict the data that can be entered in a cell to a list (Data Validation)?

31. How do I prevent data from being entered into a cell until requisite data has been added?

32. How do I prevent data from being entered into a cell unless other data meets certain conditions?

33. How to pick an item from a drop-down list?

34. How do I convert a text list of data into columns?

35. How do I highlight duplicate data?

26-27

36. How do I delete duplicate data?

Contents 37. How do I consolidate my data?

38. How do I turn off the grid lines?

39. How do I Maximise workspace?

40. How do I freeze headings so that I can see them when I scroll down (Freeze Panes)?

30-31

41. How can I see more than one workbook at a time (Arrange)?

42. How can I compare the content of two workbooks (Side by Side and Synchronous scrolling)?

43. How can I see different parts of one worksheet at the same time (Split screen)?

44. How can I see two worksheets from one workbook at the same time?

45. How do I save a certain view of the data that I use often (Custom Views) ?

46. How can I quickly see the sum of a set of data without adding a Sum function to the worksheet?

47. How do I group my data?

48. How do I add totals and subtotals to my data without having to manually insert Sum functions?

49. How do I automatically highlight data that is greater than or equal to a value (Conditional Formatting)?

35-36

50. How do I automatically highlight the top 3 data records in a data set (Conditional Formatting)?

36-37

51. How do I automatically add graphical indicators to a data set (Conditional Formatting)?

37-38

52. How do I lock cells?

53. How do I protect my worksheet so that certain data or calculations cannot be changed?

54. How do I use logical functions IF, AND, OR?

39-42

56. How do I sort by more than one field?

43-44

57. How do I compare different scenarios of data (What if )?

44-46

58. How can I quickly record Macros?

59. How do I create a Macro and run a Macro?

60. How do I assign a Macro to a button?

48-49

61. How do I assign a Macro to my ribbon?

49-50

62. How do I create a Pivot Table?

50-52

63. Creating a PivotTable using Recommended Pivot Tables

64. How do I use Slicers in a Pivot Table?

65. Create a standalone PivotChart

55-56

Power Pivot Tips & Tricks 1.

How do I install Power Pivot and Power View in Excel 2013?

2. I have an Excel workbook with a Power Pivot model created in an earlier version of Power Pivot.

How can I convert this model to the latest version?

3. My workbook seems to freeze periodically and then returns to normal again. What could this be?

4. How do I create a hierarchy for my date dimension?

61-62

5. The month names display alphabetically in my PivotTable.

How can I get them to display according to calendar month?

62-63

Contents 6. Is it possible to drill down to detail from a Power Pivot PivotTable?

7. My Power Pivot model is quite complex.

How can I provide a simplified view of this model to certain end users?

8. How can I rank the data in one of my dimension tables according to a sum of a value in my fact table?

64-66 66

9. Earlier versions of Power Pivot did not allow for the formatting of measures.

Does it still apply to the latest version?

10. Does the new version of Power Pivot support any form of Metadata?

11. How do I create a timeline to filter data according to different time periods?

12. How can I quickly analyse my current table by another dimension, without changing dimensions in my

PivotTable fields list?

69-70

13. Will Power View be available to an Excel user if I donâ&#x20AC;&#x2122;t have a SharePoint environment?

70-71

14. What new reporting types are available in Power View for Excel 2013?

71-73

15. How do I obtain an aggregated measure in a related table?

73-74

16. How do I hide columns from my Power Pivot Table, Chart or Power View?

17. How do I determine the distinct count of a specific entity?

74-76

18. How can I determine when last my Power Pivot data was refreshed?

19. How can I create a subset of data based on rows or columns?

20. I am connected to a database and have written a SQL query to import data for one of my

Power Pivot tables. How can I modify the original query?

21. How can I determine the moving annual total for a particular measure

(Accumulated value over the last 12 months)?

77-78

22. My database does not contain a date/time table.

What is the easiest way to create my own in Power Pivot?

23. Where can I find more information about the amount of space and memory my Power Pivot

model is using?

78-79

24. How do I add a PivotTable/Chart to an existing sheet?

79-80

25. How do I calculate a Percentage Contribution?

26. How do I improve the visual impact of my charts by using a secondary axis?

81-82

27. How do I improve the visual impact of my charts by using smooth lines?

83-84

28. How do I improve the visual impact of my charts by editing the borders and fill options?

29. How do I improve the visual impact of my charts by using mixed chart types?

30. Where did my Field List go and how can I get it back?

85-86

31. How do I display a related column in a table?

86-87

32. How do I reference a particular dimension member value?

33. How do I compare a measure to a particular dimension member?

34. How can I get the transactions that occurred on the First/Last day of any Time Period?

35. How do I add my existing Excel data to Power Pivot?

Microsoft Power BI Bonus Tips

BI - Rapid Engagement Plan Many organisations today have vast amounts of data but lack the ability to leverage it and obtain true business insight. Large scale Data Warehouse projects can take time to implement and companies are often left to rely on gut feel to stay ahead of the competition. Karabina™ Solutions embraces the latest advancements in Microsoft’s Business Intelligence (BI) stack and has created an alternative for organisations that need to see results in a short period of time. Our Rapid BI engagement packages leverage Microsoft’s new Tabular capability to create in-memory semantic data models. The reporting experience is delivered through Microsoft Excel and will supply your organisation with the much needed direction it needs and FAST. Our packaged offerings allow you to choose priority areas which will have the highest impact on your business. Combining Tabular models with Microsoft Excel‘s reporting and dashboard capabilities enables us to keep costs down while keeping solutions simple, effective and relevant.

Connect with Us

™

Notes

Excel Tips & Tricks The power of Excel needs no introduction. Excel 2013 brings you even more! The first thing you’ll see when you open Excel is a brand new look. It’s cleaner, but it’s also designed to help you get professional looking results quickly. You’ll find many new features that let you get away from walls of numbers and draw more persuasive pictures of your data, guiding you to better, more informed decisions.

EXCEL TIPS & TRICKS

1. Quick Analysis of data Data Analysis is now quick and easy. Creating charts, formatting spreadsheets, adding Totals and PivotTables can be done in a few easy steps.

• Select the range of cells containing the data that you want to analyse. • Click on the Quick Analysis button or press CRTL + Q for the Quick Analysis interface to pop up.

• In the Quick Analysis Gallery, select the appropriate choice for your requirements.

• Hover over each gallery and select an option to see a preview of your formatted data, as per the option selected.

2. Which tab to use in the Quick Analysis Gallery? Formatting highlights sections of your data by adding Icon sets, data bars, colors and others. This helps you analyse values in your spreadsheet by comparing colors or by using different Icon sets.

Self Help BI ™© | www.karabina.co.za

EXCEL TIPS & TRICKS

Charts are based on different data types. If you are uncertain of the type of chart to use, click on the Insert option and select the Recommend Charts button.

Totals allow for calculations in columns and rows. For example, clicking on Sum calculates a total for the columns selected.

Tables allow you to easily filter your data or create PivotTables.

A Sparkline is a mini line chart, which fits into a single cell within Excel. It presents the general shape of data (typically over time) in a condensed way.

Self Help BI ™© | www.karabina.co.za

EXCEL TIPS & TRICKS

3. Fill out columns of data with Flash Fill Flash Fill under the Data Tab helps to eliminate repetitive tasks for you. It detects what you want to do by identifying patterns in your data. In the example below, we want to create a Name column that is a combination of the FirstName and the Surname fields. You only need to type the first combination of the two fields in the first row. When you start the second record, the cells are Flash Filled by Excel.

To demonstrate this new feature, enter the following in a spreadsheet.

Self Help BI ™© | www.karabina.co.za

EXCEL TIPS & TRICKS

Enter a new ID number in cell A3 then click on cell B3.

Navigate to the Home tab in the ribbon. From the Editing section click “Fill” and then click on “Flash Fill”.

Alternatively, enter the shortcut key (Ctrl+E) in cell B3. The cell will now show you the birthday based on the ID number typed in cell A3.

Self Help BI ™© | www.karabina.co.za

EXCEL TIPS & TRICKS

4. Connection to new data sources Data can be imported from data feeds like Windows Azure DataMarket, SharePoint and OData to either a PivotTable or Table. Connections can also be made to other OLE DB providers. Multiple tables can be used in the Excel Data Model. Under the Data tab, select From Other Sources to import the applicable source data.

5. Use a timeline to show data for different time periods A timeline helps to compare data in a PivotTable or PivotChart over different date periods. Selections can be made on the timeline for a specific date or for multiple dates. Timelines represent an easy way of navigating through your data in a visual manner. Create a PivotTable or PivotChart from your data source. Once this is finished, click on the Insert tab and select Timeline. This will show a text box, allowing you to select the relevant date field for your timeline.

Self Help BI ™© | www.karabina.co.za

EXCEL TIPS & TRICKS

Click OK Your timeline has now been created and can be used to segment your data accordingly.

6. Drill Down, Drill Up to see different levels of detail Navigation to different levels of data has been simplified with Drill Down and Drill Up functionality. Your current view might be looking at summarised data in your PivotTable or PivotChart hierarchy and you might need a lower level of detail to have a more comprehensive view of the data. With the Drill Down functionality, you are now able to navigate down a level in the data hierarchy, to see the detail of the summarised data. You can then select Drill Up which will take you back to the summarised level. In the PivotTable Tools tab, select Analyze. If you have added a hierarchy field to your report you will be able to Drill Down and Drill Up. In the example below, a Year to Month Hierarchy has been combined with a Sales Amount Measure. If you click on Drill Down, only the applicable year’s months are visible.

Self Help BI ™© | www.karabina.co.za

EXCEL TIPS & TRICKS

Notice the Drill Up arrow which will show the data at year level again. You can also right click on the field and select Drill Down/Drill Up.

Self Help BI ™© | www.karabina.co.za

EXCEL TIPS & TRICKS

Quick Explore With Quick Explore you can quickly drill down/drill up on your datasets. It is easy to navigate to different levels of data keeping selected measures intact. Click on a cell and click the Quick Explore button.

7. Using OLAP’s calculated measures and members BI functionality can be leveraged by adding your own calculated Members or Measures using MDX calculations. Calculated Members or Measures can be created or modified without installing 3rd party applications. Please note that this section requires an OLAP data source i.e. a MS Analysis Services Cube. Choose a PivotTable that is based on an Analysis Services Cube. In the PivotTable Tools tab select Analyze and then click on fx OLAP Tools. You will now be able to create a calculated Measure or Member with MDX.

Self Help BI ™© | www.karabina.co.za

EXCEL TIPS & TRICKS

8. How do I create and customise my own Ribbon? In Excel 2003 and earlier versions, you had the ability to create or customise a toolbar. With the introduction of the MS Ribbon in 2007 you were no longer able to do this. With Excel 2013, you can create and customise your own ribbon again, including all your most frequently 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

Self Help BI ™© | www.karabina.co.za

EXCEL TIPS & TRICKS

• Select New Group in the list and rename e.g. Formatting (Use this group for all your formatting functions) • 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 this to other team members by clicking on Import/Export • Click OK to exit customisation

9. Most useful Short Cut Keys File Functions •Save •Print •New workbook

Ctrl + S Ctrl + P Ctrl + N

Editing •Copy •Paste •Paste Special •Repeat Last Command •Cut •Undo •Find/Replace •Go To •Fill Down •Fill Right

Ctrl + C Ctrl + V CTRL+ALT+V Ctrl + Y or F4 Ctrl + X Ctrl + Z Ctrl + F Ctrl + G Ctrl + D Ctrl + R

Self Help BI ™© | www.karabina.co.za

EXCEL TIPS & TRICKS

Formatting • Bold • Italics • Underline

Ctrl + B Ctrl + I Ctrl + U

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

10. Function Keys

F1 = Help F2 = Edit Cell content F3 = Display Named Ranges for pasting F4 = Repeats previous command F5 = Go To

11. 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.

Self Help BI ™© | www.karabina.co.za

EXCEL TIPS & TRICKS

• Using your alternate mouse button click and drag down or across to the region you want to fill.

12. 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.karabina.co.za

EXCEL TIPS & TRICKS

13. 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 of the cell, flows onto a new line.

• Select the cells, column or row you want to apply the wrapping to. • Select Wrap Text on the Home tab of the ribbon.

• Result:

Self Help BI ™© | www.karabina.co.za

EXCEL TIPS & TRICKS

14. 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 click here to fit columns double click 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 click any of these

15. How do I force a line break in a cell? To force a line break in a cell, Press Alt + Enter.

line break

16. How do I set a default shape?

• Select Shapes on the Insert tab of the MS Ribbon.

Self Help BI ™© | www.karabina.co.za

EXCEL TIPS & TRICKS

• • • • •

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, it will select the default shape you have just created.

17. 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 to Right Click Select the paste link icon.

18. How do I keep track of what’s happening to my data when I make changes (paste as linked picture)? 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 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) • Right Click

Self Help BI ™© | www.karabina.co.za

EXCEL TIPS & TRICKS

• The data and/or graph is pasted as a picture that changes as the data changes. While the pasted picture can not 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

Please note: You cannot use the paste picture link if you have formatted your data as a Table using the Table Styles. 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.karabina.co.za

EXCEL TIPS & TRICKS

19. How do I Paste Transpose?

• • • •

Select the data Copy the data Select the new copy position Right Click and select the copy transpose icon

20. How do I access data in a cell? There are three ways to access the content of a cell

• Double click on the cell • Select the cell and press F2, or • Select the cell and access the formula bar

The formula bar can edit cell content from there

21. 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

Self Help BI ™© | www.karabina.co.za

EXCEL TIPS & TRICKS

22. 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 of the ribbon and select Go to Special)

• Select Visible Cells only and click OK

• Copy and Paste as normal

Self Help BI ™© | www.karabina.co.za

EXCEL TIPS & TRICKS

23. How do I quickly create a named range? 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. • Select the range or cell • Type the new name in the name box on the formula bar e.g. TEST in the example below (Note: the name must be unique and contain no spaces)

• Press Enter (Note: this process is very important as it saves the name)

24. 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 with understanding complex calculations. • Name the cell/range • Type the formula. When you get to the point where you need to use the figure, either type in the name of the range or press F3 to list all the named ranges. Select the required name

Self Help BI ™© | www.karabina.co.za

EXCEL TIPS & TRICKS

• Complete the formula and press enter

• Click OK

25. 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 i.e. 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 • Click the File tab and select Options • In the Options dialogue select Advanced

Self Help BI ™© | www.karabina.co.za

EXCEL TIPS & TRICKS

• Scroll down and click on the Edit Custom List button • Click on Import to import your list (it should be listed in the Import list of cells box)

• 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 the rest of the cells with the content of the Custom List.

Self Help BI ™© | www.karabina.co.za

EXCEL TIPS & TRICKS

26. How do I calculate the week number of a particular date?

• In the cell, type =WEEKNUM(Date) where Date = the cell that has the date.

Please note: The cell must have a valid date format.

27. How do I calculate the number of Working Days?

• In the cell, type =NETWORKDAYS(start_date; end_date; [holidays])

Please note: The cell must have a valid date format.

28. How do you ensure that a cell always has the current date?

• Type =TODAY() in the cell and press enter • Format the cell to only display a date

Self Help BI ™© | www.karabina.co.za

EXCEL TIPS & TRICKS

29. How do I restrict the data that can be entered in a cell to a date period?

• Select the cell(s) that you want the restriction to apply to • 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

30. 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 list • Name the list • Select the cell(s) to add the drop down list to • 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 to display the available lists • Select the Name of the list • Select OK

31. 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). (Note: ensure that Ignore blank is not ticked) • Click OK Self Help BI ™© | www.karabina.co.za

EXCEL TIPS & TRICKS

32. How do I prevent data from being entered into a cell unless other data meets certain conditions? • 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

33. How to pick an item 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 (ensure that the column already has data and the information you want to enter is already in the column) • Right Click • Select Pick from drop down list • A list now appears listing all unique items in that column. Select the data you want

34. How do I convert a text list of data into columns? The drop down list is generated from the text in the existing column that you are adding data to

• 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 a Format • Click Finish

Self Help BI ™© | www.karabina.co.za

EXCEL TIPS & TRICKS

35. How do I highlight duplicate data?

• Select the range you want to check • On the Home Tab select Conditional Formatting

• Select Highlight Cells Rules • Select Duplicate Values • Select your required formatting • Click OK

Self Help BI ™© | www.karabina.co.za

EXCEL TIPS & TRICKS

Another way of finding duplicate values is to select the data and then click the

icon in the bottom right corner.

The duplicate cells will be highlighted.

Self Help BI ™© | www.karabina.co.za

EXCEL TIPS & TRICKS

36. 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 box select the column that has the potential duplicates

• Click Ok. A dialogue box will appear confirming the removal of the duplicate values.

Self Help BI ™© | www.karabina.co.za

EXCEL TIPS & TRICKS

37. 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 consolidated Browse and select the first set of data

Select if you want the data to be linked and listed in the groups

Select if you want headers from rows and/or columns

Headings generated from source data

Self Help BI ™© | www.karabina.co.za

EXCEL TIPS & TRICKS

38. How do I turn off the Gridlines?

• Deselect Gridlines on the View Tab of the Ribbon

39. How do I maximise workspace?

• Turn off the Formula bar by deselecting it on the View Tab of the Ribbon. • Turn off the ABC123 Headings by deselecting Headings on the View Tab. • Right click on the ribbon and select Collapse the Ribbon.

40. How do I freeze headings so that I can see them when I scroll down (Freeze Panes)? • Select the cell one position below and to the right of the position that you would like to freeze the headings for. • On the View tab of the Ribbon, select Freeze Panes

Self Help BI ™© | www.karabina.co.za

EXCEL TIPS & TRICKS

41. How can I see more than one workbook at a time (Arrange)? If you would like to navigate more than one workbook at a time, you can do this using the Arrange feature.

• Open two workbooks • Select Arrange All on the View Tab of the Ribbon • Select how you want the windows arranged • Click OK

42. How can I compare the content of two workbooks? (Side by Side and Synchronous scrolling) If you have data in several workbooks that you would like to compare, you can do this using 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: Synchronous scrolling will turn on by default)

43. How can I see different parts of one worksheet at the same time? (Split screen) If you have data in several workbooks that you would like to compare, you can do this using Synchronous scrolling. • Select a cell in the worksheet (if you select a cell in row 1, a vertical split is created. If you select a cell in column A, a horizontal split is created. If you select any other cells 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 the split to the required size.

Reposition by dragging grey line

• To remove the split, deselect Split on the View tab of the Ribbon.

Self Help BI ™© | www.karabina.co.za

EXCEL TIPS & TRICKS

44. 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. This means that 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

45. 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)

Self Help BI ™© | www.karabina.co.za

EXCEL TIPS & TRICKS

46. How can I quickly see the sum of a set of data without adding a Sum function to the worksheet?

• Select the cells that contain the data that you would like to calculate. • Look on the Status bar on the bottom of the screen. You will see a total for the cells that you have selected.

• 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 or a Numerical Count.

47. How do I group my data? Grouping your data allows you to expand and collapse data to view data at a grouped or detailed level. It does not insert totals by default. These should be added manually or by using the Subtotal functionality on the Data tab of the Ribbon.

• Select the rows or columns you want to group • Select Group on the Data tab of the Ribbon • Select Rows/Columns • Click OK Reposition by dragging grey line

Click here to collapse a specific level

Click here to expand a specific level

• To remove, select the rows or columns and then select Ungroup on the Data Tab of the Ribbon.

Self Help BI ™© | www.karabina.co.za

EXCEL TIPS & TRICKS

48. 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. • A Subtotal Dialogue will open. “At each change in“ is where Excel will create the subtotals. • “Use Function” is the type of subtotal required e.g. Sum, Average, etc.

• “Add subtotal to” refers to the data Excel will use to create the Subtotals. • Click OK

(Note : Groupings are automatically added)

• You can add additional Subtotals by repeating the process. Ensure Replace current subtotals is deselected • To remove, select Remove All

Self Help BI ™© | www.karabina.co.za

EXCEL TIPS & TRICKS

49. 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…

The new feature “Quick analysis” can also be used as shown below

Self Help BI ™© | www.karabina.co.za

EXCEL TIPS & TRICKS

• Enter the value and highlight formatting (Note: when working with a % you need to enter fractions i.e. 20%=0.2)

• Click OK

50. 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

Self Help BI ™© | www.karabina.co.za

EXCEL TIPS & TRICKS

• Click OK

51. How do I automatically add graphic 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 Scales or Icon Sets • Select the type

Self Help BI ™© | www.karabina.co.za

EXCEL TIPS & TRICKS

Data Bars

Colour Scales

Icon Sets

52. 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.karabina.co.za

EXCEL TIPS & TRICKS

53. 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 (This is optional. You can leave this blank and have no password). Remember that you will need to remember 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 deselect this (Note: this is recommended if you want to prevent the intellectual capital being copied onto another worksheet) • Click OK

54. How do I use logical functions IF, AND, OR? HINT Please check what you have as a list separator in your regional settings as this will influence whether the calculations contained in the following section appear to be working or not. If you have a semi-colon (;) as a list separator replace the comma in the formulas with a semi-colon (;). To find this setting – refer to the next page.

Self Help BI ™© | www.karabina.co.za

EXCEL TIPS & TRICKS

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 argument is not met, then the result will be FALSE. Example: (refer to the next page)

Self Help BI ™© | www.karabina.co.za

EXCEL TIPS & TRICKS

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 than 10 to qualify for a discount. OR Function

If any one argument in an OR function is met, then the result will be TRUE. If none of the arguments are met, then the result will be FALSE Example: See formula here

In the example above if the discount is TRUE or if the Top Client is “Yes” then they will 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. The Value if False is returned as the cell value Example: See formula here

Self Help BI ™© | www.karabina.co.za

EXCEL TIPS & TRICKS

If the Discount Overwrite is TRUE then give a Discount of 10%, alternatively no discount applies. Embedded Functions Once you have mastered the logical functions, you can embed them into one another so that the above three steps can be achieved in one formula as follows:

55. 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

• In the Custom Lists box, select the list applicable

Self Help BI ™© | www.karabina.co.za

EXCEL TIPS & TRICKS

Click OK This will now be the default for this column until this sort option is deleted. Even if you click the quick sort will sort by month number and not by month alphabetically.

icon it

To remove:

• Select the Custom Sort icon on the Data tab of the Ribbon • Select the Month sort by level • Select Delete Level • Click OK

56. 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

Self Help BI ™© | www.karabina.co.za

EXCEL TIPS & TRICKS

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

57. 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

Self Help BI ™© | www.karabina.co.za

EXCEL TIPS & TRICKS

• Select Scenario Manager • Select Add

• Name the Scenario, select which cells will change and enter your comment

• Click OK • Enter the values for this scenario

Self Help BI ™© | www.karabina.co.za

EXCEL TIPS & TRICKS

• 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. The figures in your grid will change in line with the scenario that you select. • If you select the Summary button, Excel will give you a summary report of the changes to the values based on all the scenarios.

Self Help BI ™© | www.karabina.co.za

EXCEL TIPS & TRICKS

58. 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

• Select Macro Recording • The Macro Record button will be permanently pinned to the Status Bar

59. How do I create a Macro and run a Macro? Macros are useful to record repeated functions such as the formatting of a Heading. Plan the steps in advance. 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 Record Macro 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)

Self Help BI ™© | www.karabina.co.za

EXCEL TIPS & TRICKS

• Click OK • Perform the steps you want recorded • Select the Stop Recording Macro button on the Status Bar

Stop recording

60. How do I assign a Macro to a button?

• Create the Macro • Insert a shape from the Insert tab

Self Help BI ™© | www.karabina.co.za

EXCEL TIPS & TRICKS

• 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

61. How do I assign a Macro to my 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 8) • Select New Group

• Rename the new group and make sure it stays selected (grey) • In the Choose commands from drop down box, select Macros

Self Help BI ™© | www.karabina.co.za

EXCEL TIPS & TRICKS

• Select anywhere in the data • Select PivotTable on the Insert tab of the Ribbon • Select a table or range for your PivotTable • Click OK

62. How do I create a PivotTable?

• Select anywhere in the data • Select PivotTable on the Insert tab of the Ribbon • Select a table or range for your PivotTable • Click OK

• A new worksheet is created linked to this data with the PivotTable framework as follows:

Self Help BI ™© | www.karabina.co.za

EXCEL TIPS & TRICKS

• Drag and drop the fields into the four Pivot areas

Self Help BI ™© | www.karabina.co.za

EXCEL TIPS & TRICKS

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

Self Help BI ™© | www.karabina.co.za

EXCEL TIPS & TRICKS

63. Creating a PivotTable using Recommended PivotTables Analysing data in your worksheet can help you with business decisions. If you are unsure of what to select in your dataset to create a PivotTable, you can look at Recommended PivotTables. These will create a few previews of your current data for you to choose from. 1.

Click Insert > Recommended PivotTables

Select the data source, table or range, for the PivotTable.

In the Recommended PivotTables dialog box, click any PivotTable layout preview, and then choose the appropriate data layout.

Click OK

Self Help BI ™© | www.karabina.co.za

EXCEL TIPS & TRICKS

64. How do I use Slicers in a PivotTable? Slicers provide a way of filtering data in a PivotTable. The PivotTable Report Filter is limiting on its own, as you cannot see what data has been used to filter on. You can overcome this limitation by using slicers.

• Select the PivotTable • Select Insert Slicer on the Analyze tab of the Ribbon • Select the field/s you want slicers for

• Click OK • Slicers can be resized, moved and formatted using the Slicer Tools Ribbon Item. • Select the items you want to see in the PivotTable

Self Help BI ™© | www.karabina.co.za

EXCEL TIPS & TRICKS

65. Create a standalone PivotChart A PivotChart no longer needs to be part of a PivotTable. PivotCharts are easily copied or moved to new sheets. Drill Down and Drill Up features are also available to navigate your PivotChart.

• Select PivotChart on the Insert tab.

• Then select the applicable option from the drop down arrow i.e. PivotChart or PivotChart & PivotTable. • Select the table or range for your PivotChart. • Click OK.

• Then select the applicable option from the drop down arrow i.e. PivotChart or PivotChart & PivotTable.

Self Help BI ™© | www.karabina.co.za

EXCEL TIPS & TRICKS

This will create your PivotChart.

An example of a PivotChart before Drill down.

An example of a PivotChart after Drill down.

Self Help BI ™© | www.karabina.co.za

Notes

Power Pivot Tips &Tricks Microsoft Power Pivot for Excel is a data analysis tool that delivers unmatched computational power directly within the application that you already know and love â&#x20AC;&#x201C; Microsoft Excel. The latest version, released with SQL Server 2012, provides many new features that will enable business and technical users to build powerful models in no time at all!

POWERPIVOT TIPS & TRICKS

1. How do I install Power Pivot and Power View in Excel 2013?

• Select FILE and then Options • Select Add-Ins from the menu and select COM Add-Ins from the drop down menu

• Click on Go

• Select the Microsoft Office Power Pivot for Excel 2013 and Power View options and click OK

Self Help BI ™© | www.karabina.co.za

POWERPIVOT TIPS & TRICKS

2. I have an Excel workbook with a Power Pivot model created in an earlier version of Power Pivot. How can I convert this model to the latest version? Ensure that you have the Power Pivot Add-In installed in Excel 2013. Open the Excel workbook with the Power Pivot Add In enabled. The model will automatically be converted to the newer version.

3. My workbook seems to freeze periodically and then returns to normal again. What could this be? One of the most likely reasons relates to the default Auto Recovery settings in Excel. Excel will automatically save the workbook every 10 minutes if you have not yet saved your work. When saving large Power Pivot models, the saving operation may take up to 10 or more seconds. Follow the steps below to change these settings:

• Click on the Excel File menu and select the Options menu item • Select the Save Excel option • Uncheck the “Save AutoRecover information every x minutes” checkbox

4. How do I create a hierarchy for my date dimension?

• Open the Power Pivot Window and select the Diagram View button on the Power Pivot Home tab

Note: Another Diagram View icon is available in the bottom right corner of the Power Pivot Window. This option can also be used.

• Hold down the Ctrl key and select the fields that you would like to include in your hierarchy • Right-click on one of the selected fields and select the Create Hierarchy option

POWERPIVOT TIPS & TRICKS

• Provide a suitable name for your hierarchy and drag the fields so that they display in the correct order

• Close the Power Pivot Window and Refresh your PivotTable • Browse to the table that contains the hierarchy, select it and view the results

5. The month names display alphabetically in my PivotTable. How can I get them to display according to calendar month?

POWERPIVOT TIPS & TRICKS

• Open the Power Pivot Window and ensure that the relevant date table is selected • Select the month name column and then select the Sort by Column icon on the Power Pivot Home tab

• In the By column, select the Month column. The Month column is a field that contains the actual month number e.g. January has a value of 1

• Click OK • Close the Power Pivot Window and Refresh your PivotTable. The month names will now be sorted in the correct order.

POWERPIVOT TIPS & TRICKS

6. Is it possible to drill down to detail from a Power Pivot PivotTable? Yes, this is a new feature in Microsoft SQL Server 2012 Power Pivot for Excel.

• In your PivotTable, click on the cell for which you would like to return the details • Select the Quick Explore icon

The PivotTable will show the details selected.

7. My Power Pivot model is quite complex. How can I provide a simplified view of this model to certain end users? This can be achieved by using Perspectives. The Perspective will also be available in Power View and in SharePoint.

• Ensure that you have enabled the Advanced tab in the Power Pivot Window • On the Advanced tab, select Create and Manage

POWERPIVOT TIPS & TRICKS

On the Perspectives pop-up window, select New Perspective

• • •

Provide a name for the Perspective and then tick the attributes and measures that you would like to be visible to the end user in this view Click on the OK button The new Perspective will be visible on the Select: drop-down

• Close the Power Pivot Window and Refresh your PivotTable • Browse to the Perspective in your worksheet and view the new simplified view displaying limited fields.

POWERPIVOT TIPS & TRICKS

Note: Perspectives should not be used as a security mechanism to hide sensitive data from end users. All Perspectives will still remain visible to end users.

8. How can I rank the data in one of my dimension tables according to a sum of a value in my fact table? In this example, the user would like to rank the States according to the number of strikes that took place in that state.

• Open the Power Pivot Window and browse to the specific dimension table • Add a new column to the dimension table and give it an appropriate name • Add the following DAX statement for the new column:

=RANKX(ALL(States), SUMX(RELATEDTABLE(FactStrike), [NoOfStrikes]))

o States: Dimension table name of FactStrike: Fact table name o NoOfStrikes: measure for which the rank will be applied

• Refresh the PivotTable and select your new field

Note: the rank in this scenario starts at 2 as there are records in the system where no state has been allocated.

POWERPIVOT TIPS & TRICKS

9. Earlier versions of Power Pivot did not allow for the formatting of measures. Does it still apply to the latest version? Significant enhancements have been made to enable measure (now called calculated fields) formatting in the latest version of Power Pivot. Follow the steps below to access the new functionality.

• In the Power Pivot window click the Calculated Fields tab and then select Manage Calculated Fields

• Select the Calculated Field that you want to format and apply the format required as shown below.

POWERPIVOT TIPS & TRICKS

10. Does the new version of Power Pivot support any form of Metadata? Yes, descriptions can be added to columns, tables and measures to store additional metadata.

• Right-click on the relevant column or table name and select Description to add your metadata

11. How do I create a timeline to filter data according to different time periods? Excel 2013 introduces a new filter mechanism, called a Timeline filter. This feature can be accessed in the following manner: • Create a PivotTable based on a cube source e.g. Analysis Services Multidimensional, Tabular or Power Pivot • The Timeline filter can be accessed from two locations: o Select the INSERT menu and Timeline under the Filter subsection o Or, if you have selected your PivotTable, select the ANALYZE menu and click on Insert Timeline under the Filter subsection

• Ensure that the DimDate table in your model contains a key column of data type Date, as the timeline will use this column to create your timeline slicer.

POWERPIVOT TIPS & TRICKS

• Select the appropriate date field • The Date filter will then be created as a separate control in the Excel workbook to slice and dice accordingly.

• • •

Use the drop down menu in the top right of the filter to select the relevant level of the hierarchy to filter on. Drag your mouse over the bars to begin slicing While the Timeline filter is selected, an OPTIONS menu will be made available to allow the user to make additional customisations or formatting changes to the control.

12. How can I quickly analyse my current table by another dimension, without changing dimensions in my PivotTable fields list?

• Click on a cell in your PivotTable (refer to the next page)

POWERPIVOT TIPS & TRICKS

• A magnifying glass will then be made available to the user (see above) • By clicking on the magnifying glass, the EXPLORE window will appear

• Select a new dimension and attribute to analyse the data by and then select Drill To. A new breakdown of the same measures will now be displayed, reflecting the new selected dimension.

13. Will Power View be available to an Excel user if I don’t have a SharePoint environment? Yes. Excel 2013 Preview includes a direct updated Power View reporting capability in the desktop client. You can view this by following the steps below:

• On the INSERT menu, select the Power View Reports option

POWERPIVOT TIPS & TRICKS

• A separate Power View sheet will be created directly inside Excel 2013.

14. What new reporting types are available in Power View for Excel 2013? Pie charts and maps have now been introduced in Excel 2013 Preview.

1. Pie Charts

o Select a measure and dimension from the Power View Fields List o On the DESIGN menu - select Other Chart and then Pie

POWERPIVOT TIPS & TRICKS

1. Maps Note: There are multiple ways to create maps. The method below is only one example.

o Select your measure and geographic field from the Power View Fields List o On the DESIGN menu, select the Map icon under Switch Visualizations tab.

Note: In certain instances your data will need to be sent to Bing in order for it to be geocoded. In others Power View will automatically identify geographic data types. For example, see the Advanced Power Pivot Table settings in the Power Pivot model.

o Now you can rearrange your fields by dragging them into the various Power View Field areas

POWERPIVOT TIPS & TRICKS

o The maps are zoom-able. Scroll in or out to see a lower/higher level of granularity

15. How do I obtain an aggregated measure in a related table?

• Ensure that a Relationship is created between your two tables • Create a new Column in your summary table e.g. Customer Sales

POWERPIVOT TIPS & TRICKS

• • • •

In the Formula Bar, type = and then select the appropriate aggregation function: SUMX, AVER AGEX, COUNTAX, MINX or MAXX Choose the RELATEDTABLE function and the relevant table where the granular measure exists Specify the relevant column containing the measure e.g. vw_FactSales[TotalSales] Press the Enter key

16. How do I hide columns from my Power Pivot Table, Chart or Power View? Columns can be hidden in the following ways:

• • • •

Open the Power Pivot window Right click on the column that you want to hide Select the Hide from Client Tools option Select the From PivotTable option

If you want to select more than one column from the client tools, select the first column. Whilst holding the SHIFT key down, select the last column and follow the steps above.

17. How do I determine the distinct count of a specific entity?

• In your Excel window in the Power Pivot tab, click on Calculated Fields and then select New Calculated Field.

POWERPIVOT TIPS & TRICKS

• Choose the table where the field will be created and specify a Calculated field Name e.g. No of orders.

• Identify the field that will be counted and then type in the formula using the DISTINCTCOUNT function • Click OK

POWERPIVOT TIPS & TRICKS

18. How can I determine when last my Power Pivot data was refreshed?

• • • •

Open the Power Pivot window Select the applicable table that you would like to see 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

POWERPIVOT TIPS & TRICKS

19. How can I create a subset of data based on rows or columns? • Add the field to Row Labels or Column Labels • Click on the Analyze 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 any unwanted items • Click on the OK button to create the set • Your newly created set will appear in the Power Pivot Field List

20. I am connected to a database and have written a SQL query to import data for one of my Power Pivot tables. How can I modify the original query?

• • • • •

Open the Power Pivot window Click on the applicable table and select the Design menu item in the ribbon Click on the Table Properties icon Click on the drop down option for Switch to, which will allow you to Edit the SQL statement Click on the Save button

Note: This will only be valid if you are connected to an underlying database and want to change your extraction query.

21. How can I determine the moving annual total for a particular measure? (Accumulated value over the last 12 months)

• • • •

Open the Power Pivot window Select the applicable table that you would like to check Click on the Design Tab Click on the Table Properties icon

POWERPIVOT TIPS & TRICKS

CALCULATE(SUM(vw_FactSales[TotalSales]), DATESBETWEEN(DimDate[Date], NEXTDAY(SAMEPERIODLASTYEAR(LASTDATE(Dimdate[Date]))), LASTDATE(DimDate[Date])))

-- Measure name -- Date column -- Start Date -- End Date

22. My database does not contain a date/time table. What is the easiest way to create my own in Power Pivot? • Open a new Excel workbook NOTE – if your Excel separator has been set to “;” instead of “,” the formulas will have to be changed accordingly. • 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 this into your Power Pivot model

23. Where can I find more information about the amount of space and memory my Power Pivot model is using? Note: Ensure that your hidden files are viewable • Open Windows Explorer -> View -> Options -> Change folder and Search Options • In the view Table, Tick Show Hidden files, folders and drives to see the full path • 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

POWERPIVOT TIPS & TRICKS

• • •

To determine the amount of memory that the Power Pivot 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 - <Dimension> <Name>vw_DimProduct</Name> <ID>902512de-3c34-485b-b811-2ba89015580b</ID> 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 Power Pivot

24. How do I add a Pivot Table/Chart to an existing sheet? Power Pivot supports a number of report layouts when creating reports from a Power Pivot model.

• Select Pivot Table on the Insert Tab on the Ribbon and then click the location where you want to insert it. This can be in an existing Worksheet or a new Worksheet.

POWERPIVOT TIPS & TRICKS

• This will allow you to create and structure a new PivotTable as per normal. • Please note: 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 Report 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.

25. How do I calculate a Percentage Contribution? • In Excel in the Power Pivot tab, click on Calculated Fields and then select New Calculated Field. • Specify a Calculated Field name e.g. Percentage Cost • Type in the following calculation: =sum(FactStrikes[COST_REPAIRS]) / CALCULATE(SUM(FactStrikes[COST_REPAIRS]); ALL(DimDate[MonthFullName]))

POWERPIVOT TIPS & TRICKS

Please note: You can only use an actual column from the table for the first entry, and not a calculated column. This technique can be used for any attribute as required.

26. How do I improve the visual impact of my charts by using a secondary axis? Excel provides a number of techniques which can be used to improve the visual impact of charts and graphs. A standard line chart which compares two measures over time, such as No. of orders and Last 12 Months would start by looking very similar to the screenshot below:

POWERPIVOT TIPS & TRICKS

• These two measures are different by orders of magnitude, therefore the Last 12 Months is causing the No. of Orders to be hidden. • Right Click on the No. of Orders line, and select Format Data Series • Under Series Options, select Plot Series on Secondary Axis

• This allows the No of Orders to be viewed alongside the Last 12 Months Amount.

POWERPIVOT TIPS & TRICKS

27. How do I improve the visual impact of my charts by using 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 Last 12 Months line, and select Format Data Series • Under Line Style, select the Smoothed Line option.

• This can be done for all lines where necessary.

POWERPIVOT TIPS & TRICKS

28. How do I improve the visual impact of my Charts by editing the borders and fill options?

• Right Click on the chart area, and select Format Chart Area. • Under Fill, Select Gradient Fill • Under Border , Select Rounded Corners

POWERPIVOT TIPS & TRICKS

29. How do I improve the visual impact of my charts by using mixed chart types?

• Right Click on the No of Orders line and select Change Series Chart Type • Select a relevant Column/Area Chart Type

30. Where did my Field List go and how can I get it back? Power Pivot 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.

POWERPIVOT TIPS & TRICKS

To display the Power Pivot Field List ensure that the Field List button is selected in the Analyze PivotTable Tools Ribbon.

31. How do I display a related column in a table?

• Ensure that a Relationship is created between your two tables • Create a new Column in your detail table e.g. Country Name

POWERPIVOT TIPS & TRICKS

• In the Formula Bar, type the following expression RELATED(DimSalesTerritory[SalesTerritoryCountry])

32. How do I reference a particular Dimension member value? • • •

In Excel in the Power Pivot tab click on Calculated Fields and then select New Calculated Field Specify a calculated field name e.g. AdventureWorks Sales Type in the following calculation: CALCULATE(SUM(FactResellerSales[SalesAmount]),DimProduct[EnglishProductName]= ”HL Mountain Frame - Black, 42”)

Note: You can only use an actual column from the table for the first entry, and not a calculated column. This technique can be used to reference any Dimension member and measure.

POWERPIVOT TIPS & TRICKS

33. 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 e.g. 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 Excel in the Power Pivot tab click on Calculated Fields and then select New Calculated Field Specify a Calculated Field name e.g. Percentage of Mountain Bike Frame Sales Type in the following calculation: Sum(FactSales[SalesQuantity])/CALCULATE(Sum(FactSales[SalesQuantity]), DimProduct[BrandName]=”Litware”)

POWERPIVOT TIPS & TRICKS

34. 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 Excel in the Power Pivot tab, click on Calculated Fields and then select New Calculated • We can then use a combination of the CALCULATE function and the LASTDATE function as follows: o = CALCULATE(SUM(FactProductInventory[UnitsBalance]),LASTDATE(DimDate[FullDateAlternateKey])) • Sum(FactSales[SalesQuantity])/CALCULATE(Sum(FactSales[SalesQuantity]), DimProduct[BrandName]=”Litware”)

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.

POWERPIVOT TIPS & TRICKS

35. How do I add my existing Excel data to Power Pivot? • • •

Select all rows and columns on the dataset that you would like to add. Make sure that each column has a descriptive name. On the Home Tab in the Excel Ribbon, click on Format as Table. Choose any style that you like. Click on the Design Tab, and give the newly created table a meaningful name.

• Click on Power Pivot and select “Add to Data Model”. This will open up Power Pivot and add your table to the model automatically.

Notes

Microsoft Power BI Bonus Tips The Microsoft Power BI toolset is designed to empower business users with the ability to interactively and dynamically create mash-ups of data, to analyse this information and then to create stunning visualisations that can easily be shared with and presented back to business. The BI toolset allows IT to balance employeesâ&#x20AC;&#x2122; need for rich, interactive information and collaboration with the need to manage the safety and confidentiality of information. Power Pivot, introduced in SQL Server 2008 R2 and enhanced in SQL Server 2012, enables users to get deep into their data, allowing them to explore it and to turn it into invaluable information. This information can then be used to explain the data not only to themselves, but to others as well. Once they have grasped the information, they can surface it using the striking visualisations available to them in Power View. Power View allows for the creation of interactive, dynamic visualisations that come alive on your screen, allowing for further information exploration and explanation. Power View works hand-in-hand with Power Pivot to project interactive insights to end-users, allowing them to see changes in their data over time and enabling them to mould the information into something that makes sense to them. Many times users want to integrate information not only from their internal data systems, but also from resources available on the internet. This is where Power Query can open up a whole new world to them. Power Query allows users to search the internet using English-language. Please note:The Power Query and Power Maps add-in options only run on Office 2013 Professional Plus and O365 ProPlus. For more information, please visit (http://office.microsoft.com/en-us/excel/ power-bi-download-add-in-FX104087144.aspx).

MICROSOFT POWER BI BONUS TIPS

PowerView Tips 1. How do I add a slicer to my PowerView report?

• • • •

Open your PowerView report Ensure that you click on a blank space in your report Select the field that you would like to add as a slicer. A new table will be created on your report If you selected a numeric value such as Year, then set its properties to “Do Not Summarize”

• On the Design tab, click on the “Slicer” option. This will transform your selected field into a slicer.

2. I’ve added an item to my PowerView report, however I am unable to move it or click on one of its members. How do I fix this? This is most probably due to another report item overlapping your newly added item. To fix this: • Right Click on the item that seems to be causing the issue • Highlight the “Send to Back” menu option and select either “Send to Back” or “Send Backward”

MICROSOFT POWER BI BONUS TIPS

• See if you can move or select your newly added report item. If not, repeat the steps above on some of the other report items.

3. Is there a way to make the analysis of my data over time more appealing other than having to filter on my data year by year or month by month? Definitely. Your Power Pivot graphs have a very useful setting called “Small Multiples”. Here is how to use “Vertical Multiples”: • Alter your existing report in such a manner that your months are displayed on the X-Axis • Click on your report and then drag your Calendar / Fiscal year field into the empty space under “Vertical Multiples”

MICROSOFT POWER BI BONUS TIPS

• • •

Click on the graph, and then click on the Layout tab on the Excel ribbon Under the “Grid Height” and “Grid Width” drop downs, select 3 Resize your graph so that all values are displayed clearly. The time analysis of your data should now be a lot more visually appealing

PowerQuery Tips 1. Is there a way to rapidly pull information into Excel from the internet without leaving Excel or copying the information from my web browser? Most certainly. Using Microsoft’s Power Query you can quickly and easily extract information from the internet by either asking a question or browsing a website’s content directly. Here is how to browse a website such as Wikipedia’s content:

• • • • •

Install the Power Query Excel Add-In Once installed, open up Excel Click on the Power Query tab Click on the From Web button Enter the URL that you would like to query

MICROSOFT POWER BI BONUS TIPS

• Click on OK • Power Query will extract data tables from your selected URL, and will then display each data table found as seen below

• Select which table you would like to import, and then click on Done. Power Query will import the data directly into Excel. It will also allow you to Load the data into a new or existing Power Pivot model, as show below:

MICROSOFT POWER BI BONUS TIPS

2. The data that I imported from the internet seems to be pivoted. Is there a way to unpivot the data using Power Query? • If you have already imported the information you need, then select the PowerQuery table and click on the “Filter & Shape” button found under the Query Settings tab

• Right click on the desired column, and select “Unpivot Columns”

MICROSOFT POWER BI BONUS TIPS

PowerMap 1. I’ve heard of PowerMap for Excel. What is it and how can I use it with my data? PowerMap Preview for Excel 2013 allows you to plot geographic and temporal data visually, analyse that data in 3D and create interactive tours to share with others. This preview gives you an early look into the new features that provide 3D data visualisation for Excel and a powerful method for people to look at information in new ways, enabling discoveries in data that might never be seen in traditional 2D tables and charts.

2. What type of data would I need in order for me to take advantage of PowerMap? Ideally your data should contain longitude and latitude information; however we would recommend that you have at least one or more of the following items. The more information you have the better Power Map will be able to plot your information on Bing Maps:

• • • •

Country Name State / Province Name Postal Code Street Address

MICROSOFT POWER BI BONUS TIPS

3. What are the different visualisations available in Power Map? There are currently three ways to visualise your data:

• Column Charts

• Bubble Charts

100

MICROSOFT POWER BI BONUS TIPS

• HeatMaps

101

Notes

BREAKTHROUGH INSIGHT

Unlock new insights with pervasive data discovery across the organisation. Rapid data exploration and visualisation Managed self-service BI Credible, consistent data Scalable analytics and data warehousing

â&#x201E;˘

Advance your BI Career

academy

Join our world class BI practitioners in their uniquely tailored Business Intelligence Learning Paths to help develop your BI Career Karabina Academy provides training, mentoring and coaching by experienced professionals in scarce skills such as Business Intelligence, Collaboration and Customer Insight. We assist customers to secure their investment in Business Technology Solutions by enabling people to work more effectively within these environments.

+27 11 463 8155 www.karabinaacademy.co.za info@karabina.co.za

Overview 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 Power Pivot, Power View, Power Query and Power Maps. Users have the ability to model data, turn it into information and visualise the results in new ways with Slicers and enhanced graphing with massive amounts of data from many sources.

Karabina 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 2013 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.