Posted on Leave a comment

Unstack data using new dynamic array formulas

Microsoft introduced new Dynamic Array Formulas for Excel 365 users. In this article you will learn how to use SEQUENCE function to unstack your data.

You are provided a set of data. All the data is stacked in column A. Your task is to convert one column data range into multiple columns data range.

Each source data is in a different row. Headers of the table are in rows:1, 2, 3. We can find the data regarding the first product in rows 4, 5, 6 and the data concerning the second product in rows 7, 8, 9 and so on. You should put these rows numbers in a table. Each line of the table should only contain rows numbers of a single product. The table we need to create should look like the one in the picture below.

You can easily generate such a table using new Excel Dynamic Array Formula called SEQUENCE. Click an empty cell and start typing the formula. The first argument is the number of rows the formula should spill into. In the example it is 11(headers and ten products). The second argument is the number of the columns the formula spills into. In the example formula should spill into 3 columns (Product, Quantity and Sales)

=SEQUENCE(11,3)

The final step is to embed SEQUENCE function in INDEX function. INDEX functions returns a value from the cell at an intersection of a row and a column. But first you need to know the row’s and column’s number. The row number is returned by SEQUENCE function and the column number is 1 since the source data is entirely in one column i.e. column A.

=INDEX(A1:A33,SEQUENCE(11,3),1)

 

Posted on Leave a comment

Cell color based calculations

There’s no built in function in Excel that allows you to make calculations based on the cell color. However, there is a simple method with no VBA needed that allows calculations based on cells’ color.

Step 1 – Select cells of the same color

On the ribbon go to the Home tab and click Find and select drop-down button. Select Find. You can also use CTRL+F keyboard shortcut. The Find and Replace dialog box appears. In the dialog box click Options button.

Now click Format drop-down and select Choose Format From Cell option. The dialog box disappears. Point and click the cell with desired color eg. red. The cell’s format is grabbed and displayed in the dialog box preview.

Click Find all button. At the bottom of the window you can notice a list of all red cells. Click that list and use CTRL+A keyboard shortcut to select all cells. All red cells are highlighted. Now you cal close Find and select dialog window.

Step 2 – Define name

On the ribbon go to the Formulas tab and click Define name icon.

The Define name dialog box appears. In the Name texbox type name for the selected cells eg. Red. Click Ok button to close the dialog box.

Repeat the first and second step to define names for all the colors used in your table.

Step 3 – Calculations

In the example I will calculate Sum of numbers in all red cells. You can use the defined name in many other formulas as well.

Click an empty cell and type =sum( . Now type the name of the color and close prentices. Press Enter to confirm the formula. Notice that in the formula you used the defined name for cells region instead of referring to each cell separately.

Posted on Leave a comment

Format numbers as square meters

There’s no build in option in Excel to format numbers as square meters. In oredr to do that you need to use custom number formatting.

Select cells that you want to applay formatting to. Right click selected cells and from the menu choose Format cells.

Format Cells dialog box appears. Go to the Number tab and from the categories list select CustomIn the Type text box type the following code:

# ##0,00″ m²”

To get the superscript two press and hold ALT button and type 178 using the number keyboard.

If you can’t enter superscript two using the keyboard shortcut, try the folowing solution. Click an empty cell. Go to the Insert tab and select Symbol icon. In the Symbols dialog box select superscript two and click Insert buton then close the dialog box.

The superscript two is inserted to a cell. Highlight it on the Formula bar and press CTRL+C keyboard shortcat to copy it. Press ESC button to leave the Formula bar. Now you can just paste the superscript two to the cutom formatting code.

Posted on Leave a comment

Excel test for a job interview

Many employers require candidates to be familiar with MS Excel. Depending on the job, more or less advanced knowledge of the programme is necessary. In the application letter, it is not worth lying about your skills, as more and more often one of the elements of recruitment is a test verifying the knowledge of Excel.

The test usually consists of several tasks. The candidate is provided with a spreadsheet with data and has to perform tasks that are similar to their future duties at work.

Usually, the candidates are allowed from 15 to 30 minutes to solve the test.

It is difficult to predict what questions. However, based on conversations with job seekers, there is a standard set of issues that arise during the tests.

Often recruiters ask about:

  • Pivot Tables
  • SEARCH Function. (VERTICAL) VLOOKUP),
  • Logica functions (IF)
  • Ability to use $ sign in addressing cells – often candidates are asked to make multiplication table in Excel.

Less than 20% of people can make a multiplication table in Excel without error in the first attempt.

You should also be aware of traps. The most common include numbers in text format, missing column headings in the PivotTable source data range.

The Best way to prepare for the test is to perform as many exercises as possible. You can find many of them online.

It is a Good idea to sign up for training, e.g., Intermediate level. During the training, you can always ask the trainer questions, ask for additional explanations, exchange your experiences and opinions with other participants, which is a definite advantage. You can also mention in your CV the training you underwent.

Good luck for the job interiview.

Posted on Leave a comment

Excel – enter a new line break in a cell and remove the existing one

Enter a new line break with a keyboard shortcut

