Excel Tidbits: Creating Column Header Rows for Sorting

Date: 2012.05.02 | Category: Programming & Functions | Response: 0

Well, sad to say this one has stumped me for a long time. I work in Numbers from Apple’s iWorks Suite about as much as I do in Excel. Data sets above about 1000 rows, assuming use of processor-heavy formulas like vlookups, really slow Numbers down. From a visual and formula perspective, Numbers is much easier to use. One of my favorite aspects is the ability to create header rows so that the last row becomes the column headers and the references in formulas. That makes modifying data quicker and improves one’s ability to keep track of a greater variety of segments.

However, when you’re dealing with tens of thousands of records/rows and multiple sheets, Excel excels (wink, wink) high above Numbers. Unfortunately, it is much harder to make things look professional in the program.

When I create dashboards, I usually want data summaries and pivot tables above the data, but it’s a pain when sorting the data below since typically you have to select the data you want to sort. Luckily the sorting options are saved even if your data selection changes. But it would still be much easier if I could just click a cell in a column and have Excel already know the data I want to sort and what I do not.

The solution is adding column filters (see image from the Mac Excel below).

1. Select the column header row.
2. Then under the “Data” menu or in the “Data” toolbar, select “Filter”.
3. You don’t even need to use the filters. In fact, just remove them after adding them. From then on, Excel will know which row is the column headers when sorting. Brilliant!
4. Now test the sort. Select a cell, or click a filter arrow on a column header to select a new sort for the column.
5. Tip: If you remove the filters on the header row, be sure NOT to try sorting above that column header row. Doing so will retrain Excel to ignore the row and you’ll have to reassign the filters again.

No more selecting the cells you want to sort before so doing–saving mental energy; allowing that energy to focus on the task and not loose track of a formula you’re creating or a use-case scenario you’re trying to answer.

Want to see it in action? Download an example filter column header row spreadsheet to give it a try.

Best wishes!

Excel Tidbits: Extracting Domains from URLs Function

Date: 2012.04.02 | Category: Programming & Functions | Response: 0

I rarely show how much of a techie I am, but thought this equation was valuable to share. In some of the work I do for my company, Anthem Optimization, I mash and extract data daily using Excel. I needed a way to pull out the domain from a web address and compare it with domains from other sheets and activities. This then gives me insight into how often we do work on websites across all our projects.

