excel Archives

How to Create or Import a Table in Adobe InDesign

The table is just too useful to keep out of many business documents these days. It’s too good at organizing data. Luckily, even a desktop publishing program like InDesign has the feature handy. And we can even import an existing one pretty much as-is, to …

Slicers in Excel PivotTables

When Excel 2007 was introduced, the PivotTable Field List included a “Filter” area, where one could drop a field to use as a filter (for example, year, brand name, size, etc. from a block of sales data). If the user filtered for one year, or …

Track Changes in Microsoft Excel–Essentials

One of my wife’s colleagues recently asked about whether it’s possible to track changes in an Excel spreadsheet. It certainly is, though it works a little differently than in Word, say. Turning the feature on in Excel is quite easy–simply go to the Review tab …

Access vs Excel—Which should we use for what?

Judging Access vs Excel can take thought. Most people find Microsoft Excel fairly easy to learn—it has a fairly gentle learning curve, the fundamentals only take a short time to pick up, and the program is actually fairly versatile—it’s not just a ledger book on …

How to Use COUNTBLANK in Excel

Some of the Excel functions, such as IF, come into play all the time, even outside business. They’re versatile and can do a lot. But others seem a little abstruse, or out in left field. One question I occasionally get from business people in Excel …

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.

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?)