Microsoft_Excel_2013_logo.svg

Pivot Table Basics

Pivot Tables are some of the most flexible and powerful tools you can build with Excel. But if you’ve never worked with Pivot Tables before, you may be wondering what you can actually use them for. In this article, we’ll cover some of the most popular ways to use Pivot Tables. Once you know when to use a Pivot Table, you can learn how to build one.

1. Use a Pivot Table to Summarize Data

At their most basic level, Pivot Tables are used to summarize data. Especially with larger data sets, Pivot Tables are very good at allowing you to slice and dice your data anyway you need. Regardless of whether you have a list of scores and need to determine the percentage of your class that obtained A’s, B’s, etc. or you have a company’s salary data and want to determine what the average pay for each office location is, Pivot Tables can allow you to do all of this and more. Once you have the basics down, you can also learn how to use a Pivot Table to create more complicated summaries or run more advanced calculations.pivot table joke

2. They Come as Both Tables and Graphs

Pivot Tables also have a very flexible layout. You can either structure your summaries as traditional tables or you can summarize your data by way of a graph. You can even create a linked table and graph, where any changes in the table are immediately reflected in the graph. In this regard, Pivot Tables are very flexible and allow you to structure them to whatever your presentation needs are.

3. A Pivot Table Can Count, Add, Divide, and More!

The number of ways that you can summarize your data is almost endless. Whether you need a straight-forward count of the number of people who work for a particular manager or you need to perform some sort of mathematical function on your data (add, subtract, divide, multiply, etc.), a Pivot Table can accommodate all of these needs. You can also create percentiles with your data and calculate the percentage of people at your company who qualified for a particular bonus level. All of this is possible with a few quick clicks in your Pivot Table.

Get even more free tips and tricks when you signup for our free email newsletter. You’ll receive free Excel tips and tricks directly in your inbox and also receive exclusive discounts to our upcoming training classes.

Microsoft_Excel_2013_logo.svg

3 Reason Why You Should NOT Share an Excel Workbook

As a follow-up to our last post on why you should Share an Excel workbook, this post will cover some of the main reasons why you should not Share an Excel workbook. Sharing is a very valuable function in Excel, but in some situations, it is better to simply turn the Sharing off. While Sharing does open up many opportunities for collaboration with your team, it also restricts many key functions in Excel.

1. You Need to Insert an Object

One of the shortcomings of the Sharing function is that it disables many critical functions. You’ll find that once you share an Excel workbook, most of the options on the Insert Tab are disabled. So, this means that you can’t do things such as insert shapes, pictures, or even hyperlinks. In effect, when you Share an Excel workbook, your options are limited mostly to simply entering data into the spreadsheet. If you need to insert an object in a Shared workbook, you will first need to disable Sharing, insert your object, and then re-enable Sharing.

2. You Need to Create a Pivot Table

Creating Pivot Tables (or Charts) is also disabled when you Share an Excel workbook. You will still be able to view and manipulate Pivot Tables that you created before you enabled Sharing. However, you won’t be able to create any new ones. So, before you Share an Excel workbook, you need to make sure that you don’t need to create any additional Pivot Tables.Why not to share an Excel Workbook

3. You Need to Import Data

Another function that is disabled when you Share an Excel workbook is the suite of Data Import options. Once the file is Shared, you won’t be able to pull data into Excel directly from text files, databases, the Internet, etc. You will still be able to use the Copy/Paste functions, however, depending on the type of data that you’re working with, this may create more problems than it solves. So, if you have a large volume of data that you need to pull into a spreadsheet, import the data before you enable Sharing.

When You Share an Excel Workbook, We’ll Share Our Knowledge!

Now that you know the pros and cons of Sharing an Excel workbook, you can continue to expand your Excel knowledge by signing up for our free email newsletter! You’ll get free Excel tips and tricks directly in your inbox and also receive exclusive discounts on our training classes.

Microsoft_Excel_2013_logo.svg

Make an Excel Pivot Table in 2 Steps

Few skills are more important to master than the ability to create an Excel Pivot Table. If you know how to create an Excel Pivot Table, you can quickly summarize and analyze large datasets. You can also create dynamic tables and charts in order to quickly see different views of your data. Luckily, creating an Excel Pivot Table is pretty simple and can be accomplished in just two steps, allowing even the newest of Excel users to start creating complex data analysis.

Step 1: Select the Data You Want to Pivot

The first step is to simply define the data you want to include in your Excel Pivot Table. So, just highlight the data you want to include in the pivot, as in the example below:

Excel Pivot Table Selection

Step 2: Create Your Excel Pivot Table!

Then simply go up into the Insert Tab and select PivotTable, as in the example below:

