Track Changes in Microsoft Excel–Essentials

Seth Bonder Leave a comment

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 in the Ribbon, slide over to the Changes group, and click Track Changes.
Review Tab, Track Changes
Almost immediately, we see one of the differences between this and the Word version–we are told this will put the document into Shared mode, allowing others to open and edit the file at the same time. We also want to decide which specific changes to highlight.
Highlight Changes dialog box
Usually it’ll be When and Who. By not checking Where, we’re saying we want to see where *all* changes are being made, which is normally desirable. And we almost always want to track changes on screen, though it’s possible to list changes on a new sheet. (Sometimes, for business situations, it’s quite helpful to do the latter, but being able to track and accept or reject onscreen is often immediately useful.)
Once the feature is on, any changes we make will be tracked. A marker will appear at the upper left of the cell(s) in question, to let us know something’s different. Whenever we save, the markers disappear.
More names added
But the program still tracks changes. Whenever we’re ready, we can review them and decide to officially accept or reject them.
Accept-Reject dialog box
And if something changes after that..no matter how many times, the feature will still follow along.
Uncle Vlad added to list

So we can definitely track changes in Excel. The nature of the program requires that it function a little differently than in other members of the Microsoft Office, but not so much that the average user would have trouble with it.

The one downside to tracking changes in any of the Office programs is the feature is a little memory intensive. This isn’t nearly as much of a problem as it might have been a few years ago, what with the faster processors and less expensive memory we can get now.

Access vs Excel—Which should we use for what?

Seth Bonder Leave a comment

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 steroids. Microsoft Access, on the other hand, can be kind of intimidating. Some folks hear the word “database” and, to quote the Joker, “they start losing their minds!” The whole idea of a database program is scary because there’s more to learn, more to set up before you can use it, and more to manage even when it’s working fine.

So let’s just ask the question: When should we use each one?

To answer this, we need to understand what each one can do. Excel is a spreadsheet program. Even though information on one sheet can talk to info on another, it’s primarily meant to do math-related things. So if all you need is the equivalent of a sheet of graph paper to visually organize, say, a personal or house inventory, Excel does pretty well. You could use Word for this same purpose, and have about the same amount of work, if you don’t want to use Excel. Formatting and other aesthetic things are only about as hard as in Word, so it’s almost as if you built a giant table in Word and jotted stuff in it. Or if you want to budget monthly, quarterly, and annually, and have all of those update each other, great.

On the other hand, if you need to track something more complicated, such as information on the employees of a small (or large) company, you may run into trouble with Excel. The problem is, you may need to have three or four kinds of information that all relate to the employees, but are not directly related to each other: Personal info (home address, birthday, family), office info (building, floor, office number, phone number, business email), health plan (HMO, policy type, policy number), and maybe transportation (car make and model, parking spot, bus route, commute time). Yet all of these need to tie together, as it were. You might have each of these connect to the others through, say, the employee ID number or Social Security number—make that ID part of each of the four categories so they have a common element. This is where the term “relational database” comes from—the different categories of information nevertheless relate to each other, like a baseball team where each player has a separate job, but all cooperate to play and win.

If this idea makes sense, that we separate the types of information but allow them to relate through one item, then you understand one of the main concepts of the relational database. Like Access.

So the basic answer to the question is, If you have just one kind of information (or several that are unrelated), you can store each chunk on a separate worksheet in Excel and probably have no trouble. But if the different subsets (or as Access would call them, data tables) are related after all, using Access to store the information and work with it would actually be worth the extra time and effort. As a bicycle is good for getting around by yourself, but a pickup truck can haul some serious cargo by comparison, so Excel and Access. (Sort of.  🙂  )

As a postscript, the reason I’m not bringing up PowerPivot here—which lets us do some database-ish things in Excel—is that it’s a different tool again from Access, though it is useful. And not everyone knows about it, or how it works. We do teach it at SkillForge, but again, it’s a somewhat specialized tool, whereas Access is a more general database application and has more multi-user features, making it more suitable for business database work.

RGB vs CMYK–Which should I use?

Seth Bonder

Having taught and worked with Adobe Photoshop for many years, I get asked many basic but good questions about it by new users. One has to do with color, and the color systems we use in a picture. There are several that Photoshop can use, but the two most common are RGB and CMYK. The question: Which is best?

The fundamental difference between the two is, RGB is meant for use on screen, and CMYK for print. The terms used to describe how they work are “additive primaries” and “subtractive primaries”, which refer to how these systems show white. For RGB, imagine standing in a dark room with a white wall. Take three flashlights, with color filters (red, green, and blue) and shine them on the wall. Where the three colors overlap, they seem to make white light (the opposite of what a prism does with white light—see Pink Floyd’s album THE DARK SIDE OF THE MOON, specifically the cover—and the back cover.) So the three additive primaries add up to white.

Additive Primaries

With CMYK, the example is even simpler—a piece of paper going through a color printer may have areas where no ink or toner has landed. And the color of the paper we usually use? White, of course. So when the colors are held back, or subtracted, from a spot on the paper, that spot stays (or is) white.

