Export folder or file list to Excel

Export the list of files contained in a folder or sub-folders has always been a difficult task. Now with Power Query this job is as easy as 1-2-3 😎😃😍

But more, if you don’t want the list of files but just the folder and sub-folders name, it’s also easy.

Connect Power Query to a path

  1. Go to the tab Data
  2. Select the menu Get Data
  3. Sub-menu From File
  4. Select the menu From Folder

In the dialog box, you select the path you want to import (click on the button Folder)

You can visualized a short list of the files in the folder and then you click on Edit

Power Query opens with the list of folders and files.

Export folder list

If you just want to export the folder list, and sub-folders, in Excel, you just have to follow these 4 steps

1. Remove Other Columns

First, we keep only the columns with the folders

  1. Select the column Folder Path (the last one)
  2. Right-click
  3. Select the option Remove Other Columns

2. Remove Duplicates

Then, we keep the name of each folder only once

  1. Right-click on the single column that you have in Power Query Editor
  2. Select the option Remove Duplicates

3. Remove the beginning of the path

This step in not necessary if you want to keep the starting path.

  1. Right-Click on the column
  2. Select the option Replace values
  3. In the text box Value to Find, write the original path (in this case C:\Users\fred_\OneDrive\20_EXCEL\)
  4. Leave the field Replace with empty 

Now, the result is

4. Split on the \

Last step, we split each string on each symbol \

  1. Right-Click on the column
  2. Select the option Split Columns
  3. Then the sub-option By Delimiter
  4. In the first Dropdown list, select Custom
  5. And write the delimiter, the symbol \
  6. The option Each occurrence of the delimiter must be selected

Final result

And that’s finish 😄 Click on the icon Home > Close & Load to load the result in Excel

Export file list 

If you want to extract the file list and the folder, there is few more steps but it’s still very easy. Let’s restart from the step of load of the path.

1. Add the file size

In the informations displayed, the size of the files is missing. Well in fact it is there but not visible yet.

  1. On the column header Attribute, click on the double-arrows icon
  2. Unchecked all the options except Size
  3. At the bottom of the list, unchecked Use original column name as prefix

Now you have added the file size for each files

2. Keep few columns

Here we must keep at last 3 columns. If you want to keep more (like the date of creation for instance), you have to include the column in your selection

  1. Select in this order, the column Folder Path, Name, Size (use the Control key to do the selection)
  2. Right-Click on the header (take care to not deselected the 3 columns)
  3. Select the option Remove Other Columns

And the result is this one

3. Redo previous steps

To finalize, redo the steps seen in the previous example

  1. Replace the original path
  2. Split by delimiter

Then load to Excel and you have immediately, the full list of files of the original path

Power View

POWER VIEW has been deprecated. Now you must use Power BI Desktop

Presentation of Power View

Power View was a tool in Excel 2013 Pro Plus to create interactive dashboard.

Dashboard generated with Power View

Each element (chart, map, table of value) can interacted with the others.

For instance, if you select a slice of the pie chart, this will filter all the others element according to the selection

Technology of Power View

Power View has been developed with the technology Silverlight. Unfortunately, this technology has been depreciated by Microsoft in October 2012. So it was obvious that Power View in its original state couldn’t evolve.

In fact, in the same time, HTML5 was much more robust, quick and light than Silverlight. So it was necessary for Microsoft to develop a similar tool but base on HTML5.

Power View with Excel 2016

Power View is not visible by default in Excel 2016. Nowhere in the ribbon, you can find it.

For compatibility reason, Power View could be installed by activating the tool from the menu Options>Custom Ribbon

But, that was at the beginning of Excel 2016. It seems in 2018 that none of the Power View dashboard couldn’t opened.

What is the future of Power View?

In fact, now, the best way to create interactive dashboards is to use Power BI Desktop. And there is many reason for that:

  • The tool is free
  • You can connect to many different sources
  • Manipulate easily your data (Power Query)
  • Create your own measures (Power Pivot)
  • You have tons of visuals to present your data
  • Share easily your dashboards to a specific audience

New functions of Excel 2016

With the new release of Excel 2016, six new very interesting functions have been added in three libraries.

Library Text

Function CONCAT

  • Combines the text from multiple ranges and/or strings, but it doesn’t provide the delimiter or ignore empty arguments
  • This function depreciates CONCATENATE

Function TEXTJOIN

  • Better than the CONCAT function (it’s strange that Microsoft has introduced these 2 functions in the same release)
  • TEXTJOIN combines the text from multiple ranges and/or strings, and includes a delimiter you specify between each text value that will be combined.
  • If the delimiter is an empty text string, this function will effectively concatenate the ranges
Function TEXTJOIN
TEXTJOIN with space delimiter

Library Logical

