Posts

Reversing Row order in Excel

In many cases we face challenges in reversing row order in Excel.  There are two simple approaches for doing this. One is to add an extra column with serial numbers and then sort on that column in descending order. This works perfect for small amount of data.

For big data it is best to use Microsoft Power Query for Excel Add-in. It works great with Microsoft Excel 2010 and Microsoft Excel 2013.

 

 

Simple steps to perform the action:

  1. Convert your existing Data to Excel Table. To know more on Excel Table, you can refer to the article Working with Excel Table.
  2. Select the created Table and click the From Table under Power Query Tab as shown in the below image.
  3. The Power Query window will open. Select the Reverse Rows under the Transform Tab.

power-query-tab-table

 

power-query-reverse-rows

Benefit of Power Query?

It works on a smaller sample of data and then applies the transformation when you choose Save and Load option. This is much faster than getting all the data and then trying to sort it which is the first method.

Why is this required?

Usually required with logs where the first transactions or rows are at the bottom. So the data is received in reverse chronological order. Twitter feeds, Timeline Updates, Live blogs – all follow this pattern.

This method works independent of the time-stamp column. What is wrong with timestamp? It may be in different time formats, some rows may have same timestamp and some rows may have no timestamp at all.

Generating a List of File Names

This tip describes how to retrieve a list of file names in a folder and display them in a worksheet.This technique uses an Excel 4 XLM macro function in a named formula. It’s useful because it’s a relatively simple way of getting a list of file names into a worksheet – something that normally requires a complex VBA macro.

Start with a new workbook and then follow these steps to create a named formula:

  1. Choose Formulas Tab ➜ Define Name to display the New Name dialog box (CTRL + F3).
  2. Type FileList in the Name field.
  3. Enter the formula in the Refers To field =FILES(Sheet1!$A$1)
  4. Click OK to close the New Name dialog box.

filelist_name_manager

Note that the FILES function is not a normal Worksheet Function. Rather, it’s an old XLM style macro function that is intended to be used on a special macro sheet. This function takes one argument (a directory path and a file specification) and returns an array of file names in that directory that match the file specification. A normal worksheet formula cannot use these old XLM functions, but named formulas can. After defining the named formula, enter a directory path and file specification into cell A1.

For example:

  • F:\Future in Hands\Training\*  to display all files
  • F:\Future in Hands\Training\*xl* to display all Excel files

Then this formula displays the first file found: =INDEX(FileList, 1)

If you change the second argument to 2, it displays the second file found, and so on.

Below figure shows an example. The path and file specification is in cell A1. Cell A2 contains this formula, copied down the column: =INDEX(FileList,ROW()-1)

The ROW function, as used here, generates a series of consecutive integers: 1, 2, 3, and so on. These integers are used as the second argument for the INDEX function. When you change the directory in cell A1, the formulas update to display the new file names.

generating-a-list-of-file-names

Note

If you use this technique, you must save the workbook as a macro-enabled file (with an *.xlsm or *.xls extension).

 

Resume Writing Tips

Know the purpose of your Resume

Some people write a resume as if the purpose of the document was to land a job. As a result they end up with a really long and boring piece that makes them look like desperate job hunters. The objective of your resume is to land an interview, and the interview will land you the job (hopefully!).

Back up your qualities and strengths

Instead of creating a long (and boring) list with all your qualities (e.g., disciplined, creative, problem solver) try to connect them with real life and work experiences. In other words, you need to back these qualities and strengths up, else it will appear that you are just trying to inflate things.

Make sure to use the right keywords

Most companies (even smaller ones) are already using digital databases to search for candidates. This means that the HR department will run search queries based on specific keywords. Guess what, if your resume doesn’t have the keywords related to the job you are applying for, you will be out even before the game starts.

These keywords will usually be nouns. Check the job description and related job ads for a clue on what the employer might be looking for.

Use effective titles

Like it or not, employers will usually make a judgment about your resume in 5 seconds. Under this time frame the most important aspect will be the titles that you listed on the resume, so make sure they grab the attention. Try to be as descriptive as possible, giving the employer a good idea about the nature of your past work experiences. For example:

Bad title: Accounting Good title: Management of A/R and A/P and Recordkeeping.

Proofread it twice

It would be difficult to emphasize the importance of proofreading your resume. One small typo and your chances of getting hired could slip. Proofreading it once is not enough, so do it twice, three times or as many as necessary.

Use bullet points

No employer will have the time (or patience) to read long paragraphs of text. Make sure, therefore, to use bullet points and short sentences to describe your experiences, educational background and professional objectives.

Where are you going?

Including professional goals can help you by giving employers an idea of where you are going, and how you want to arrive there. You don’t need to have a special section devoted to your professional objectives, but overall the resume must communicate it. The question of whether or not to highlight your career objectives on the resume is a polemic one among HR managers, so go with your feeling. If you decide to list them, make sure they are not generic.