Subtractive Primaries

But we need black ink too, because the dyes or pigments only sorta make black, and a normal eye can see this. So the printing folks added it for completeness.

In doing this, though, we get a couple of problems, and it takes a little thought to get round them. First, because the RGB system normally involves a screen that illuminates itself, it can show more subtle shadings of color than a piece of printed paper (which, unless you’re using radioactive inks—shame on you!—does NOT glow in the dark). The term Photoshoppers and graphic artists use is the “gamut”, which is wider/larger for RGB than for CMYK (where the ink/toner can smear a tiny bit and mess up the shadings). And a printout can’t show all the shades that a screen can. By definition, therefore, printout will always look a little less intense (“saturated”) than onscreen images.

Color Gamuts

And second, any Photoshop image saved in the CMYK system will use 33% more space on disk, regardless, than if saved with RGB. Why? Because the number of color “channels”, how many kinds of color there are in the picture at minimum, is three for RGB, and four with CMYK.

So which should we use?

If you’re scanning in a photograph you want to clean up and reprint (say, from the early 1900s), CMYK will work better, because what you’ll see on screen is what will come out of the printer. But if you’re scanning for archival purposes, scan in CMYK if possible to get a realistic version of the image, THEN change to RGB. Since RGB’s gamut is wider than CMYK’s you won’t lose any subtleties or shadings, but you will get a smaller file on disk. And you can scan in CMYK, do all your work, then save in RGB for later. Best of both worlds. Just have to make sure we understand the tradeoff of size, gamut, and storability.

How to Use COUNTBLANK in Excel

Seth Bonder

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 is something like, “How do I make sure someone has filled in all the cells where I need data to, say, calculate an average correctly?” (Think “tax form” or W-4, for example. You want to make sure certain spaces are filled in regardless.)

In some situations, you might use a function like ISERROR, and incorporate it with IF to test if there IS an ERROR when you perform a calculation:

