How to create Slicers which are the New Visual way to Filter a Pivot Table

Microsoft Excel can be used to carry out a number of functions such as sorting, filtering, and subtotal to manage large lists of data. Yet, its MS Excel PivotTable is a very useful feature when it comes to analyzing all that data and doing it quickly. Its use is all the more pronounced when the user is required to quickly create a compact summary report (based on lots of data) without having to write complex formulas or rely on lengthy techniques.

Being very versatile, MS Excel PivotTable is often considered Excel’s best analytical tool because it complements speed with amazing flexibility and dynamism, by which the data interrelationships that are being viewed can be changed. It is so practical that its features can be put to practical use by actually getting down to work on it, even if a user doesn’t go through the instructions on the printed page, which is a visually-oriented feature based on displaying fields in different locations. With MS Excel PivotTable, it is possible to create a complete summary report with heaps of data in very little time without having to write complex formulas and rely on obscure techniques.

Thorough learning about all the features and aspects of PivotTable from the MS expert

A webinar that is being organized by Compliance4All, a leading provider of professional trainings for all the areas of regulatory compliance, will offer complete learning on the numerous PivotTable capabilities and its many tools and features.

The speaker at this session is Dennis Taylor, an Excel expert who has worked extensively with Microsoft products (especially spreadsheet programs) since the mid-1990’s and has taught hundreds of workshops and authored numerous works on this program. Please register for this webinar by visiting best ways to create PivotTables and gain complete insights into the ways by which to put the full range of functionalities of MS Excel PivotTable to use.

PivotTable simplified

Teaching participants the quickest and best ways to create PivotTables and Pivot Charts is the main objective of this webinar. These include the following capabilities:

o  How to compare two or more fields in a variety of layout styles

o  How to sort and filter results

o  How to perform ad-hoc grouping of information

o  How to use Slicers instead of filters to identify which field elements are displayed

o  How to drill down to see the details behind the summary

o  How to categorize date/time data in multiple levels

o  How to create a Pivot Chart that is in sync with a PivotTable

o  How to add calculated fields to perform additional analysis

o  How to hide/reveal detail/summary information with a simple click

o  How to deal with dynamic source data and the “refresh” concept

o  How to create a PivotTable based on data from multiple worksheets.

All these aspects will be covered in detail. Dennis will also cover the following areas at this session on MS Excel PivotTable:

o  Pre-requisites for source data – preparing data so that it can be analyzed by PivotTables

o  Creating a PivotTable with a minimum number of steps, including the Recommended PivotTables option

o  Manipulating the appearance of a PivotTable via dragging and command techniques

o  Using Slicers to accentuate fields currently being shown (and which ones are not)

o  Using the new (in Excel 2013) Timeline feature

o  Creating ad hoc and date-based groupings within a PivotTable

o  Quickly create and manipulate a Pivot Chart to accompany a PivotTable

Apart from MS Excel users who are familiar with PivotTable concepts, but need expanded techniques to analyze lists of data; anyone needing to know how to create PivotTables from multiple sources and use Slicers, Timelines, Calculated Fields, and Conditional Formatting will also benefit from this course.

For Depth info

Advertisements

MS Excel can be a wonderful tool for carrying out umpteen functions

Mastering MS Excel formulas and functions can make a miracle out of this program. When used optimally, MS Excel can be a wonderful tool for carrying out umpteen functions and optimizing work related to a number of departments. For example, the Accounts Department can do a number of important functions such as loan repayment calculation, generating a profit and loss statement, solve complex mathematical and engineering problems, and carry out anything that involves addition, subtraction, multiplication and other functions with utmost ease.

functions-and-formulas-of-ms-excel-2-638

 

This is because MS Excel has a number of built-in tools. If a user knows how to use these; it could be a real blessing, because it carries out a number of complex tasks at the touch of a button. The user of MS Excel only needs to know how to use these functions. The features range from simple sum and average to the IF and VLOOKUP functions.

Goes beyond calculating and arriving at data