Insert Excel Pivot Table

Just click “Ok” on the next screen, and you should get something that looks like this:

Excel Pivot Table Final

You have successfully created a Pivot Table! Now, you are ready to add fields and filters and begin analyzing your data! What you have created is the skeleton for a basic Pivot Table. Once you have a better understanding of how to arrange fields and filters in the table, you can create advanced (and dynamic) data analysis projects. With further training, you can also learn how to incorporate Pivot Charts into your Pivot Table reports. Pivot Charts and Pivot Tables function similarly and are extremely valuable if you need to model your data graphically. Excel Pivot Tables are extremely powerful data analysis tools if you know how to use them. The best way to start learning more about them is to simply find a reliable resource on the internet and sign up for some training classes.

To get started, start by signing up for our Desktop Secrets Newsletter, where you’ll receive Excel tips and tricks directly in your inbox in addition to exclusive discounts on our upcoming training classes.

Microsoft_Excel_2013_logo.svg

3 Excel Date Functions Everyone Should Know

Knowing how to use a few of the many available Excel date functions is a useful skill if you have to do any sort of time tracking or reporting. Aside from using proper number formatting, it is also important to know how to use a few of the core Excel date functions. Knowing how to use these date functions will enable you to analyze date/time data in increasingly interesting and more complicated ways. If you learn the three Excel date functions outlined below, you will be well on your way to becoming an Excel power user.

1. Get Started with Excel Date Functions TODAY!

The TODAY and NOW Excel date functions are particularly useful if you need a dynamic function to automatically calculate the date and/or time every time you open or refresh a spreadsheet. The only difference between these two functions is that TODAY will simply return today’s date, while NOW will return the current date and time (calculated using the time settings on your computer). To use either of these functions, simply enter =TODAY() or =NOW() into a cell on your spreadsheet. There is no need to put anything inside the brackets of the function.

2. Determine Working Days With NETWORKDAYS

The best Excel date function to use to calculate the number of working days between two dates is NETWORKDAYS. The syntax is =NETWORKDAYS(Start-Date,End-Date,Holidays). You can enter the dates manually into the formula, but they have to be formatted using Excel’s special serial date formatting. It’s much easier to simply put your dates into two different cells and use cell references to pull them into the formula:

Excel Date Functions

Note that the exclusion of holidays from your calculation is optional. Any dates listed in the holidays section of the formula will be excluded from the working days count.

3. Determine a MONTH/DAY/YEAR or HOUR/MINUTE/SECOND

If you’re presented with a date, and you only need to pull out the particular month of that date, the easiest way to do that is with the MONTH Excel date function. If you use =MONTH(Date), Excel will return the numerical value of the month in the date you have indicated, as in the example below.

Excel Date Functions

The DAY/YEAR/HOUR/MINUTE/SECOND functions all work the same way. These functions are useful if you need to break out the data by segments (day, year, etc.) and are particularly helpful in Pivot Table filters when the standard date filters aren’t feasible.

Put Some Training on Your Calendar

This only scratches the surface of what you can do with Excel date functions. There are many more applications that will enable you to perform increasingly complex data analysis with Excel. To get started, signup for our free email newsletter, where you’ll get Excel tips and tricks directly in your inbox. You’ll also get exclusive discounts for our training classes and webinars.

Microsoft_Excel_2013_logo.svg

How to Land an Entry Level Business Analyst Job

Entry level business analyst jobs are not easy to obtain. However, the starting salary for an entry level business analyst is well worth the effort. The easiest way to gain an edge on the other job candidates is to build your Excel skills. In addition to learning how to use Excel, knowing how to showcase what you’ve learned is key to landing an entry level business analyst job. Follow our three tips below and, you’ll be well on your way to earning a spot at a Fortune 500 Company near you.

1. Make Your Resume Excel

As an applicant for an entry level business analyst position, you probably already have a well-refined resume. However, you don’t want to overlook the importance of including your Excel experience on your resume. When skimming through resumes, many employers look for key indicators such as the ability to create pivot tables or write macros. Try to avoid using such generic descriptors as “Advanced Excel User” or something similar. Don’t tell your potential employers that you know how to use Excel, show them! If you’ve used Excel in prior jobs or in projects for school, be sure to include that in the prior experience section of your resume. Including a sentence such as “Utilized Pivot Tables to analyze and slice data in order to prove…” will go a long way towards showing that you know how to use Excel and impressing the hiring manager for your entry level business analyst role.entry level business analyst

2. Get Certified!