Put the most important information first

This point is valid both to the overall order of your resume, as well as to the individual sections. Most of the times your previous work experience will be the most important part of the resume, so put it at the top. When describing your experiences or skills, list the most important ones first.

Attention to the typography

First of all make sure that your fonts are big enough. The smaller you should go is 11 points, but 12 is probably safer. Do not use capital letters all over the place, remember that your goal is to communicate a message as fast and as clearly as possible. Arial and Times are good choices.

Do not include “no kidding” information

There are many people that like to include statements like “Available for interview” or “References available upon request.” If you are sending a resume to a company, it should be a given that you are available for an interview and that you will provide references if requested. Just avoid items that will make the employer think “no kidding!”

Explain the benefits of your skills

Merely stating that you can do something will not catch the attention of the employer. If you manage to explain how it will benefit his company, and to connect it to tangible results, then you will greatly improve your chances.

Avoid negativity

Do not include information that might sound negative in the eyes of the employer. This is valid both to your resume and to interviews. You don’t need to include, for instance, things that you hated about your last company.

Achievements instead of responsibilities

Resumes that include a long list of “responsibilities included…” are plain boring, and not efficient in selling yourself. Instead of listing responsibilities, therefore, describe your professional achievements.

No pictures

Sure, we know that you are good looking, but unless you are applying for a job where the physical traits are very important (Ex: modelling, acting and so on), and unless the employer specifically requested it, you should avoid attaching your picture to the resume.

Use numbers

This tip is a complement to the 13th one. If you are going to describe your past professional achievements, it would be a good idea to make them as solid as possible. Numbers are your friends here. Don’t merely mention that you increased the annual revenues of your division, say that you increased them by $100,000, by 78%, and so on.

One resume for each employer

One of the most common mistakes that people make is to create a standard resume and send it to all the job openings that they can find. Sure it will save you time, but it will also greatly decrease the chances of landing an interview (so in reality it could even represent a waste of time). Tailor your resume for each employer. The same point applies to your cover letters.

Identify the problems of the employer

A good starting point to tailor your resume for a specific employer is to identify what possible problems he might have at hand. Try to understand the market of the company you are applying for a job, and identify what kind of difficulties they might be going through. After that illustrate on your resume how you and your skills would help to solve those problems.

Avoid age discrimination

It is illegal to discriminate people because of their age, but some employers do these considerations nonetheless. Why risk the trouble? Unless specifically requested, do not include your age on your resume.

You don’t need to list all your work experiences

If you have job experiences that you are not proud of, or that are not relevant to the current opportunity, you should just omit them. Mentioning that you used to sell hamburgers when you were 17 is probably not going to help you land that executive position.

Go with what you got

If you never had any real working experience, just include your summer jobs or volunteer work. If you don’t have a degree yet, mention the title and the estimated date for completion. As long as those points are relevant to the job in question, it does not matter if they are official or not.

Sell your fish

Remember that you are trying to sell yourself. As long as you don’t go over the edge, all the marketing efforts that you can put in your resume (in its content, design, delivery method and so on) will give you an advantage over the other candidates.

Don’t include irrelevant information

Irrelevant information such as political affiliation, religion and sexual preference will not help you. In fact it might even hurt your chances of landing an interview. Just skip it.

Use Mr. and Ms. if appropriate

If you have a gender neutral name like Alex or Ryan make sure to include the Mr. or Ms. Prefix, so that employers will not get confused about your gender.

No lies, please

Seems like a no brainer, but you would be amused to discover the amount of people that lie in their resumes. Even small lies should be avoided. Apart from being wrong, most HR departments do background checks these days, and if you are buster it might ruin your credibility for good.

Keep the salary in mind

The image you will create with your resume must match the salary and responsibility level that you are aiming for.

Analyze job ads

You will find plenty of useful information on job ads. Analyze no only the ad that you will be applying for, but also those from companies on the same segment or offering related positions. You should be able to identify what profile they are looking for and how the information should be presented.

Get someone else to review your resume

Even if you think you resume is looking kinky, it would be a good idea to get a second and third opinion about it. We usually become blind to our own mistakes or way of reasoning, so another people will be in a good position to evaluate the overall quality of your resume and make appropriate suggestions.

One or two pages

The ideal length for a resume is a polemic subject. Most employers and recruiting specialists, however, say that it should contain one or two pages at maximum. Just keep in mind that, provided all the necessary information is there, the shorter your resume, the better.

Use action verbs

A very common advice to job seekers is to use action verbs. But what are they? Action verbs are basically verbs that will get noticed more easily, and that will clearly communicate what your experience or achievement were. Examples include managed, coached, enforced and planned.

