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.


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?) (more…)

Read More

QlickView Preserving Dates in Crosstables

The data transformation tools in QlikView are amazing.  When I think of the hours I have spent working with data that was output by some system trying to get that data into a format that I could then START modeling.  Oh, the wasted man hours.

One of the QlikView transformation tools has the ability to take a crosstable (like a pivot table in Excel) and convert it into a straight table; a traditional flat table where data typically starts.


The only problem with this tool is that it converts all of the data in the crosstable into straight text.  This means that even if you see data that looks like a date (ex: 11/25/2015), it is really just a collection of numbers and slashes.  QlikView does not understand what these numbers represent.  This makes modeling the data a bit problematic. (more…)

Read More

5 Tips for Photoshop Users

Sometimes it’s the little things that make all the difference.

Tip 1: We’ve all used the ever popular Save As to prevent messing up a photograph and not being able to go back afterward and edit out our changes. A much simpler approach to work in a non-destructive manner on an image is to create either an empty retouching layer or an image copy retouching layer. When using the empty retouching layer approach remember to check Sample All Layers in the options bar.


Tip 2: To return a property window to its default settings, simply hold down the alt key (Windows) or the opt key (Mac) and the cancel button will switch to a reset button. After clicking reset, simply release the alt/opt key and it returns to cancel.



Tip 3: There have been several times over the years where an added lens flare was that last little touch to make an image complete. The frustration arose when trying to precisely place the lens flare where it should go. The lens flare property window is often times less than cooperative with this accurate placement. The solution involves using the info panel to find the x and y coordinates where the lens flare will go, and then alt (Windows) or opt (Mac) clicking inside the lens flare panel thumbnail image to bring up the precise lens flare property window. Plug in the x and y coordinates found earlier and it now rests exactly where we wanted it.


The last two tips are for people using Photoshop for web page design.

Tip 4: Photoshop makes it increasingly easier to design for the web. Not only do we have web page size choices as part of creating a new document, but we can also use the New Guide Layout window (found in the View menu) to establish a layout grid. Number of rows, columns, column width, row height, and margins can all be assigned from within this window. You can even save your favorite grid as a preset for use over and over again.



Tip 5: After establishing a grid to be used for your web page you can start placing images and shapes to accommodate text blocks. Once your layout is complete, let Photoshop tell you what CSS you’ll need to place each item in your page. Select the image or shape to be placed and use the layers panel menu Copy CSS command to generate CSS directly to the clipboard. This generated CSS can then be pasted into any text editor, such as Dreamweaver or Brackets. Photoshop will even generate CSS3 code for gradients, drop shadows, and rounded corners.



Read More

Using Adobe Bridge Instead of Windows Explorer

Adobe Bridge CC is a separate application that can be downloaded as part of your Creative Cloud subscription. For years it has been an additional application that came with familiar programs like Photoshop, Illustrator, or InDesign. Its real purpose escaped me for years and I mostly ignored its existence. That is until a little over two years ago.

I was doing graphic design work as a contractor for a major hotel chain. Mostly small composited images that went along with weekly articles for an internal newsletter. The number of images could range anywhere from one to two dozen each week. Different brands required different logos, required different color palettes. There were many resources for me to use in different file formats, and that was when I remembered one of the key benefits to using Bridge in a Windows environment.

Take a look at the image below and tell me what the Photoshop, Illustrator, PDF, or InDesign files contain. Pretty much impossible to tell isn’t it. I do know which ones are Photoshop files versus Illustrator files, but that’s about it.


Now take a look at the same folder’s contents using Bridge.


Every file, no matter the format, has a thumbnail that lets me see the actual content. Now imagine you have fifteen different Illustrator logos all for the same brand, each one just a little bit different. Each thumbnail would let me see the subtle differences and allow me to select the correct logo. This may sound incredibly simple, but it is huge when you have hundreds of images you are working with.

Add to that some of the filtering features Bridge makes available and you too many find yourself singing its praises. And that barely scratches the surface of what you can do with Bridge. Batch processing, adding meta data and/or keywords, and synchronizing color settings are all tasks that can be performed using Bridge.

It’s all part of your Creative Cloud subscription, check it out.

Read More

Crystal Reports Clear Recent Reports History

Crystal Reports – Clear Recent Reports History

As with most applications, Crystal Reports maintains a history of the last several reports opened so as to facilitate repeated access.


This is a very helpful feature, but there comes a time in every report user’s life when they just want to clear the list and start clean.

