How to Import Excel Data into Microsoft Access

How to Import Excel Data into Microsoft AccessWatch this video on YouTube In this Microsoft Access tutorial, you’ll learn how to import data from a Microsoft Excel worksheet and create a table from it in an Microsoft Access database. To learn more about Microsoft Access, …

Excel – Hide Records with Zeroes in Pivot Table Calculations

One of the most asked questions of beginning, and sometimes seasoned, pivot table users is “How do I hide the entries in a pivot table whose totals equal zero?” On first blush, this seems like an easy feat, but users quickly discover that it’s not as easy as predicted. There are ways to sort the source data and then exclude the entries with zero values, but that task of sorting and filtering the source data would have to be performed each time the source data is updated. This is not an appealing prospect.

There is actually a very easy way to not display pivot table records that equal zero.

Excel Hidden Camera Tool – Great for Dashboards

If you are an Excel user who likes to create charts, design dashboards, or just likes to play with neat toys in Excel, this tutorial is going to be right up your alley.

Excel contains (in a super-secret place) a hidden camera. “But why would I need a hidden camera in a spreadsheet program?” I’m glad you asked. If you have ever created a chart on one sheet, but you need the chard displayed simultaneously on a different sheet, and you don’t want to make two of the same thing, the camera tool will solve this problem.

First thing’s first; we have to find the camera before we can put it to creative use.

12 Excel Keyboard Shortcuts for Every User

Keyboard shortcuts are a great way to improve the speed at which documents are built, regardless of the application. It seems like there is a keyboard shortcut for just about every feature Excel contains; and there may be that one guru in the office that knows them all. But most of us fall somewhere between Guru and Labrador retriever (hopefully, closer to the former.)

The good news is that it’s not an “all or nothing” proposition when it comes to keyboard shortcuts. Knowing just a few of the most productive keyboard shortcuts will serve you far better than knowing none at all.

So let’s get this show on the road!

CTRL+SHIFT+L – Turn On/Off Filter Controls

Filters are of tremendous use when analyzing large numbers of records in a table, but you are only interested in a select set of records that met a specific criteria. Activating your filters is just a CTRL-SHIFT-L away. This keyboard can also be used to turn off all of the filters and display the entire list. (Filters are on by default when you convert a straight table to a Data Table, and not always desired.) Finally, if you hit the “L” key twice (CTRL-SHIFT-L & L) you can effectively clear the current filters to start fresh with a new filter query.

How to Subtract Dates in Microsoft Excel using DATEDIF

Subtract Dates in Excel using the DATEDIF FunctionWatch this video on YouTube In this tutorial, you’ll see how to subtract two dates from one another in Microsoft Excel using the DATEDIF function. The DATEDIF function allows you to calculate the difference between two dates and …

How to Create a Crystal Report using Microsoft Excel Data

Create a Crystal Report using Excel DataWatch this video on YouTube In this tutorial, you’ll see how to create a report in Crystal Reports using data from an excel worksheet. This video will demonstrate the different methods for connecting to Microsoft Excel data from Crystal …

Excel – Conditional Formatting with Subtotals

If there has ever been a more “You’ve got your chocolate in my peanut butter!” moment, it’s the blending of Conditional Formatting with the Subtotals tool in Excel.

If you have ever used the Subtotals tool to group information you have probable been impressed with its ability to group data by some changing event (like States) and have those groups aggregated and then structured into a collapsible outline.

Before Subtotals

After Subtotals

CondFmt2

But the one shortfall when it comes to the Subtotals tool is that there are no built-in artistic styles that can be applied to give the list a bit of pizazz.

Using Excel MODE Function to Return a Text Response

Excel’s MODE function is a great tool for returning the most frequently occurring number in a set of numbers. But what if you want to return the most frequently occurring word in a list of words?

MODE with Numbers

Using the MODE function in Excel is quite simple; you point to a list of numbers and MODE will tell you which number occurs the most often.

In this list, the number “4” appears more often than any other number.

MODE with Words

As you can see, the MODE function does not work very well when pointing to a list of words.

Mode2

The function returns a “#N/A” error message.

Not to fear; MODE can be made to return words, but it take the combined efforts of SEVERAL functions, none of which are MODE! (How odd does THAT sound?)

Excel – Mixed Pivot Table Layout

Microsoft Excel Pivot Tables is one of the greatest inventions known to man; second only to those buttery mints they bring you in fancy restaurants. Even the greatest of ideas are not without their issues. Take for example the Report Layout feature in Pivot Tables. Excel gives the user three options with which to display hierarchically (wow; that’s a hard word to spell) related data.

Compact Form
Outline Form
Tabular Form

Let’s examine what makes each layout unique.

Compact Form

All row-based data is combined into a single column, one row per line item, and indented to reflect the position within the hierarchy.

Converting Names into Email Addresses

Suppose you have a list of names, perhaps a roster of employee names, and you wish to generate email addresses for these individuals. If you work at a company that has an established standard for email addresses (i.e. first initial of first name with last name) then you have a few options. The preferred strategy depends largely on the version of Excel you are using as well as the naming pattern used in the emails addresses.

Flash Fill (Excel 2013 / Excel 2016)

If you are not familiar with Flash Fill, this tool allows you to type a pattern next to existing data and Flash Fill will repeat the pattern for the remaining data but on a per-record/per-line basis.

Let us take a look at the following example:

You have a list of first and last names and you wish to convert those names to an email format that takes the first letter of the first name, adds a “dot”, then adds the last name with an “@” sign and the company domain name. If we had an employee named “Fred Smith” who worked at “widget.com”, we would need to assign the email address “f.smith@widget.com” to the user.

Imagine a list like the following: