The best tips and tricks on Excel 2013

You need to find out in a list how many different values are contained, or how to split values from cells? Thanks to the following Excel tips, you can make many work with the popular spreadsheet much easier.

Excel is without doubt one of the more powerful tools in office environments. Nevertheless, the data is often bypassed relatively manually, even though the spreadsheet could take the work off. Admittedly, not all functions or procedures are self-explanatory, but we have summarized the following step-by-step instructions for you.

Some of the above tips work not only with Microsoft Excel 2013 but also with Excel 2010 or even earlier versions. If this is so, we have noted this on the spot.

Often, data in Excel is not stored in the desired cell division. With Excel 2013, Microsoft introduced the flash preview. It saves tedious manual work as well as the use of the text conversion assistant or chained formulas.

The standard situation during the data transfer is, without a doubt, one of the following: It is not possible to transfer data in the same way as it would be necessary for further processing. Individual data is grouped into a cell that should be separated for reasonable processing. The very big classics are first name and name as well as postal code and place.

To date, under Excel, you could separate the data or use the text conversion wizard under formulas, see below also split Excel data from one cell into several cells. Excel 2013 introduces a new feature that makes it much easier to deal with such problems. The function is called “Flash Preview” and attempts to determine the desired action from the pattern of inputs.

In our first example, the first column shows the names of the participants, first name, and last name were entered in a cell. For the further processing, first names and surnames are to be stored in columns B and C. For this purpose, we enter the first name of the list manually into the cells B2 and C2 separately in the first name and name. If we now switch to cell B3 and select the flash preview in the Data menu, Excel suggests the corresponding values for the underlying cells. The keystroke for the flash preview is CTRL + E. You can use the flash preview context menu to automatically mark all modified cells.

What works with the separation of information works just as well with the combination of data and the addition of characters. In our second example, a product description is to be composed of three components, which are in columns A, B and C. The name should be composed of the three values separated by slashes and spaces. Again, it suffices to generate this label manually in cell D2. If you switch to cell D3 and select the flash preview, the column is filled out automatically.

Functions like LEFT or RIGHT can be replaced by the flash preview. In our example, we need the last three digits of a nine-digit customer number for an evaluation. The procedure is the same as above: enter the last three digits, change to the next empty cell, and select the flash preview. VoilĂ , the desired digits end up in the cells below.

Is there a difference to the traditional approach with functions? Yes, namely a verifiable: If functions are in play, the target data change, if one changes the origin data – this does not happen with the Blitzvorschau.

There can always be small problems if the manually entered desired format collides with given cell formats – popular examples here are the date formats.

Data quickly and easily

Especially users in small and medium business has Microsoft Excel 2013, a quick analysis. This allows users to quickly reach their goal without the knowledge of formulas or analysis tools.

With the new features of Quick Analysis and Flash Preview (see also Excel 2013 – separating or combining values from cells ), Microsoft addresses users in small and medium-sized enterprises. With the combination of both functions, data can be quickly disassembled and evaluated.

If you select a data block, the symbol for the quick analysis appears automatically, or alternatively, you can call it with CTRL + Q. Quick analysis includes formatting, charts, results, tables, and sparklines. Each of the points provides numerous options to help you get results quickly. We have selected some examples.

In our example stores sell three different products, the table shows the number of sold pieces per store. We would like to quickly identify the branches in which more than 300 pieces of a product have been sold. To do this, we switch to the fast analysis and select the option under Formatting Greater than. There you can enter the condition for formatting. The quick analysis always provides a live preview, so you can see the result before you confirm the option. If you want to format the upper 10 percent of the values in color,

Similarly, a graphical evaluation is accomplished. In our example, we want to show in a bar graph, which store has sold as much of every product. For this, we select diagrams and grouped bars from the quick analysis . If only the diagram title is missing, and the graphic is finished.

Among the results , a number of statistical functions can be found in the rapid analysis, for example for the average determination. In our example, we select the column with product A and choose Average results . Now the average value is displayed in the free cell below the column. If you switch to this cell as an active cell, you can select other evaluations instead of the average from a drop-down menu. For example, the minimum, maximum, sum or number of numbers are available.

Under Tables , you can format tables or quickly create pivot tables in quick analysis. For example, with a few clicks, a report with the total sales quantity of product B is created.

The last top point in the fast analysis are the so-called sparklines, ie mini-diagrams, which are displayed in a cell. This is useful, for example, to show trendlines in a line. For this, we use an example with quarterly numbers and products. The trend line shows how the sales figures of the product behave over the quarters.

Quick analysis offers a wide range of possibilities for a wide range of evaluations that require neither great Excel or formula knowledge.

Anyone who has to go through complex evaluations or is responsible for project workflows is well advised to examine the relevant periods for actual working days. This serves as a planning aid as well as an argumentation aid. Excel provides a separate function for this.

The situation is well-known: expressions such as “The project can be relaxed in one month”, and then this month brings only 21 working days, even though it is a month with 31 gross days. And you have already dealt with an unrealistic lump sum.

The Excel function allows you to calculate how many workdays between two dates are actually available. The syntax of the function is very simple

If you only enter the start date and the end date, Excel returns the working days for the corresponding period, without the weekends. To ensure that holidays or other holidays, such as holidays or regular bridging days, are taken into account, you also need a list for the “Free_Tage” argument. To do this, create a range of cells with the appropriate dates, and assign a name for this range, which you then insert into the formula.

Drag and drop cells using the mouse

In order not to accidentally catch the function for automatic filling or copying, you have to keep an eye on the shape of the mouse pointer. Move the mouse to the edge of the cell you want to move until the big white plus icon disappears, and an arrow appears in all four directions instead. Now the cell can be moved by mouse and placed in another area.

This also works with several cells, but only if they directly adjoin each other. The cell references in formulas are adjusted automatically.

Number of different values

You’ve got a list and you need to quickly determine how many different values it contains in one area? A small formula does this calculation.

A problem that occurs again and again: For a certain area in Excel , you need to determine how many different values are present in order to process a project further. The COUNTING function performs well: Packed in a matrix function, it determines the number of different values.

In our example, the values are located in the subscriber area. We have named the range B2: B13 in this way. The formula is:

{COUNT = IF (subscriber; participants)}

This formula is to be entered as a matrix function, since it is to be counted for every value, how often this occurs. If you type the formula with CTRL + SHIFT + ENTER, Excel automatically adds the braces and performs this as a matrix function. This is also to be considered for the following formulas.

The element Bernhard occurs five times in our example, so the above formula returns “5”. Since each participant is to be counted only once, one divides 1 by the number. If you add the whole thing now, you get the number of different values in the specified range with:

=SUM {(1 / COUNTIF (subscriber; participants))}

Anyone who has to reckon with empty cells in the list can possibly exclude this by an IF condition, since the formula otherwise causes an error. This is something like this:

=SUM {(IF (Subscriber = “”; 0; 1 / COUNTWAY (Subscriber; Subscriber)))}



Leave a Comment