Use a good printer

If you are going to use a paper version of your resume, make sure to use a decent printer. Laser printers usually get the job done. Plain white paper is the preferred one as well.

No hobbies

Unless you are 100% sure that some of your hobbies will support you candidacy, avoid mentioning them. I know you are proud of your swimming team, but share it with your friends and not with potential employers.

Update your resume regularly

It is a good idea to update your resume on a regular basis. Add all the new information that you think is relevant, as well as courses, training programs and other academic qualifications that you might receive along the way. This is the best way to keep track of everything and to make sure that you will not end up sending an obsolete document to the employer.

Mention who you worked with

If you have reported or worked with someone that is well known in your industry, it could be a good idea to mention it on the resume. The same thing applies to presidents and CEOs. If you reported to or worked directly with highly ranked executives, add it to the resume.

No scattered information

Your resume must have a clear focus. If would cause a negative impression if you mentioned that one year you were studying drama, and the next you were working as an accountant. Make sure that all the information you will include will work towards a unified image. Employers like decided people.

Make the design flow with white space

Do not jam your resume with text. Sure we said that you should make your resume as short and concise as possible, but that refers to the overall amount of information and not to how much text you can pack in a single sheet of paper. White space between the words, lines and paragraphs can improve the legibility of your resume.

Lists all your positions

If you have worked a long time for the same company (over 10 years) it could be a good idea to list all the different positions and roles that you had during this time separately. You probably had different responsibilities and developed different skills on each role, so the employer will like to know it.

No jargon or slang

It should be common sense, but believe me, it is not. Slang should never be present in a resume. As for technical jargon, do not assume that the employer will know what you are talking about. Even if you are sending your resume to a company in the same segment, the person who will read it for the first time might not have any technical expertise.

Careful with sample resume templates

There are many websites that offer free resume templates. While they can help you to get an idea of what you are looking for, do not just copy and paste one of the most used ones. You certainly don’t want to look just like any other candidate, do you?

Create an email proof formatting

It is very likely that you will end up sending your resume via email to most companies. Apart from having a Word document ready to go as an attachment, you should also have a text version of your resume that does not look disfigured in the body of the email or in online forms. Attachments might get blocked by spam filters, and many people just prefer having the resume on the body of the email itself.

Remove your older work experiences

If you have been working for 20 years or more, there is no need to have 2 pages of your resume listing all your work experiences, starting with the job at the local coffee shop at the age of 17! Most experts agree that the last 15 years of your career are enough.

No fancy design details

Do not use a coloured background, fancy fonts or images on your resume. Sure, you might think that the little flowers will cheer up the document, but other people might just throw it away at the sight.

No pronouns

You resume should not contain the pronouns “I” or “me.” That is how we normally structure sentences, but since your resume is a document about your person, using these pronouns is actually redundant.

Don’t forget the basics

The first thing on your resume should be your name. It should be bold and with a larger font than the rest of the text. Make sure that your contact details are clearly listed. Secondly, both the name and contact details should be included on all the pages of the resume (if you have more than one).

Consider getting professional help

If you are having a hard time to create your resume, or if you are receiving no response whatsoever from companies, you could consider hiring a professional resume writing service. There are both local and online options are available, and usually the investment will be worth the money.

Filling Gaps in a Range

When you import data, you can sometimes end up with a worksheet that looks something like the one shown in the figure. This type of report formatting is common. As you can see, an entry in column A applies to several rows of data. If you sort this type of list, the missing data messes things up, and you can no longer tell who sold what when.

filling-gaps-in-a-range

If your list is small, you can enter the missing cell values manually or by using a series of Home ➜ Editing ➜ Fill ➜ Down commands (or its Ctrl+D shortcut). But if you have a large list that’s in this format, you need a better way of filling in those cell values. Here’s how:

  1. In Cell A4, type =A3 and press Enter.
  2. Select A4 and Copy the Cell (CTRL +C).
  3. Select the range that has the gaps (A3:A14 in this example).
  4. Choose Home ➜ Editing ➜ Find & Select ➜ Go To Special. The Go To Special dialog box appears.
  5. Select the Blanks option and click OK. This action selects the blank cells in the original selection.
  6. Paste the Data (CTRL +V) and its done.
  7. You can Replace the formula by Paste Special ➜ Values

After you complete these steps, the gaps are filled in with the correct information. Now it’s a normal list, and you can do whatever you like with it – including sorting.

Using SmartArt to create a bulleted list

We can use SmartArt to create a bulleted list in Excel. Choose Insert➜Illustrations➜SmartArt, and choose the diagram style from the dialog box.

The below figure shows a “Vertical Bullet List” SmartArt diagram. This object is free-floating and can easily be moved and resized. The example shown here uses minimal formatting, but you have lots of control over the appearance of SmartArt.