The major advantage of maximizing the use of MS Excel is that it saves lots and lots of time for the user. Be it a student or an accounts executive; MS Excel helps them to make the best use of time, as they will be required to spend a lot lesser time on functions since MS Excel does it for them.

Calculation of and arriving at data is not the only function MS Excel is capable of carrying out. Calculating someone’s exact age in days, combining text items from several cells into a single cell or converting a list of lower case text items to capital letters are just some of these. And what is MS Excel’s capability in relation to creating financial data and models? It is perfectly useful here, too.

functions-and-formulas-of-ms-excel-10-638

Discover the ways of optimizing the use of MS Excel

Want to understand how to make MS Excel work magically for you? Then, a webinar from Compliance4All, a leading provider of professional compliance for all areas of regulatory compliance, will show you how. The speaker at this extremely useful session is none other than Mike Thomas, a subject matter expert in a range of technologies including Microsoft Office and Apple Mac, who has worked in the IT training business since 1989.

From the time Mike founded theexceltrainer.co.uk; he has produced nearly 200 written and video-based Excel tutorials. To be able to make your MS Excel a much more efficient program and help it save you enormous time and resources; please get the most out of the learning from this webinar by enrolling for it at Creating complex functions the easy way

1ec5ef5b-96a1-4353-96c2-c9a26ad2adc5This session is ideally suited for any Excel user who needs to go beyond the basics of using formulas or simply wants to become more comfortable and productive in using Excel formulas and functions. Any Excel user who deals with large lists needs these tools and techniques to effectively manage the lists and become more productive. This program can be a good fit for anyone who is above the entry level.

Mike will cover the following areas at this webinar:

  • Creating formulas to perform mathematical calculations
  • Copying formulas and the difference between absolute and relative references
  • Assigning names to cells and using them in formulas
  • Using functions to combine text strings from multiple cells
  • Using functions to change text from lowercase to uppercase and vice versa
  • Using functions to perform calculations on dates and times
  • Using the IF function to automate data entry
  • The VLOOKUP function
  • Creating complex functions the easy way.

Learn how to future-proof VLOOKUP by using Excel’s Table feature versus referencing static ranges

Professionals in a wide number of industries and businesses find the VLOOKUP function in Excel very useful. Why this is so is that it helps in locating an exact value. Using VLOOKUP, an MS Excel user can find a specific piece of information in the spreadsheet. This is very useful when there are very many values in a grid. In other words, the VLOOKUP function is ideal for locating an exact one from among a number of values. Using VLOOKUP, users can return data from other locations in a worksheet.

An indication of the functions that VLOOKUP performs can be had from its expansion, which is Vertical Lookup. It works by searching for the specific item in a vertical manner. As opposed to normal values that can be performed manually; VLOOKUP function helps users to go further by enabling them to perform more complex tasks such as unit sales for a period of time at a certain price or its variables.

If the user wants to calculate the unit sales of say, a clothing material from one date to another at a certain price; VLOOKUP is extremely handy. This can be expanded to any number of brands sold by the business for any date or unit price, which is why this function is considered very useful.

We can understand the VLOOKUP function in MS Excel by drawing a parallel with “Control F”, or the “find” function in MS Word. In MS Word, it is possible to use this function to go to the exact word, whereas using VLOOKUP; the user can get down to much more, like the entire object’s associated values.

Get professional help to understand VLOOKUP better

An exploration of this function is best made with expert help. At a webinar that is being organized by Compliance4All, a leading provider of professional trainings for all the areas of regulatory compliance; Excel expert David Ringstrom, CPA, will explain the way in which this program can be tweaked. David Ringstrom is a nationally recognized instructor who designs and teaches Excel courses that are based on over 25 years of consulting and teaching experience. To gain the benefit of experience that David brings into MS Excel, please register for this webinar by logging on to http://www.compliance4all.com/control/w_product/~product_id=501297LIVE?Wordpress-SEO