Many applications contain a mechanism that allows the user to clear either individual entries or empty the entire list.  Sadly, Crystal Reports contains no such feature. (more…)

Read More

Excel – Mixed Pivot Table Layout

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.

PivotLayout01 (more…)

Read More

Excel – Convert Names to Email Addresses

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 “”, we would need to assign the email address “” to the user.

Imagine a list like the following:



Read More

How to Size Fonts in CSS

One consideration most designers go through when creating content for today’s web is; should my stylesheet begin with a reset? The variety of reset options used today is fodder for a completely different discussion, but there is a subtopic I’d like to pursue.

Oftentimes a stylesheet will begin with a few very basic reset options. Simple things like setting the margins and padding in the body of the document to 0 (zero). Another is establishing the font to be used throughout the document as well as its size. The user agent (designer speak for the browser) sets the size of the font to medium, which translates to 16 pixels.

Firefox-font-settings Chrome-font-settings

The easiest way to establish this default font size setting is to set it to normal, or 100%. Most of the popular reset stylesheets do just exactly that, set the font-size to 100%.

That works well, but we might run into some issues if we decide we want to use ems as our unit of measure.

CSS offers 15 different units of measure: cm, mm, in, px, pt, pc (all absolute), em, ex, ch, rem, vw, vh, vmin, vmax and % (all relative).The absolute units of measure work well for output other than the screen, such as print. Ems work well for the screen because of their scalability. That is, until we start nesting items, such as lists.

If the font for a list is set to .8em and there happens to be nested data, the nested list uses inheritance to establish its size. That size, in our example, will be 80% of its parent. So every nested list would be 80% of the previous container. The incredible shrinking list is not exactly what we want.

compounding-screen-capture1 compounding-screen-capture2

The solution to this dilemma is to use .8rem instead. Rem as a unit of measure refers to the root element, not the parent. So each list, no matter how deeply it is nested, would be 80% of the normal font size.

This is one of those early lessons learned when you start using something other than pixels for sizing in your CSS files.

compounding-screen-capture4 compounding-screen-capture3

Read More

New, Active, Default Features


Have you ever opened the latest version of an application and discovered that a new feature was a default setting? This one goes all the way back to 2010 when Adobe released their fifth version of the Creative Suite.

Many applications have some sort of zoom tool. Most of the time it’s a drop-down menu with some size settings, or in the case of graphics programs like Photoshop there is a tool that looks like a magnifying glass.

This zoom tool has worked the same way forever. Select the tool from the tools panel, click on an area of your image and it will zoom in on that area. Click again, it does that same thing. You say you want to zoom back out, just hold down the alt (Windows) or option (Mac) key and a little minus sign shows up by your cursor letting you know the zoom direction has been reversed.

Another usage of the zoom tool would be to draw a marquee around a specific section of your picture to zoom in on that section. That’s where our story changes with the release of Photoshop CS5.

The first time you chose the zoom tool and attempted to draw a marquee around a section of your image you started zooming automatically, almost uncontrollably. After playing around you realize if you drag right, it zooms in, and if you drag left it zooms out. Okay, I get it, but is this the only way it works?

The answer is no, but it is the new default setting and it has a name: Scrubby Zoom. Sounds more like Scrappy Doo’s artistic cousin than a tool setting. It will be active by default if your computer supports OpenGL which is a 2D, 3D rendering API.

If you like it; no harm, no foul. If it’s not your cup of tea you can deactivate it simply by unchecking Scrubby Zoom in the Options bar after selecting the zoom tool.

Five years later it’s still the default setting, and in my case, is still turned off. It’s just not my cup of tea, but you may love it.

Read More

Typography: Part 3

In Part 1 we began with a simple classification of the different typefaces used today.

In Part 2 we took a simple look at some of the basic character components.

In Part 3 we want to make the users of Adobe products aware of a wonderful service that is available through the Creative Cloud.

Back in 2009 Jeff Veen, Bryan Mason, Ryan Carver, and Greg Veen, all originally part of the Measure Map/Google Analytics team began a company called Small Batch, Inc. Its purpose was to make font usage on the web far more accessible by introducing a product called Typekit.

Designers had been painfully aware of the limitations associated with font usage online since the webs beginning. At best there were about 14 fonts most designers could count on being installed on the average user’s computer. Did you think everyone was really that crazy about Times New Roman, Arial, or Verdana? (more…)

Read More

How To Plan a WordPress Website