When preparing a report in Excel, sometimes you need to type a longer text. If a text is too long, it doesn’t fit in the cell, and wrapping the text does not improve its readability. A good solution would be to insert a new text line in the cell.

While typing just press ALT and ENTER keys on your keyboard to enter a new line break in a cell. Mac users should use OPTION COMMAND RETURN keys.

Notice that after entering a new line of text in a cell the Wrap text icon is on.

Remove a line break

To remove text line breaks in a cell, you should unwrap the text by clicking Wrap text icon. If this solution doesn’t work, use the formula described below.

Enter a new line break with a formula

To enter a new line break, you can use CHAR formula. This formula returns a character for a given ASCI character code. For example, if you type CHAR(36), the formula returns the $ sign. To return line break with CHAR formula you need to use code 10 on Windows and code 13 on Mac.

Here is a short example of entering lorem ipsum text with line breaks.

=”Lorem ipsum”&CHAR(10)&”Lorem ipsum”&CHAR(10)&”Lorem ipsum”

Instead of using & sign you can also use embed CHAR formula in CONCAT or CONCATENATE function.

=CONCAT(“Lorem ipsum”,CHAR(10),”Lorem ipsum”,CHAR(10),”Lorem ipsum”)

After typing formulas, don’t forget to wrap the text and adjust the cell’s width and height.

Remove line breaks using a formula

To remove line breaks with a formula, you must substitute line break character with a space character. Embed CHAR formula in SUBSTITUTE formula.

The arguments of SUBSTITUTE formula are:
Text – text in which you make changes, mostly it is a cell reference
Old_text – a string that is to be replaced. In our example, it is line break expressed with CHAR(10) formula
New_text – a new string that replaces the old one. In this example it is space.
The complete formula looks like this:

=SUBSTITUTE(A1,CHAR(10),” “)

Posted on Leave a comment

Convert date to standard Excel format – easy solution

It’s not surprising that not everyone follows the instructions and enter dates in cells in standard Excel format. Also, it often happens when you export data from a database to xlsx file or import data from web to Excel that the format of dates differs from the standard one.

Some people struggle writing complicated formulas, but fortunately, Excel is equipped with Text to Columns tool. Text to Columns tool can convert any nonstandard date format to the standard one.

Let’s suppose that your task is to convert yyyy.dd.mm date format to standard Excel date format.

Here are the steps to follow

  1. Select cells.
  2. Go to Data tab and click Text to columns icon.
  3. In the first step of Convert Text to Columns Wizard choose Fixed width data type and click Next button.
  4. Don’t insert any break lines in the second step of the wizard. Just click the Next button to move on.
  5. In the third step of Convert Text to Columns Wizard select in the Column data format list Date.
  6. Drop down the list and select the date format that you need to convert. In this example, it is the last one YDM.
  7. Click Finish Button
  8. The dates have been converted to your Excel standard format.
Posted on

Excel assessment test for a job interview

If you apply for a position at the office, you should bear in mind that most employers require you to have hands-on experience in Excel. The command of Excel that is expected by your future employer depends on the position you are applying for and on tasks you will be performing.

Basic level

At the basic level employers expect you to know how to:

  • Property enter data
  • Perform basic mathematical calculations
  • Filter and sort data
  • Format cells
  • Freeze panes
  • Print documents
  • Insert simple charts
  • Perform operations on spreadsheets, e.g. hide, unhide rows or columns

Intermediate level

Users with intermediate command in Excel should also be able to:

  • Use logical formulas (IF, IFS, IFERROR, OR, AND), lookup formulas (VLOOKUP), mathematical formulas (SUMIF, ROUND), statistical formulas (COUNTIF, COUNTIFS) and text formulas
  • Perform data analysis using Pivot Tables and Subtotals
  • Analyze data with charts
  • Import data from csv or text files

Advanced level

Advances users should be able to:

  • Use most of logical, lookup and information formula
  • Write more complex embedded formulas
  • Perform advanced data analysis using pivot tables
  • Use macros
  • Import and transform data using PowerQuery
  • Create dashboards using PowerPivot tool

Although you can state the level of your command in Excel in your résumé, be prepared for an Excel test for job applicants.

There are different forms of tests that you should be aware of. The most common are:

  • Interview
  • Case study
  • Multiple choice test

Interview

During an interview, you might be asked about your experience in working with Excel spreadsheets. You might be asked to describe reports you used to prepare and which Excel functions and tool you used. It is common to ask which Excel function or tool is more suitable to solve a particular task. Occasionally you can be asked about advantages or disadvantages of the most popular formulas or tools.

Case study

A case study is one of the most common ways of checking a candidate’s command in Excel. Usually, you are provided with a spreadsheet and asked to solve tasks similar that you will face during your work routine. Remember that your time to perform all tasks will be limited.

Multiple choice questions

This type of test is usually performed online before you are invited for a job interview. You receive an email with a link to a website with the test. On average such tests consists of 30 questions. Remember to read instructions carefully; the test can be either single or multiple choice. It is important to get to know Excels’s technical language before sitting such a test, check names of formulas, tools, and elements of the program’s window.