What makes this presentation different is that the presenter will demonstrate every technique at least twice: first, on a PowerPoint slide with numbered steps, and second, in Excel 2016. He will explain the differences in Excel 2013, 2010, or 2007 both during the presentation as well as in his detailed handouts. David will also give participants an Excel workbook that will include nearly all the examples he will be using during the webinar.

Alternatives to VLOOKUP do exist

However, using VLOOKUP isn’t always the most efficient approach. Hence, David will explain alternatives such as the INDEX and MATCH, SUMIF, SUMIFS, SUMPRODUCT, IFNA, and OFFSET functions. He will explain the limitations of VLOOKUP. In addition, he will also help participants understand the ways of future-proofing VLOOKUP by using Excel’s Table feature versus referencing static ranges.

One of the major gains of this learning session is that the participants will be able to improve the integrity of their spreadsheets with Excel’s VLOOKUP function. In addition to helping participants learn the ways of performing dual look-ups; the speaker will also help them get clarity on the user actions that can trigger #REF! errors. Participants will be able to use the IFERROR function to display something other than an #N/A error value when VLOOKUP can’t find a match.

He will cover the following areas at this webinar:

o  Future-proofing VLOOKUP by using Excel’s Table feature versus referencing static ranges

o  Using the SUMIF function to summarize data based on a single criterion

o  Learning why the INDEX and MATCH combination often is superior to VLOOKUP or HLOOKUP

o  Using the MATCH function to find the position of an item on a list

o  Using the IFERROR function to display something other than an #N/A error value when VLOOKUP can’t find a match

o  Improving the integrity of spreadsheets with Excel’s VLOOKUP function

o  Learning about the IFNA function available in Excel 2013 and later

o  Using the SUMIFS function to sum values based on multiple criteria

o  Using VLOOKUP to perform approximate matches

o  Seeing what types of user actions can trigger #REF errors

o  Performing dual lookups, which allow you to look across columns and down rows to cross-reference the data you need.

Time-saving tips on MS Excel usage

MS Excel is a program that has an amazing number of features that make it highly capable and suited for a number of usages. Although it has innumerable features; they come with simple shortcuts and methods. These are not always visible to the casual user. The use of these shortcuts is of tremendous use to users, because it saves them precious time, which can be put to more valuable uses. Employees who know the secrets of MS Excel are certainly likely to be more productive.

A webinar that is being organized by Compliance4All, a leading provider of professional trainings for the areas of regulatory compliance, will explain the ways by which the use of MS Excel can be optimized. The speaker at this webinar is Dennis Taylor, an Excel expert who has worked extensively with Microsoft products, especially spreadsheet programs, for over two decades. Gaining learning from this breezy webinar is simple: Just visit http://www.compliance4all.com/control/w_product/~product_id=501020LIVE/ to enroll.

An amazing number of shortcuts and tips

Dennis will help participants significantly enhance their productivity at work by offering valuable tips on how to make the best use of Microsoft Excel by offering guidance on the use of the right shortcuts, whether it is a keystroke shortcut or a hidden command sequence.

At this session, Dennis will present an amazing 100+ Excel shortcuts, many of them keystroke shortcuts and many of them dragging techniques not involving command sequences. He will explain how to differentiate between shortcuts when appropriate. He will explain these separately in relation to Excel versions 2016, Excel 2013, and Excel 2010.

The interesting part of this webinar is that these productivity tips, shortcuts, and accelerator tools will be presented throughout the session using Excel worksheets and workbooks that are based on real-life data, which will be made available to all attendees.

Dennis will cover the following areas at this session:

o  Navigate seamlessly through workbooks and worksheets with keystroke and mouse shortcuts

o  Copy or move data with simple dragging instead of multi-step command sequences

o  Display/hide all worksheet formulas instantly; select all formula cells with two mouse clicks

o  Use keystroke shortcuts for a various number formats

o  Build lists of dates, times or values without using time-consuming commands

o  Create charts instantly and learn manipulation tips

o  Create formulas faster with entire column references

o  Master over 100 tips to make you a Power User!