In line with Tip #1 above, an easy way to demonstrate your Excel prowess is to obtain the official Microsoft certifications. The benefit of earning an Excel certification is two-fold. First, in the process of earning the certification, you will learn all about the core features of Excel and how to use them effectively in your everyday work. Then after you’ve earned the Excel certification, simply listing the certification on your resume will quickly and easily demonstrate your advanced Excel knowledge to prospective employers. Excel knowledge is key to landing an entry level business analyst position. Earning an Excel certification is an easy way to both obtain that knowledge and showcase it.

3. Build on Your Existing Knowledge

Whether you’re trying to land an entry level business analyst position or any other job, you should always look for ways to improve your existing skill set to remain competitive in whatever field you go into. Because Excel is such a complicated program and it has so many different uses, there is always something new you can learn about the program. So, whether you’re just starting out, or you were born with a spreadsheet in your hand, you can always learn something new.

Build Key Skills for an Entry Level Business Analyst Job for Free!

A great way to learn more about Excel is to sign-up for our free email newsletter. You’ll get valuable Excel tips right in your inbox, and you’ll also receive exclusive discounts to our classes and webinars!

Microsoft_Excel_2013_logo.svg

Five Advanced Excel Skills Every Job Applicant Should Know

Find a new job with advanced Excel skillsAdvanced Excel skills are among the most commonly requested job skills hiring managers request of new employees. Many times, a job posting will simply ask for “Advanced Excel Skills” among the list of job requirements. What are these skills? What does it take to become an advanced Excel user? What follows is a list of key Excel skills that every advanced user should know.

1. Pivot Tables

Pivot Tables are among Excel’s most mysterious functions. Yet, learning how to use them is fairly straight-forward. Pivot Tables simply summarize data, and they provide you with additional functionality to present your data in almost any way you can imagine. Want to quickly get a count of how many employees work in each department in your office? Create a Pivot Table. Want to figure out what the average performance score is across several different work units? Create a Pivot Table. Need to analyze a complex report with dozens (or hundreds) of data columns? Create a Pivot Table. Pivot Tables are flexible and dynamic, which makes them extremely useful in the business world. Knowing how to create Pivot Tables is one of the most crucial advanced Excel skills you can learn. Become a master of Pivots, and you’ll be sure to impress any hiring manager.

2. VLOOKUP Function

If Pivot Tables are used more frequently than any other Excel feature in business offices every day, VLOOKUPs are a close second. Whether you’re validating financial reports or simply summarizing and analyzing data, you’ll probably use the VLOOKUP function several times in the course of your daily work. The real power of the VLOOKUP function lies in its simplicity, enabling you to quickly find and pull the exact data you need out of large data sets. Once you learn how the VLOOKUP works, you’ll find an increasing number of opportunities for its application in your everyday work. Along with Pivot Tables, VLOOKUPs are a key advanced Excel skill. If you know nothing else, knowing how to create Pivot Tables and use VLOOKUPs will put you ahead of most applicants in the job market.

3. Paste Special

Paste Special is another key advanced Excel skill. Any job that involves Excel (which will be most jobs) will involve moving data around. Paste Special allows you to unlock the power of the Cut/Paste functions. Maybe you only need to copy the formatting of a cell and not its content, or maybe you need to copy the output value of a formula without copying the formula itself. Paste Special allows you to do all of this and more! Everyone knows how to cut and paste, very few know how to use Paste Special.

4. Macros

Knowing how to automate tasks with code will set you far and above the competition when you apply for jobs. In Excel, you can do this with Macros, and you don’t even need to know anything about code to get started! Learning how to use Macros will enable you to write code that will automatically complete repetitive tasks for you. So, if you need to run reports every week (or every day), you can write some Macros to automate things such as the formatting and even some of the analysis. This will enable you to complete your work faster and more consistently. Learn how to use Excel to write a few simple Macros, and you will be able to impress hiring managers with your ability to fly through work assignments using your advanced Excel skills.Creating powerful charts is an advanced Excel skill

5. Charts

One of the final advanced Excel skills that will set you apart from the competition is your ability to design useful and informative charts. Anyone can create a chart (or graph). It is much more difficult to create a chart that provides a useful insight into some data. Knowing how to stack charts on top of each other and how to use both the primary and secondary axis are advanced Excel skills that make a new employee very valuable. If you intend to do any sort of data analysis, knowing how to create dynamic and informative charts is crucial.

Advance Your Job Search with Advanced Excel Skills

Knowing how to use Excel is an important part of most jobs you will apply to. Having a set of advanced Excel skills, like those discussed above, will set you apart from the legions of applicants you are competing against. If you master these skills, you will stand apart and above most of your competition.

Join our mailing list to learn how to use these and other advanced Excel skills. You’ll get free tips directly in your inbox that will help you become an Excel master, and you’ll also receive exclusive discounts to our training classes.