wordpress-logo-stacked-rgbYou’ve decided you need a website for your business, your frequent family reunions, your… And YOU want to build it – you don’t want to pay someone. Where do you start? Here’s the frustration and fun. You search  “how to build a website” (or something similar) and the resulting possibilities are seemingly endless. Wow. Where’s the aspirin? (more…)

Read More

Typography: Part 2

In Part 1 we began with a simple classification of the different typefaces used today. We kept things simple and broke them down into 5 different groups; serif, sans-serif, monospace, handwritten, and decorative.

In Part 2 we will take a simple look at some of the basic character components. This is not an extensive listing of all the possible bits and pieces that make up a font, but rather a basic sampling of some more common terms associated with typefaces.

There are well over 100 components that can make up a character set. Several of the ones listed might even be referred to using a different term. A tiddle is also a dot, the mean line can also be called the midline. In the example below the letter g is used to demonstrate 2 components, but is made up of at least 5 including; ear, bowl, counter, link, loop, and descender.

So, while you are reading this, understand that hundreds of designers over many centuries are responsible for making your life easier, and more entertaining all due to the simple little font.


Read More

Typography: Part 1

This will be our initial look into the world of text. All those letters that we string together to form books, online articles, blog posts, and the like.

Most mere mortals pay little attention to the how and why of assembling all those words into a form worthy of consumption.

“Typography is to literature as musical performance is to composition: an essential act of interpretation, full of endless opportunities for insight or obtuseness.” ― Robert Bringhurst, The Elements of Typographic Style

This series will begin with a simple classification of the different typefaces used today. To keep things simple I, and many others, have chosen to break them down into 5 different groups; serif, sans-serif, monospace, handwritten, and decorative.




Read More

Making a Font Selection in Photoshop CC 2014 or Later


This isn’t about choosing the perfect font to enhance a brochure or flyer. It is much simpler than that.

In most applications today when we want to change the font being used, we make a choice from a long list of fonts installed on our computer. It’s generally a combo box, list box, or whatever term you want to use to describe some type of list.

They are generally in alphabetical order, so you start scrolling to find your chosen font within what feels like an endless list.

Photoshop CC 2014 or later to the rescue!

At long last, instead of scrolling through a list of fonts, you just type the name of the font you wish to use at the top of the list box. For instance, type A-R-I-A-L and the fonts containing those letters, in that order, will appear. As a matter of fact, Arial in it’s different incarnations is the only font in the list .

Gone are the days of scrolling past…
back up…
oops, too far…
back down…
I know it’s here somewhere.

Thank you Adobe!

Sometimes it’s the simple things that bring a smile to your face.

Read More

Photoshop: Flow vs Opacity


When working with some of the drawing tools in Photoshop, you will notice two different adjustment settings in the options bar. The adjustments I am referring to are opacity and flow.

Just what are the differences between these two settings?

Let’s go back to the days of childhood when we were all artists. Remember coloring books?

Even if your parents didn’t buy them for you, whenever you went to a restaurant you were probably given a few crayons and something to color. You would color quietly until everything was filled in or you got bored. Then you would start scribbling over and over in the same spot. The longer you scribbled in that same spot, the darker the color would get because it would continue to build up.

That technique, if you are using Photoshop without a drawing tablet, is what flow allows us to do. The percentage is much like the amount of pressure you applied while coloring; again, if working without a drawing tablet. The higher the value, the harder you were pressing, and each pass of your mouse is like scribbling over and over in the same spot. Even if you don’t release the mouse, each pass lays down more color.

So what about opacity?

Let’s go back to coloring book days and birthday parties. Did you ever get a present wrapped in tissue paper?

One layer was never enough; you could see through it so you knew immediately what the gift was. The solution was to keep wrapping tissue paper around the package until you couldn’t see through it.

That’s the way opacity works. Painting the same spot over and over without releasing the mouse is like laying down one sheet of tissue paper. Release the mouse, do it again, another sheet of tissue paper. The percentage setting relates to how see-through the tissue paper or color is. The lower the percentage, the more transparent it is.

Combining these two concepts allows for increased fine tuning of brushes, masks, cloning, etc. Your understanding of these two simple settings will change the way you work. It has for me.


Read More

Understanding Task Dependency Types in Microsoft Project

Dependency Types in Microsoft Project

When creating schedules in Microsoft Project the first thing that a Project Manager would typically do is to input the tasks involved in a project. These tasks then need to be linked to show the relationship between them. These links create task dependencies.