=IF(ISERROR(AVERAGE(D2:D10))…

…so that if performing the calculation glitches, you can have the formula show a message to this effect.

=IF(ISERROR(AVERAGE(D2:D10)),”There’s a blank cell there”…

Otherwise, perform the calculation.

=IF(ISERROR(AVERAGE(D2:D10)),”There’s a blank cell there”,AVERAGE(D2:D10))

Using ISERROR with AVERAGE

 

But this doesn’t quite solve the problem, because even if just one of the cells has data in it, there won’t be an error as far as Excel is concerned.

Formula finds one cell with data and calculates average

 

So we need to call a function that looks for ANY cells being empty, and lets us know. It’s called COUNTBLANK. (Sounds like some kind of strange nobleman–Count Blank, from some tiny hamlet somewhere….)

 

=IF(COUNTBLANK(D2:D10)>0,”Missing Data in D2 thru D10″,AVERAGE(D2:D10))

COUNTBLANK finds missing data and tells user

 

What it does is pretty simple, though. It COUNTs the BLANKs in a range, and can let us know how many there are. IF (as you see in the above formula) there are any (“COUNTBLANK(D2:D10)>0”), we want to see the TRUE result from the IF (the message about missing data); if not–that is, if the test is FALSE–then we want it to calculate the average. We’ve told the program to perform the calculation only IF the COUNT of BLANK cells is NOT greater than 0, and let us know if there are any blanks so we (or the user) can correct this.

COUNTBLANK finds no missing data

 

Of course, guaranteeing that only numbers get filled in is another matter. But there are a few different ways to take care of this, such as Data Validation. The important thing is, MISSING data are a problem, as the result isn’t an accurate one. And letting the user know about this is the big thing.

Microsoft Project: Formatting a Gantt Chart for Summary Task

Mark Fitzgerald

Recently a student posed a question during a Microsoft Project class. How does one format the bars of a Gantt chart to look the same for each unique summary task? Something that may look like this.

slide-13

It begins with creating a custom flag field for each summary task. In Microsoft Project a flag field is a basic Boolean value. A simple yes/no field to identify further action. They are created using the Custom Fields button on the Format tab within the Gantt Chart Tools of the ribbon.

slide-2

The Custom Fields button opens a separate dialog box with many options. Our first choice will be to select flag from the drop down list of data fields in the upper right-hand corner. We are presented with twenty (20) flag fields. Our example uses five (5) and each one needs a unique name.

slide-3

That can be accomplished by selecting a flag, clicking rename, and supplying the new value.

slide-4

Once our five (5) flags have been named we can click OK to close the Custom Fields window.

slide-5

Next we need to mark our tasks as active (yes) or inactive (no) using the flags we just created. Insert a new column in the sheet view of the Gantt chart using one of our flag fields.

slide-6

The entire column defaults to no but we will select the first task of our named summary and change its value to yes.

slide-7

Once selected we can use the auto-fill handle (just like in Excel) to copy our yes across the remaining subtasks.

slide-8

We will insert a column for each of our summary tasks and repeat the process described above.

slide-9

The great thing is, once the tasks have been flagged we can hide the additional columns we just inserted.

slide-10

At this point we are in the home stretch. There is only one more step necessary to finish. We need some custom bar styles to match our flagged tasks and that is done by choosing the Format tab within the Gantt Chart Tools of the ribbon and selecting Bar Styles from the Format button in the Bar Styles group. You can also double-click a blank area within the chart side of the Gantt Chart View.

slide-11

In the Bar Styles dialog box we will scroll to the bottom of the list and add a new entry for each of our summary tasks using the name we gave to each flag. Shape, pattern, and color can be applied to each group and of course the flag number will be selected in the Show For … Tasks column.

slide-12

Once complete, clicking OK will supply our newly flagged and formatted Gantt bars with the shape, pattern, and color we just set up.

slide-13

Access 2013: Creating a Calculated Field

Mark Fitzgerald

Often times the information you need isn’t stored as a field within a database. As a matter of fact, certain fields make more sense to calculate whenever they are needed instead of storing the value in a table.

Microsoft Access 2013 makes this very easy.

To create a calculated you will need a query in Design View. Select the Create tab and in the Queries group click Query Design button. The QBE (Query by Example) window opens and the Show Table dialog box is ready for you to add the table or tables you need.

create-query-design

add-tables

Once the table / tables are in place you can insert the fields necessary for your query.

insert-fields

The calculated field will be entered in the next available column. It is often easier to see what you are typing if you open the Zoom window. That can be accomplished by Right-clicking the open field and selecting Zoom from the Context Menu.

zoom-tool

With the Zoom window open we can enter our calculation. The information to the left of the colon represents the caption we want at the top of the column. The information to the right of the colon is our calculation. In this case it is the curUnitPrice field. No table name is necessary because we are only using one table. The field name is wrapped in a pair of square brackets and is being multiplied by 0.1.

enter-code

Once the calculation is completed click OK on the Zoom window, save the query, and it is ready to run.

save-the-query

calculated-field

Access 2013: Saving a Filter as a Query

Mark Fitzgerald

You find yourself working in an Access table filtering out some unnecessary data and it dawns on you this filtering is something you’ll end up doing frequently. It would make sense to store this process as a query in the database.

filter-number-filyers-greater-than

enter-a-value

filter-results

Okay, so now I have to switch over to the Create tab and design a new query based on the filter I just applied. Or do I? You can actually save the filter you just applied as a query. The process is simple.

Go to the File tab and choose Save As > Save Object As > Save As.

file-save-as-save-object-as

Once you click the Save As button you will be prompted to name the results as either a query, form, or a report.

as-query

It really is that simple. The filter you just ran is now a query and can be used whenever necessary.

query-results

 

HTML: A Brief History

Mark Fitzgerald

Long ago in the far off land of Switzerland something very interesting began, and Al Gore wasn’t there.

1989 Tim Berners-Lee proposes an Internet based hypertext system for sharing documents between disparate operating systems while working as a contractor at CERN (European Organization for Nuclear Research). If you’re interested CERN is derived from Conseil Européen pour la Recherche Nucléaire.
1991 The first publicly available description of HTML. It consisted of 18 elements greatly influenced by SGML (Standard Generalized Markup Language).
1993 First proposal of HTML as a specification to the IETF (Internet Engineering Task Force). At the same time Dave Raggett was working on what he referred to as HTML+ (Hypertext Markup Format) while working for HP (Hewlett-Packard) in Bristol, England.
1994 IETF creates the HTML Working Group.

World Wide Web Consortium (W3C) was also formed by Berners-Lee at the Massachusetts Institute of Technology Laboratory for Computer Science (MIT/LCS).
1995, November 24 HTML 2.0 published as the first specification of the language.

1995, November 25 Form-based file upload
1996, May Tables
1996, August Client-side image maps
1997, January Internationalization
1996, December 16 CSS Level 1 published as W3C Recommendation
1997, January 14 HTML 3.2 published as W3C Recommendation.

The HTML Working Group had been closed in September of 1996 and all future development fell under the auspices of the W3C.
1997, December 18 HTML 4.0 published as W3C Recommendation.

There were three variations:

Strict

Transitional

Frameset
1998, May 12 CSS Level 2 published as W3C Recommendation
1999, December 24 HTML 4.01 published as a W3C Recommendation.
2011, June 7 CSS3 Color Module Recommendation
2011, September 29 CSS3 Selector Level 3 Module Recommendation
2011, September 29 CSS3 Namespaces Module Recommendation
2012, September 19 CSS3 Media Query Module Recommendation
2014, October 14 HTML5 published as a W3C Recommendation.

The large gap between HTML 4.01 and HTML5 saw the W3C strike off in a new direction incorporating elements of XML with HTML thus creating XHTML. They continued to work in this direction until a fissure arose in 2004 and members from Apple Computers, Mozilla Foundation, and Opera Software formed the Web Hypertext Application Technology Working Group (WHATWG). They pursued a specification they referred to as HTML5. On April 10, 2007 WHATWG proposed that the W3C adopt their HTML5 specification as the starting point for the newest recommendation. On May 9, 2007 the W3C’s new working group accepted the proposal.
2016, November 1 HTML 5.1 published as a W3C Recommendation.

Changing Microsoft Projects Default Task Duration

Mark Fitzgerald

In a recent Microsoft Project class a question arose concerning the default task duration, which is set in days. This student had recently been working on a deployment project and the default duration was too broad. It needed to focus on a smaller time-frame and it made perfect sense to set the task duration to hours.

An excellent question and the answer couldn’t be easier.

day-default-duration

To change the default duration for the tasks in a project we need to use the backstage options Microsoft Project makes available. By choosing the File tab and selection Options we are taken to the Project Options dialog box.

backstage

Once there we are presented with eleven options along the left side of the dialog box. Scheduling is the third option on the left and the one we are looking for.

duration-is-entered-in

In the Schedule section we will find Scheduling options for this project, and Duration is entered in will reveal the five choices we have:  minutes, hours, days, weeks, and months.

duration-length-options

Also notice that the Scheduling options for this project can be set for only this project or for All New Projects.

Once the duration has been changed simply click the OK button in the lower right corner and you will be returned to you open project. Any value entered in the duration column will now default to hours unless you specify otherwise.

duration-changed

 

Photoshop CC 2017 Property Panel Update

Mark Fitzgerald

November of 2016 has seen an update to the Adobe Creative Cloud Suite which includes Photoshop. I have already posted one article on the changes to the New Document window and another article on the updates to the Liquify Filter.

The focus of this article will be on the changes to the Properties Panel in Photoshop CC 2017. The last couple of updates have seen improvements to the Property Panel, but this by far is the biggest change.

Take a look at this screen capture of the 2015.5 Property Panel in use.

photoshop-cc-2015-5-properties-panel

The Properties Panel contains absolutely no information about the selected layer.

The same layer selected in Photoshop CC 2017 now includes x and y coordinates as well as width and height data.

properties-2017

Here is another example this time with a text layer selected. First in the 2015.5 release.

photoshop-cc-2015-5-properties2-panel

And now in the 2017 Update.

properties2-2017

In addition to the x and y coordinates available previously we now can change the font, font size, alignment, and color. Additionally there is an Advanced button that will open the Character Panel.

All of these changes are designed to accomplish one thing, making each task more efficient by limiting the number of panels we need to open.

The Properties Panel update is a welcome change to Photoshop CC 2017.

Photoshop CC 2017 Face-Aware Liquify Update

Mark Fitzgerald

If you remember the last update to Photoshop (Summer 2016) we shared a post about the new Liquify Filters Face-Aware feature. It turned out to be a wonderful addition.

The filter addition would naturally recognize the facial region and let you modify eyes, nose, mouth, and face shape. Working on a face or multiple faces became much easier.

face-aware-liquify-2015-5

But there was a drawback…there was only one set of adjustment tools for the eyes. Which meant the changes would be applied equally to both eyes.

Photoshop CC 2017 has addressed this issue and has fixed it. The Liquify Filters Face-Aware settings include two sets of controls for the eyes; one for the left and one for the right. Eye size, height, width, and tilt can be set for each eye independently.

face-aware-liquify-2017

Thank you Adobe. A great filter is now even more powerful and an easier to use utility.

 

Photoshop CC 2017 New Document Window

Mark Fitzgerald

Sometimes the changes to an application are subtle, sometimes not so much. Last year’s Start Workspace in Photoshop was one of those not so subtle changes. Just a few weeks back Adobe’s Creative Cloud application suite updated to its 2017 version and Photoshop has added another one of those not so subtle changes.

Selecting File > New in the menu bar or the New button within the Start Workspace has resulted in dialog box similar to this for a number of years.

2015-5-new-file

If you expanded the Document Type menu you were presented with several choices including; Clipboard, Default Photoshop Size, U.S. Paper, International Paper, Photo, Web, Mobile App Design, Film & Video, Iconography, Art & Illustration, Artboard , and Custom.

2015-5-new-file-options

This is no longer the case. The New Document window is much larger and although some of the old menu categories remain the window is completely revamped.

2017-new-file

Each of these presents a screen with a series of default sized blank documents, and also pre-built templates available through Adobe Stock.

2017-new-photo

Many of the templates are free and there is a search option that will take you to Adobe Stock online so you can look at the other options available.

2017-new-web-download

Once downloaded the resulting file will contain a series of scenes, layers, or artboards and each item will be on its own layer ready to be used as you see fit.

2017-after-download

All the custom options are still there but Adobe has added through Adobe Stock many start-up options. You don’t have to start with a black slate if you don’t want to.

 

 

The HTML q Tag

Mark Fitzgerald

Somewhere in the neighborhood of 18 to 20 years ago I sat down one weekend with a book, a computer, and a few images and put together my first basic website. It was about artists from the Impressionist movement and was not anything to brag about with one exception, I built it myself, using Notepad.

That was the era of HTML 3.2 moving toward HTML 4.0. Netscape was my browser of choice and most pages used tables for layout.

A lot has changed in the following years but one thing remains…most people don’t know that HTML has a q tag to wrap things in quotes. If something is supposed to be in quotes most people will just type them in their code editor, or allow their visual design application to insert the entity characters to put quotes in place. Like this:

entity-quote

Which results in this page.

quote-entity-browser

One of the main reasons I didn’t use the q tag early on was lack of browser support. Netscape supported it but IE didn’t, and IE was on the rise. The next group of browsers (Firefox, Chrome) weren’t any better. When you don’t use something for an extended period of time you tend to forget about it. That’s what happened to the q tag in my brain.

Another reason may be the fact that it is a specialized tag. It serves one purpose, wrapping text in quotation marks as part of a citation. Why learn about a tag that does one job? Mostly for SEO (search engine optimization). Giving credit where credit is due.

Using the q tag is simple and straight forward. Whatever you want quoted…wrap that text within a pair of q tags.

quote-tag

quote-ie

As for browser support in 2016; all current browsers support the q tag. IE 9 forward offers full support. So maybe it’s time to look at the small but useful q tag and give credit where credit is due.

 

HTML5 Form Date Attribute

Mark Fitzgerald

Getting corporate America to make the move to HTML5 seemed to take longer than any other transition we’ve seen in the past. In part I’m sure due to the long development process. From first working draft in January of 2008 to stable recommendation in October of 2014, that’s almost 7 years.

There are many compelling reasons to make the change but the one we will focus on today deals with forms and an easier way of inputting a date.

Early on the primary means of capturing a date was supplying a simple textbox and a visual example of the formatting.

text-element-browser

text-element

It was simple but had to have validation to guarantee its formatting.

Another approach would have been using select menus. A lot more code, both to build the lists and to concatenate the values on the server.

dob-select-list

dob-select-list-code

Another option would be to use a JavaScript library like jQuery and plug in their Datepicker UI component.

dob-jquery-datepicker

It would all be so much easier if date were just on option for a form element. Well it is in HTML5!

date-element-code

That simple input type results in a UI structure that looks like the Datepicker from jQuery.

date-element-chrome

This screen capture is using Chrome but it looks exactly the same in Opera. If you use Edge on Windows 10 it looks like this.

date-element-edge

On an iPhone using Chrome within iOS 10 it looks like this.

date-element-chrome-iphone

That’s all the good news, now for the downside. It doesn’t work in any version of IE, Firefox or Safari and doesn’t look promising for the last two in the foreseeable future.

But don’t let that stop you. Remember, if the browser doesn’t recognize the input type it defaults to a textbox, our old standard method. The benefits to the remaining users and all those on mobile devices are all in the plus column.

Adding Power View to the Excel Ribbon

Mark Fitzgerald

In recent years Microsoft has included several add-ins to boost Excels usage in the world of Business Intelligence. They include Power Map, Power Query, Power Pivot, and our topic of discussion today, Power View for Excel 2013.

Each of these ship with the Professional Edition of Excel 2013 but need to be added through the Options Panel. After going through this process I was more than surprised to find Power View was still unavailable. So this posting will help those of you experiencing the same dilemma I faced recently.

First things first. This is what my Insert tab looked like after the Power View add-in was…added in.

insert-tab-no-powerview

Normally there would be a Reports group that included one control, Power View. As you can see it doesn’t appear on this tab or any other tab.

Here are a couple of additional screen captures to show that it had been selected and does show up in the Add-In area of the Options Panel.

com-addins

options-dialog-box

I had gone through the necessary steps just as I had with Power Pivot, Power Map, and Power Query. They were all working yet Power View wasn’t available.

It took a while but the solution was found in a posting dealing with Excel 2016. Those steps are what we will go through now.

First we are going to customize the ribbon by adding a Reports group to the Insert tab. That is done by going to the File tab and selecting Customize Ribbon.

customize-ribbon

Make sure Insert is expanded and selected under Main Tabs, and then click the New Group button under the Customize the Ribbon section on the right side of the panel.

new-group

Now we will rename the group by clicking the Rename button in the same area as the New Group button.

rename-group

Now we need to find Power View in the Choose Commands from section on the left. It will be in the Commands not found in the ribbon menu option. It is Insert a Power View Report.

insert-a-power-view-report

That’s it. Click on OK at the bottom of the Options Panel and head back to the Insert tab. There it is on the far right side of the ribbon.

One additional thing, you will need Silverlight installed for Power View to work.

power-view-is-available

 

 

Styling the Humble Checkbox Using CSS

Mark Fitzgerald

The checkbox has been a staple in the world of HTML forms since day one and for the most part has remained unchanged. HTML 4 introduced the label tag which can be associated with a checkbox using an id attribute in the checkbox and the for attribute within the label. That way you don’t have to click directly on the checkbox, simply clicking the label will select or deselect it.

If the checkbox looks any different today compared to 20+ years ago it’s only because the browser renders it that way.

A typical form with several checkboxes would look like this:

plain-checkbox

And the code to generate the page looks like this:

plain-checkbox-code

Four simple checkboxes with labels. Nothing fancy but they could be simply by applying a little style using CSS.

Take a look at the same four checkboxes after a CSS makeover:

styled-checkbox

The HTML to generate the page has changed a little, but not all that much:

styled-checkbox-code

You’ll notice the label is still there but it has been emptied and moved after the input tag. We’re using it to create the circles and it is an easy task because the id associates the label with the checkbox. In place of the standard label we’re using a span tag to tell us what the checkbox represents.

All the heavy lifting is being done by an external stylesheet using one class selector, one element selector, and a few attribute selectors.

styled-checkbox-css1

First up we shape the div tag that contains each checkbox. We size it, color it, round off the corners, and position it. The relative positioning is necessary because items within it will be absolutely positioned and need a point of reference.

Next up we create the narrow gray line the circles move along. It’s nothing more than empty content that has been sized, absolutely positioned, and colored.

Now we can start working on the circles, both outer and inner, and we will use our empty labels to accomplish this. They will be positioned, sized, stacked and shaped. Oh yeah, and one last item, a CSS3 transition that will allow the circles to shift when they are clicked.

styled-checkbox-css2

The :after pseudo class will be the vehicle used to create the inner circle.

styled-checkbox-css3

The last three selectors are all attribute selectors aimed at the [type=checkbox]. The first one hides the regular checkbox. The second one moves the label (our circles) 34 pixels from its current position when the checkbox is clicked. The last selector changes the inner circle color when clicked.

A pretty amazing change and all done with a little CSS slight-of-hand.

CSS Clearfix: Three Lines and a Breakdown

Mark Fitzgerald

Occasionally the learning curve in web design can be gentle. Other times it’s a sharp right angle and once in a while it’s just a nasty hair-pin curve. How you perceive these curves is just that, your perception. You know, this morning you feel as sharp as a tack and late in the afternoon you feel as dull as a butter knife.

Clearing floated elements has been one of those butter knife curves for me. True confession time…it hasn’t been a gentle or sharp curve, it’s a I know it works so I haven’t devoted brain time to understand it curve. Was that out load? They say confession is good for the soul.

Let’s break it down and see why it’s necessary and why it works.

basic-code

The code above results in the page below. Two nested containers inside an outer wrapper. That outer wrapper should be 100% of the pages width, as tall as whatever it contains (height: auto), and it has a light gray background color.

Inside the wrapper are two boxes that should each be 30% of the wrappers width, 100 pixels in height, and have a phthalo green background color which is 70% opaque.

floated-divs

But there is a slight problem, the background color of the wrapper is missing. That’s because floated elements don’t follow the pages normal flow rule. They are removed to float left or float right which means the wrapper now believes it has no content. The height: auto of nothing is…nothing. No content, no background color.

The fix, or clearfix as it has been designated comes to the rescue. Three simple statements that make things right based on a pseudo class, a display property, and the clear property.

CSS has introduced many pseudo (false) classes in its last two versions :before and :after being included in that list. Both of these pseudo classes are useless without its content value. What are we inserting before or after? In the typical clearfix code the content can either be an empty string or a single space. We just need some content in our wrapper that is not floated and it will be placed after any existing content.

That leaves our display: block property-value pair. Using the rules established in the Block Formatting Context by the W3C block formatted elements will contain their floated children and cannot extend past the bottom edge of the block.

The code below shows our added clearfix and below it is the resulting web page showing the missing background color of the wrapper.

css-with-clearfix

floated-divs-with-clearfix

 

Using Qlikview’s Date Functions

Mark Fitzgerald

When putting together a data model for a Qlikview application you will occasionally run into a field that is not formatted properly, both from a numeric and presentational perspective. Behold the transformed junk file that Qlikview organized into meaningful data.

datedata

Meaningful yes, recognizable by the end user…not so much. Don’t get me wrong, it all works as you can see from this example.

matchingdata

Selecting a date will match up with the Plant #, Production Line ID, and Estimated Production, but the average user doesn’t what to stare at the date value and disassemble so they can mentally reassemble it as a logical date.

That’s where Qlikview’s Date() function steps in, or is it the Date#() function? You see, they are both legitimate, the first function is a formatting function and the second is an interpretation function.

A formatting function takes a numeric value as input and converts it to text whereas an interpretation function uses text as input and converts it to a number. Either way, the output is a dual which means it can be displayed as text but utilized as a number.

The first thing we’ll need to do to make it recognizable as a date is use an expression instead of the lonely date field. Right clicking the Listbox and choosing Properties from the context menu will let you change the field associated with the Listbox…or build an expression using the field.

changefieldtoexpression

The Date#() function will allow us to convert a string such as 20120528 into the serial date number 41057. Qlikview uses a serial date number based on December 31, 1899. 1/1/1900 being day 1, 1/2/1900 being day 2 and so on for each day. Today’s date 9/28/2016 would be day 42641. The format ‘YYYYMMDD’ tells the function what part of the number represents what part of the date. By the way, the word Date (in red) after the opening parentheses represents the actual date field.

datehashtag

Next we’ll nest the existing Date#() function within the Date() function. It will also be supplied with a format to present the date in a recognizable manner.

nestedfunction

Our completely formatted date is now not only usable, but also recognizable courtesy of both Qlikview date functions.

formatteddate

Qlikview Chart Styling Using Expression Attributes

Mark Fitzgerald

Qlikview makes it quick and easy to assemble your data into a professional BI application. But sometimes the information needs a little boost, that extra touch that makes the difference.

One way to accomplish this is using Expression Attributes. These attributes can be found on the Expression tab of your chart object.

Let’s take a look at a couple of examples. The line chart in the first image serves its purpose but might benefit from a style change.

linechart

By right-clicking on the chart we can enter the objects properties window. Selecting the Expressions tab allows us to see the expressions that have been used to build the chart. There is a plus sign in front of each expression that when expanded allows us to see the Expression Attributes.

objectproperties

expressionattributes

There are several different line styles that can be used:

=’<S1>’ Continuous

=’<S2>’ Dashed

=’<S3>’ Dotted

=’<S4>’ Dotted & Dashed

styledlinewithexpressionattribute

For our example we chose the dotted line.

Our next example eliminates the line of a Combination Chart and replaces it with a Diamond Symbol.

chart

A little change of color for the symbol should add that extra touch. We’ll use the RGB Function to make the Diamond Symbol red.

expressionattributebackgroundcolor

styledchartdiamondsymbol

These are just two simple examples of using Expression Attributes to enhance our Qlikview BI application. There is much value to be gained in subtle changes to our visualizations.

Using Character Mapping to Enhance a Crystal Report

Mark Fitzgerald

It is possible to dress up a report using nothing more than a font and some color. But not in the way you think. There are some specialty fonts installed on a Windows computer know as Wingdings. Each letter is a small picture (like a phone, scissors, knife, etc.) that can be used in a report to enhance the appearance.

The first step in formatting the report is to find the characters you intend to use. They can be found in the Character Map dialog box within Accessories > System Tools or by typing Character Map in Windows Search.

charactermap

Next you will need a formula to determine when a specific character will be displayed.

alternatefont

 

In this example when sales drop below $40,000 a cross will appear otherwise a check mark will take its place. The letters “Q” and “R” represent the values you’ll need in Wingdings2.

We can dress it up even more by conditionally changing the font color.

 

oddevencolor

The final step is formatting the formula font to use Wingdings2. This is accomplished by selecting the Formula object in Design View and opening the Format Editor, going to the font tab and selecting Wingdings2 from the font list.

wingdings2

The finished report now contains a “Cross” or “Check Mark” in red or green where there once was just a capital Q or R.

checksandxs

 

InDesign Data Merge with Images

admin

In this Adobe InDesign tutorial you’ll learn how to merge multiple image files into Adobe InDesign to create form letters, postcards or other custom InDesign documents that use custom images for each page in the document. To learn more, visit our InDesign Training Course page to see our InDesign Training classes.

Select and Mask Workspace: Photoshop 2015.5

Mark Fitzgerald

Photoshop has continued to refine the selection tools available and the latest update, June 2016, is no exception.

Gone is the Refine Edge tool, or better yet, not gone but improved and placed in its own workspace.

image-one

image-two

I have two images I would like to combine and masking out the unnecessary bits of the second image pictured is a simple task which has been made easier. Once photo number two has been added as a new layer to the first image and sized so our participants are approximately the same size we’ll choose the Quick Select tool. Notice there is a new choice in the Options bar, a button labeled Select and Mask.

image-three

quick-select

Clicking the Select and Mask Option takes us into a new workspace and lays out all of our tools in a new window. All the options you’ve used in the past with the Refine Edge tool are here and a few more. The View Mode now includes Onion Skin with a transparency setting that makes masking even easier than it used to be.

select-and-mask-workspace

select-and-mask-transparency

After all adjustments have been made clicking the OK button will apply the mask and return you your standard workspace.

select-and-masked-image

I’ve seen a post or two on Adobe’s Community forum concerned that the Refine Edge tool was gone. Have no fear, it’s still there it has been renamed and resides in the Select and Mask workspace.

Creating Maxfield Parrish Clouds Using Photoshop

Mark Fitzgerald

I was recently leaving the parking lot of a local business when the cloud formation in front of me reminded me of a painting by American painter and illustrator Maxfield Parrish. If you are not familiar with his work here is a painting called Ecstasy that was commissioned for the 1930 General Electric Mazda Lamp Calendar. The model was his daughter Jean Parrish.

ectasy

The clouds I was looking at were beautiful and saturated with color because it was close to sunset. I grabbed my phone, took a picture, and went about my business. Imagine my disappointment when I looked at the photo later that same evening and this is what I had captured.

IMG_1434-smaller

No worries…that’s one of the many reasons we use Photoshop.

Step number one was to get rid of the power lines, street lights, and poles. My tool of choice was the Spot Healing Brush with Content Aware active. Simply setting the brush size and dragging over sections of the poles and power lines with this tool and next thing you know; unsightly clutter removed.

spot-healing-brush-image

I wasn’t 100% sure how much of the image I wanted to keep so the next thing that needed to go was the building in the lower left-hand corner. Selecting that area with the Rectangular Marquee tool and using Content Aware Fill was all it took. By the way, this was all being done to a copy of the background layer.

content-aware-fill-after-image

Now it was time to decide what part of the image stayed, and what would be cropped. If you think you might change your mind later don’t forget to uncheck Delete Cropped Pixels in the Options bar. That way all of the image is still available.

cropped-image

Now to enhance the image with one of those layers that almost any photograph will benefit from; a Levels Adjustment. Or some of you may prefer a Curves Adjustment. Either way we are adjusting the tonal quality of the photo. Removing the vale as it were, and tweaking our contrast. At this point I’m 90% there.

levels-adjusted-image

One more step and we are complete. Just a slight bump in saturation done by adding a Hue/Saturation Adjustment layer.

memphis-clouds

When complete we have a sky that would be the perfect backdrop to any Maxfield Parrish painting. And all it took was a little imagination…and Photoshop.

Adobe InDesign CC 2015.4; Library Filtering

Mark Fitzgerald

Using InDesign to create your publication is making your life so much easier.

The photo shoot for your upcoming article went well.

Then you realize in all the pictures taken there is one shot that didn’t happen. Timing, lighting, location…whatever it was, the image isn’t there.

Thankfully, the Creative Cloud includes Adobe Stock images. All that is necessary is opening InDesign’s CC Library Panel. It can be found in every Workspace except Printing and Proofing and even then can be opened by choosing Window > CC Libraries in the menu bar.

library-panel

Once open, the Library Panel offers a search option at the very top.

adobe-stock-search

Typing in a search term or phrase will return hundreds of results from Adobe Stock’s photos, illustrations, vectors, and videos. It certainly would be nice if I could narrow the results down to include only photos and vectors.

New to the June 2016 release of InDesign that request has been fulfilled. Immediately below the search bar you will now find a “Results from Adobe Stock” option with a spinner to the left that lets you expand your search choices to include four filters; photos, illustrations, vectors, and videos.

adobe-stock-search-categories

Checking the appropriate boxes will immediately narrow your search down to only photos, and vectors in our case. Less hunting, less work equals a more productive you.

adobe-stock-library-filtered

Oh, by the way, even though this article is about InDesign this new search filter is also available in Photoshop, Illustrator, Animate, and Dreamweaver. Enjoy!

A Printer Friendly Version

Mark Fitzgerald

“You’ve seen them before: links that say “click here for printer-friendly version” or words to that effect. Every time you follow one of those links, you load a separate document that presents exactly the same information with a different layout, and probably different markup.

That means somebody (or a script) had to take the original document and convert it to a stripped-down version that’s more suitable for print output. Maybe that somebody was even you.”1

The endnote, if you take the time to read it, will tell you the article this references was written in 2002. Fourteen years later and we are still faced with far too many pages with a printer icon or the ubiquitous “click here for a printer friendly version”. Makes you wonder what we have learned during those years.

The ability to include a stylesheet specifically for print media has existed since CSS 1 released in 1996 and was refined with CSS 2 as a published recommendation in 1998. The <link rel=”stylesheet” href=”print.css” media=”print”> highlighted attribute can be added to a linked external stylesheet. That statement informs the browser the stylesheet is to be used when printing or when viewing the pages content in print preview.

If you would rather not create a separate stylesheet for print media there is the option of adding a @media print section to your existing stylesheet. Any specific rules for printing a page can be added to this media query block.

@media print    {

  header {

  background: none;

  color: #000;

  }

}

Using a separate print stylesheet also affords us the opportunity to use those interesting units of measure CSS offers like pt for fonts or in/cm for margins. We don’t use them because they don’t make any sense in a world of varying screen sizes. It makes consistent design improbable. The printed page on the other hand is consistent in size and is perfect for points, picas, inches, centimeters, or millimeters.

Most of the time designers for the web feel the content is only going to be viewed in a browser. The printed page isn’t a consideration. But it stands to reason if we design pages to adapt to varying screen sizes we should also take print into consideration. The printed web page needs to be addressed in this time of responsive design, usability, and accessibility.

Here are a few additional resources on the subject.

 CSS for print tutorial

6 Things I Learned About Print Stylesheets From HTML5 Boilerplate

10 Tips for Better Print Style Sheets

Tips And Tricks For Print Style Sheets

1Meyer, Eric (5/10/2002). CSS Design: Going to Print [Article]. Retrieved from http://alistapart.com/article/goingtoprint