Function IFS

  • Can take the place of multiple nested IF statements, and is much easier to read with multiple conditions. 
  • The IFS function checks whether one or more conditions are met and returns a value that corresponds to the first TRUE condition

Function SWITCH

  • The SWITCH function evaluates one expression (a logical test) against a list of values, and returns the result corresponding to the first matching value.
  • This function is similar to the function SWITCH in Python, DAX, …
  • If there is no match, an optional default value may be returned.

Library Statistical

Function MAXIFS

MAXIFS returns the maximum value among cells specified by a given set of conditions or criteria

Function MAXIFS
Return the maximum in function of the product name

Function MINIFS

MINIFS returns the minimum value among cells specified by a given set of conditions or criteria

Move between workbooks

SDI is the abbreviation for Single Document Interface. SDI means that each workbook opens in its own window and has its own interface. This provides a lot of useful functionalities.

Benefits of dual screen with SDI

When you work with two monitors in a dual screen setup, showing two workbooks in the same Excel instance was not easy to do. Now with SDI, you can have a workbook on each screen.

Two workbooks on 2 different instances

Move or Copy between worksbook

With the SDI, moving a worksheet from one workbook to another is very easy; it is like moving a file from a folder to another one.

To move one worksheet to another workbook

  1. Put your 2 workbooks side by side
  2. Drag and drop a worksheet from the first workbook to the other one.

If you want to copy your worksheet, press the Ctrl key while you drag and drop.

Discontinued features in Excel 2013

In Excel 2013, 13 features have been discontinued or significantly changed.

Indeed, these features were not really popular, so it’s not a big issue if they have been removed.

Power BI with Excel 2013

In this video, look everything you can do with Excel 2013

Every tools used in this video are from the new application Power BI

  • Power Query
  • Power Pivot
  • Power View
  • Power Map

The logic of Power Pivot

Working with Power Pivot it isn’t hard. In fact it’s easier than Excel but the logic is totally different. But if you respect the next 3 rules, you will be able to build measures for your model

Rule 1: Structure of the data

When you want to create a measure, your data must be stored like in a database ; all the same data in only 1 column.

For instance, here is 2 presentation of the same data.

2 dimensional table
All the value in a single column

Even if the first presentation looks better because you can read easily your data, the second is the only one to present your data if you want to use Power Pivot.

With the second presentation, all the data for the calculation are in only one column and not 12 compared to the first presentation.

Rule 2 : Basic formulas are the best

When you work with Excel, COUNTIFS and SUMIFS are common functions to calculate with criteria. But when you work with Power Pivot, you must think more simple.

When you ask to an agregator to calculate your data, it’s the nature of the calculation that is the most important like SUM, COUNT, MIN, MAX, AVERAGE, …

In fact, this principle is already visible in Excel when you change the type of calculation in a Pivot Table.

With an agregator like Pivot Table, the basic calculations are the most important

Rule 3: Filters starts the measures

With Power Pivot, the name of  your formulas is a measure.

Measure SUM in Power Pivot

Once your have created your measures, you just have to simply drag it into a field in a pivot table to visualize the result.

Here is an example of the behavior of a measure in a pivot table. Each time you select a value in a slicer, this action give the order to the measure to be recalculated.

Presentation of Power Pivot

Power Pivot is a new tool of Excel 2013 that allows you to perform calculations on an unlimited number of rows. The calculation can be done with calculated columns, like in Excel, or with measures.

It all starts with SQL Server

Before to explain how to use Power Pivot, it is important to know that this tool has been developed by the engineers of Sql Server.

SQL Server is a relational database management system developed by Microsoft. It’s interface is SQL Server Management Studio (SSMS). When you use SSMS for the first time and when you are familiar with worksheet, you will be surprised to not visualize your data like you can do with Excel.

In fact, your data are stored, somewhere, but to visualize your data, you have to create a sql query.

Result of a query In SQL Server Management Studio (SSMS)

So, to visualize the data like in Excel, the Microsoft’s engineers have developed a tool call SQL Server Analysis Services (SSAS)

Data in SQL Server Analysis Services (SSAS)

When you link SSAS to a database, you can see easily the contain of each table like in a spreadsheet. But the data are in View only mode

SSAS = Power Pivot

At this step, Microsoft has created a tool to connect to database without limitation of rows. That’s the perfect tool to avoid the limitation of Excel.

So, in 2011, Microsoft has integrated SSAS to Office. And because the only way to return the results is in an agregator, and the only agregator of Excel is Pivot Table, SSAS becomes Power Pivot in Office (logic).

Installation of Power Pivot

In the version of Excel 2010, Power Pivot was an add-in but with Excel 2013, Power Pivot was integrated in the native Pro Plus version.

Update: Since May the 22nd 2018, Power Pivot is available for all the versions of Excel (Family, Student, Pro, …) and for Excel 2013 or Excel 2016.