There are 4 different types of task dependency:

  • Finish-to-Start (FS): The finish date of one task drives the start date of another.
  • Start-to-Start (SS): The start date of one task drives the start date of another.
  • Finish-to-Finish (FF): The finish date of one task drives the finish date of another.
  • Start-to-Finish (SF): The start date of one task drives the finish date of another.


Read More

Excel Pivot Tables Filter by Values

If you’ve ever used Pivot Tables in Excel, you no doubt have discovered the wonders of filtering.  The ability to filter row or column items can be extremely helpful when you don’t wish to analyze all of the items in the driving data set.


But what do you do if you wish to filter by the Value-based items?  In other words, the numbers in the “connect the dots” area where row and column choices intersect.  These, on first glance, don’t appear to have sorting and filtering controls available.


Rest assured, they do exist; you just have to dig a bit to find them.


Read More

Excel Lookup with Dynamic Input

VLOOKUP is great for returning information from a database, but one of the limitations is that the return information is static.

What if the user wishes to look for certain data one day but different data another day?  This would require either two different sets of VLOOKUP functions or the functions would need to be reprogrammed.

In the database below, the user would wish to return address information in one scenario, but return financial information in another scenario.

Suppose there are times when the user requires a mixture of the two; that would require a third set of VLOOKUP functions. This could become an ever evolving set of work.


Read More

Automatically Refresh Excel Pivot Tables

Excel PivotTables are one of the greatest tools in the spreadsheet user’s toolkit.

However, there is one tiny bit of functionality that appears to be missing: the ability of pivot tables to automatically update when information in the source data changes.

Most user’s see this as a glaring lack of functionality. There is, however a very good reason why pivot tables do not automatically update.


Read More

Hide Drives in Windows Explorer via Registry Setting

With so many hardware devices (like printers, external hard drive enclosures, and home network devices) equipped with USB ports, it’s likely that you’ve collected an array of advertised drives in Windows Explorer that are not actually accessible unless something is connected to them.  Perhaps you have setup a mirrored drive for backup purposes that you want to be “out of sight; out of mind”.

HideDrives 1

Hiding these devices is quite simple… provided you can do a bit of counting in binary.


Read More

Preview Fonts in Photoshop

Selecting a font in most applications has come a long way over the years.  In the old days you had to select each font separately from a drop down and decide if it was the right font for the job.  Then along came the ability to sample fonts by hovering your mouse pointer over the list.  What a time saver.

But not in Photoshop! (Pssst!  You actually can, but don’t tell anyone.  It’ll be our little secret.)


Read More

Office 2013 – Recover Unsaved Documents

Admit it; you’ve done this more than once.  You open an Office application like Word or Excel and type out your next great novel or number-crunching masterpiece.  Then, in a state of haste, you start closing windows and accidentally close the one window you intended to leave open.  One second later you realize, this was the one windows you shouldn’t have closed.  All is lost… or is it?


Read More

QlikView “Allocated Memory Exceeded” Error – An Easy Check

If you have worked with QlikView and encountered the error message “Allocated Memory Exceeded”, there could be numerous reasons for this message.  You may have a quite a quest in store for yourself to figure out what the issue is.  But there’s quick and easy thing to check that may just be the reason for your plight.

If you have been writing expressions, one of the primary culplits for generating this memory error is a simple syntax error with your expression.

Here’s just such a scenerio:  You have created a fantastic pie chart and wish to add some intelligence to the chart.


Read More

Rename Files Fast with the Tab Key

If you scan documents from a network scanner (like the giant printer down the hall) and have the scan sent to your email, you are probably less than thrilled with the name that the scanner gives the file.  The name is usually something generic and uninformative like “Scan0001.pdf” or a combination of the date and the time like “07182015-100855.pdf”.



Read More

Convert a PowerPoint Presentation to Video

This tutorial applies to PowerPoint 2013, but several earlier versions of PowerPoint possess the same abilities.  The exact steps and options will vary slightly from version to version.

Showing your PowerPoint masterpiece can be thrilling, but what if you want to reach a wider audience?  How can we take our presentation from the confines of the boardroom (or is that bored room?) and set it free so the world can bask in its glory?

Very easily; we convert it into a video.

In this tutorial, we discover a feature introduced in PowerPoint 2010 which allows you to take your slides and encode them to a video that can be loaded to your corporate network for on-demand viewing through an intranet portal, or posted to a more globally accessible site such as YouTube.


Read More