using-smartart-to-create-bulleted-list-in-excel

Join multiple text quickly

We sometime comes with the challenge to join multiple text in Excel. The first thing that comes into our mind is to use the CONCATENATE Function. The major challenge in using this function that we cannot select a continuous Range such as CONCATENATE(A1:A10). To get the result we need to specify all the cells individually.

To do this quickly and smartly, we can use TRANSPOSE with CONCATENATE Function. Below are the steps for doing this. Let say the cells you want to combine are in A1:A10.

  1. In a blank cell, where you want to concatenate all the values type =CONCATENATE(TRANSPOSE(A1:A10))
  2. Don’t press enter yet.
  3. Select the TRANSPOSE(A1:A10) portion and press F9. This replaces the TRANSPOSE(A1:A10) with its result.
  4. Now remove curly brackets { and }
  5. Press Enter

 

DATEDIF function

DATEDIF calculates the difference between two dates and expresses the result in terms of months, days, or years.

The DATEDIF function, which isn’t documented in the Excel Help system, is one of the little Excel mysteries. Although the Excel 2000 Help system has an entry for DATEDIF, the function is not documented in earlier or later versions.

The syntax for the DATEDIF function is

=DATEDIF(StartDate,EndDate,Interval)

StartDate and EndDate are standard dates (or a reference to a cell that contains a date). The third argument, Interval, is a text string that specifies the unit of time that will be returned.

Valid interval codes are described in this list:

  • m:  The number of complete months between StartDate and EndDate.
  • d:  The number of days between StartDate and EndDate.
  • y:  The number of complete years between StartDate and EndDate.
  • ym:  The number of months between StartDate and EndDate. This interval excludes years, so it works as though the two dates are in the same year.
  • yd:  The number of days between StartDate and EndDate. This interval excludes years, so it works as though StartDate and EndDate are in the same year.
  • md:  The number of days between StartDate and EndDate. This interval excludes both month and year, so it works as though StartDate and EndDate are in the same month and the same year.

This function is majorly used for Age calculation. To calculate the current Age, you can use the below formula:

=DATEDIF(DOB,TODAY(),”y”) & ” years, “&DATEDIF(DOB,TODAY(),”ym”) & “months, “&DATEDIF(DOB,TODAY(),”md”) & ” days”

Where DOB is the Date of Birth. You can use a cell reference or manually write your DOB. Ex: “17-Jul-81″

This formula returns a text string, like this: 33 years, 8 months, 17 days

Count Unique records in a Range

In some situations, you may need to count unique records in a range. The below figure shows an example. Column A has a list of cities, and the goal is to count the number of unique cities in the list. The formula in cell C3 returns 7, which is the number of unique cities.

count-unique-records

To get the result we need to use Array Formula. This formula is: =SUM(1/COUNTIF(A1:A10,A1:A10))

Press Ctrl+Shift+Enter after writing the formula to get the desired output.

Shading Alternate Rows in Excel

When you create a table (using Insert➜Tables➜Table), you have the option of formatting the table in such a way that alternate rows are shaded. Shading Alternate Rows can make your spreadsheets easier to read.

This tip describes how to use conditional formatting to obtain alternate row shading for any range of data. It’s a dynamic technique: If you add or delete rows within the conditional formatting area, the shading is updated automatically. Shading-Alternate-Rows

Here’s how to apply shading to alternate rows:

  1. Select the range to format.
  2. Choose Home➜Conditional Formatting➜New Rule. The New Formatting Rule dialog box appears.
  3. For the rule type, choose Use a Formula to Determine Which Cells to Format.
  4. Enter the formula in the box labelled Format Values Where This Formulas Is True: =MOD(ROW(),2)=0
  5. Click the Format button. The Format Cells dialog box appears.
  6. In the Format Cells dialog box, click the Fill tab and select a background fill color.
  7. Click OK to close the Format Cells dialog box, and click OK again to close the New Formatting Rule dialog box.

This conditional formatting formula uses the ROW function (which returns the row number) and the MOD function (which returns the remainder of its first argument divided by its second argument). For cells in even-numbered rows, the MOD function returns 0, and cells in that row are formatted.

For alternate shading of columns, use the COLUMN function instead of the ROW function.

WEEKDAY Function

WEEKDAY Function tells you the day of week from a given date.

Syntax of Weekday formula: =WEEKDAY(Date)

Example 1: =WEEKDAY(“01-Apr-2014″) = 3 (3 for Tuesday)
Example 2: =WEEKDAY(TODAY()) = Current Day of week

WEEKDAY function returns the day number of a week as follows:

1 : Sunday
2 : Monday
3 : Tuesday
4 : Wednesday
5 : Thursday
6 : Friday
7 : Saturday