But by default, Power Pivot is not activated. To activate Power Pivot, you must go to

  1. Files > Options > Add-ins
  2. Select COM Add-ins
  3. Press GO
  4. Finally, select the option Power Pivot

Now, you can see the tab Power Pivot in your ribbon

Power Pivot Ribbon

50 new functions in Excel 2013

With Excel 2013, 50 new functions have been added. Here they are and for some of them, few extra explanations.

Most of them are dedicated for top level engineers or statisticians. But there is also some very useful functions for a daily practice of Excel.

Date and time

2 new Date and time functions. ISOWEEKNUM is a relief to convert a date with the correct week number for most of the country in the world.

  • DAYS: Returns the number of days between two dates
  • ISOWEEKNUM: Returns the number of the ISO week number of the year for a given date. You will find an example of this function in this article

Remark: DAYS is useless because you can return the number of days between 2 dates with a simple subtraction

Lookup and reference

This new function is brilliant to present to your audience the writing of your formula

  • FORMULATEXT: Returns the formula at the given reference as text
Example of the function FORMULATEXT

Text

3 new Text functions

  • NUMBERVALUE: Converts text to number in a locale-independent manner
  • UNICHAR: Returns the Unicode character that is references by the given numeric value
  • UNICODE: Returns the number (code point) that corresponds to the first character of the text

Logical

2 new logical functions

Financial

2 new financial functions

  • PDURATION: Returns the number of periods required by an investment to reach a specified value
  • RRI: Returns an equivalent interest rate for the growth of an investment

Information

3 new function is this section. SHEETS is interesting to count the number of worksheet in your workbook.

  • ISFORMULA: Returns TRUE if there is a reference to a cell that contains a formula
  • SHEET: Returns the sheet number of the referenced sheet
  • SHEETS: Returns the number of sheets in a reference

Web

3 new Web functions. In fact, the web function category is new. You can now directly return the content of a website in a cell.

  • ENCODEURL: Returns a URL-encoded string
  • FILTERXML: Returns specific data from the XML content by using the specified XPath
  • WEBSERVICE: Returns data from a web service

Math 

7 new math functions. CEILING.MATH and FLOOR.MATH are really interesting

  • ARABIC: Converts a Roman number to Arabic, as a number
  • BASE: Converts a number into a text representation with the given radix (base)
  • CEILING.MATH: Rounds a number up, to the nearest integer or to the nearest multiple of significance
  • COMBINA: Returns the number of combinations with repetitions for a given number of items
  • DECIMAL: Converts a text representation of a number in a given base into a decimal number
  • FLOOR.MATH: Rounds a number down, to the nearest integer or to the nearest multiple of significance
  • MUNIT: Returns the unit matrix or the specified dimension

Statistical

6 new Statistical functions

  • BINOM.DIST.RANGE: Returns the probability of a trial result using a binomial distribution
  • GAMMA: Returns the Gamma value
  • GAUSS: Returns 0.5 less than the standard normal cumulative distribution
  • PERMUTATIONA: Returns the number of permutations for a given number of objects (with repetitions) that can be selected from the total objects
  • PHI: Returns the value of the density for a standard normal distribution
  • SKEW.P: Returns the skewness of a distribution based on a population: a characterization of the degree of asymmetry of a distribution around its mean

Engineering

13 new functions in this section. They are only useful for top level engineer. For a basic use of Excel, they are not useful.

  • BITAND: Returns a ‘Bitwise And’ of two numbers
  • BITLSHIFT: Returns a value number shifted left by shift_amount bits
  • BITOR: Returns a bitwise OR of 2 numbers
  • BITRSHIFT: Returns a value number shifted right by shift_amount bits
  • BITXOR: Returns a bitwise ‘Exclusive Or’ of two numbers
  • IMCOSH: Returns the hyperbolic cosine of a complex number
  • IMCOT: Returns the cotangent of a complex number
  • IMCSC: Returns the cosecant of a complex number
  • IMCSCH: Returns the hyperbolic cosecant of a complex number
  • IMSEC: Returns the secant of a complex number
  • IMSECH: Returns the hyperbolic secant of a complex number
  • IMSINH: Returns the hyperbolic sine of a complex number
  • IMTAN: Returns the tangent of a complex number

Trigonometry

8 new trigonometric functions

  • ACOT: Returns the arccotangent of a number
  • ACOTH: Returns the hyperbolic arccotangent of a number
  • COT: Returns the hyperbolic cosine of a number
  • COTH: Returns the cotangent of an angle
  • CSC: Returns the cosecant of an angle
  • CSCH: Returns the hyperbolic cosecant of an angle
  • SEC: Returns the secant of an angle
  • SECH: Returns the hyperbolic secant of an angle

New Excel 2013

This is the new interface of Excel. Pretty cool :)

Do you like it ?

Load more