Version Note: The formula below only works in newer versions of Excel–typically versions that can read the xlsx format. Older versions of Excel (pre-2004) are limited to seven nested formulas. This equation has eight. If you need this to work in an older version, you can remove the over arching check for information in the $A6 cell at the very front (if($A6=”",…)). The iserror formulas in the if statements checks for errors and displays whatever is in the A6 cell if an error occurs. If the cell is blank, it should stay blank. I simply used the blank check upfront to make the formula more efficient in processing.

Here is the formula:

=IF($A6=”",”",SUBSTITUTE(IF(ISERROR(FIND(“https://”,$A6)),SUBSTITUTE(IF(ISERROR(FIND(“/”,SUBSTITUTE($A6,”http://”,”"))),$A6,REPLACE($A6,FIND(“/”,SUBSTITUTE($A6,”http://”,”"))+7,LEN($A6)+1,”")),”http://”,”"),SUBSTITUTE(IF(ISERROR(FIND(“/”,SUBSTITUTE($A6,”https://”,”"))),$A6,REPLACE($A6,FIND(“/”,SUBSTITUTE($A6,”https://”,”"))+8,LEN($A6)+1,”")),”https://”,”")),”www.”,”")

A bit complex to explain, but it considers situations where a web address uses either the secure “https” protocol or just the regular “http” and always removes the “www” from the address. The rest of it is looking for the first slash after the “http://” or “https://” and deletes everything from that slash to the end.

Here are the details for each stage:

=IF($A6=”",”",  // Makes sure nothing happens if nothing is in the cell

SUBSTITUTE(  // Used to delete www from the result

IF(ISERROR(FIND(“https://”,$A6)), // If it doesn’t find https, then execute the function to remove everything after the domain for sites with http:// — this is needed since the s adds characters prior to the slash (important for the replace function later)

SUBSTITUTE( // If https:// was not found, this begins the formula used to delete everything after the first slash after the http://

IF(ISERROR(FIND(“/”, SUBSTITUTE($A6,”http://”,”"))), // If it doesn’t find a slash after the domain, then do nothing and display what’s in the cell

$A6,      // Just display what was in the cell–which should be just a domain address since there is no slash after the http

REPLACE($A6, // This is the core function that removes everything after the domain

FIND(“/”, SUBSTITUTE($A6,”http://”,”"))     // Finds the spot/character number where the first slash is after the http://

+ 7,        // Adds the number of spaces for http so that the starting point of the replacement is correct

LEN($A6)+1,       //This finds the end of the space where text is supposed to be replaced/deleted. Added 1 to cover bases and potential spacing issues.

“”          // this is what it will be replaced with…nothing.

)          // Ends the Replace function.

),        // Ends the IsError function.

“http://”,”"    //  Once the result is determined (either after the end of the url is cut off or if it’s just a domain), delete the http:// so that just the domain shows

),      // Ends the substitute function if http:// is found.

SUBSTITUTE( // If https:// was not found, this begins the formula used to delete everything after the first slash after the http://

IF(ISERROR(FIND(“/”, SUBSTITUTE($A6,”https://”,”"))), // If it doesn’t find a slash after the secure domain, then do nothing and display what’s in the cell

$A6,      // Just display what was in the cell–which should be just a domain address since there is no slash after the https://

REPLACE($A6, // This is the core function that removes everything after the secure domain

FIND(“/”, SUBSTITUTE($A6,”https://”,”"))     // Finds the spot/character number where the first slash is after the https://

+ 8,        // Adds the number of spaces for https so that the starting point of the replacement is correct

LEN($A6)+1,       //This finds the end of the space where text is supposed to be replaced/deleted. Added 1 to cover bases and potential spacing issues.

“”          // this is what it will be replaced with…nothing.

)          // Ends the Replace function.

),        // Ends the IsError function.

“https://”,”"    //  Once the result is determined (either after the end of the url is cut off or if it’s just a domain), delete the https:// so that just the domain shows

),      // Ends the substitute function if https:// is found.

),

“www.”,”"   // so whatever the result is, it should now just be a domain, but sometimes links have www for a site and sometimes not. So, we’ll delete the www if it’s there.

)

There are many great applications for this in search engine optimization (SEO), online marketing, publicity, analytics, web development, and many more areas where reporting and comparing data is helpful! Combine this with a VLOOKUP or SUMIF formula and you will be able to total up visits, backlinks, status, errors, and much more.

Hands on Nashville Day 2011

Date: 2011.09.16 | Category: General, Volunteering | Response: 0

Over 1800 volunteers came together for the largest schools rehabilitation project for the year in Nashville, TN on September 24, 2011. Hands On Nashville Day is the largest fundraiser for the non-profit organization, Hands On Nashville–a successful volunteer coordinating organization in Nashville. HON has had significant affect on beautifying the city, helping those in need, empowering youth, and strengthening non-profit organizations. I have been fortunate to volunteer with HON for several years now, and have seen the effects of their hard work and dedication as an organization making Nashville a better place to live and work.

Meigs Middle Magnet School

Volunteers of all ages gather to help beautify 55 area public schools. I am typically fortunate to lead teams of 15 to 50 volunteers. This year I organized a team for Nashville’s first African American high school–established in 1883–the Meigs Middle Magnet School. We painted, touched up landscaping, and provided other various projects for the school. The work is generally simple, usually doesn’t require much experience in those areas, and rarely requires a professional to do the work. We are everyday people coming together to support HON and beautify our city at the same time.

Interested in volunteering? The organization has dozens of opportunities each month. People can sign up via the Hands on Nashville website. There are plenty of schools and organizations that need volunteers. I look forward to seeing you!

Hard Times are the Best of Times

Date: 2011.08.22 | Category: Leadership, Management | Response: 0

Recently, I talked with a friend of mine about a challenge in her band. She leads the band, but has had a hard time receiving respect from the rest of the members. Getting them to follow her lead and vision is often wrought with adversity, power challenges, and slow to take hold. Not too long ago, a band crisis ensued. And unexpectedly, the band members complained to her about the seriousness of the issue, looking to her for the solution. Their minds were focused on the problem. They communicated their opinion not only to her, but to each other. The crisis was not a normal disagreement. She knew it needed a solution and a response quickly.

In challenging times, we often want to wallow in that despair along with the team. As team leaders, we sometimes jump in with the group spreading the ill and the darkness, removing hope. It creates an environment that embraces pointing the blame at individuals and endorses behavior that lashes out against one another. This spiral continues by tearing down team members, dividing them, and repelling any true sense of responsible unity that is necessary for a team’s success.

But times like these are not hopeless. And they are not despairing. They are the times most valuable for a leader–when he/she can enter the moment with a hard but right solution and provide stability in a time when it feels unachievable. Mark Udall, one of the current senators for Colorado, provided this lesson from President Ford’s leadership:

President Ford was a devoted, decent man of impeccable integrity who put service to his country before his own self interest. He helped heal our nation during a time of crisis, provided steady leadership and restored people’s faith in the presidency and in government.

When people are at their greatest need, a leader takes control of the situation and becomes the person willing to take the hard steps. They define structure and direction, and paint hope for the near future. While the team is focused on the darkness around and how they feel, a leader wakes them up to a plan and solution. Like President Ford, these moments define the leader in people’s eyes—making the future easier for the leader and the team. They will trust the leader’s judgment better, listen closer, and reduce how often they challenge the leader’s authority. Prior to these trying times, a leader might have been in question. But after these moments, leadership is made clear and claimed.

Offer

Be that person coming to save the day for your team. Provide your team with:

1. Vision
• Communicate belief in the team
• Define a solution even if the journey will be hard
2. Structure
• Reiterate behavior that is unacceptable
• Define clear goals and consequences
3. Solution
• Define clear steps for the guilty parties
• Define clear steps for the innocent parties and how they will support the guilty
• Define your own role in keeping the team on task and how you will do the dirty work

Attitude

Choose to:

1. Be willing to take the hardest tasks upon yourself.
2. Communicate belief in the team.
3. If the hardest task is on someone else, promise to be by their side—encouraging them and supporting them as they tackle their new responsibility.
4. Repeat often the goal and realistic achievability of that goal.
5. Paint a vision of a time soon to come that is better and full of success or achievement.
6. Recognize this is the time when you are needed most.
7. Remember that you are less important right now. Although, this post clarifies benefits to a crisis, it’s your team that matter more than this. Providing solution is about your team’s success and growth, not their acceptance of you or your good standing.

Having this mindset brings clarity to what you need to do to take your team to a stronger place. Recognize the moment and seize it.

It’s your time to shine. How are you going to save the day for your team?

—

Additional Reading: Read advice similar to this  from an experienced leader. Michael Hyatt has some great advice for us to keep in mind. 

Seven Tips for More Productive and Loyal Employees

Date: 2010.09.03 | Category: Leadership, Management | Response: 2

I worked for a startup Internet marketing agency here in Nashville, TN named [meta]marketer as an Optimization Specialist until January 2011. My primary role was to manage a team of freelancers who do most of the link building we directed for our search engine optimization (SEO) clients. We paid just above minimum wage. The amount of work fluctuated based on our SEO strategy and client load. Considering the pay scale, most of the people we hired were just out of college or had jobs in areas not related to the tasks for which I hired them. Thus, there’s a lot of training that went with the work. Due to the combination of pay scale and occasional personal financial challenges, it was challenging to keep freelancers motivated with the detail, reptitive work we assigned. Most of the workers were millennials–a.k.a. Generation Y. The exact birth dates for this group vary, but they range between the mid-1970s and mid-2000s (quite broad) (source:Wikipedia).

I attended a presentation today by Travis Robertson, a small business strategist. His discussion was on Millennials in the Workplace. I found it helpful to hear another person’s opinion on how to manage and motivate people in that age range. Often we hear that this generation is lazy, disloyal, and distracted (i.e. A.D.D.). But as Travis points out, they are also considered the most creative and technologically advanced generation. How do you maximize the length and productivity of a millennial? Read the rest of this entry »