In this post we’re going to find out how to import data from multiple files in a folder using Power Query. To do this we will need certain conditions to be true. We’ll need Power Query installed. Read this post to find out how to install it.The data structure in each file must be the same.Same number of columns with the same column headings.Data is on the same sheet name in each file....
Imagine you track sales each day. Your data contains a row for each date with a total sales amount, but maybe you want to know the total sales for the month at each day. This is a running total, it’s the sum of all sales up to and including the current days sales. In this post we’ll cover multiple ways to calculate a running total for your daily data. We’ll explore how to use worksheet formulas, pivot tables, power pivot with DAX and power query....
This post is going to show you all the ways you can get the URL from a hyperlink in Microsoft Excel! A hyperlink is a link that allows users to navigate to a web page or document. Hyperlink cells typically contain a URL, which is the address of the linked page or document, and anchor text, which is the text that appears on the screen and is clicked by the user....
This function can be used to find the first occurrence of a text string within another text string. Syntax FIND(Text to find, Text, Start position) Text to find (required) – This is the text you want to find within another text string.Text (required) – This is the text you want to search within.Start position (optional) – This is the result the function will return if the expression is false. If nothing is entered here the function will return FALSE when the expression is false....
If you’ve ever had a long list of inputs/outputs in your workbook and wanted to give them defined names based on their labels, then this is a tips you’ll definitely want to learn as it’s a lot quicker than naming them individually using the Name Box or Name Manager. Since spaces aren’t allowed in defined names, this method also replaces any space characters in your labels with underscores. Use the Create from Selection command....
Syntax TRIM(Text) Text (required) – This is the text you want to remove extra spaces from. Example In this example we use the function on the text “These extra spaces are annoying! ” to remove the excess spaces and result in the text “These extra spaces are annoying!”.
There’s a good chance you’ve probably needed to convert your Excel file into a PDF. In this post, I’ll show you 5 ways you can easily convert your Excel file into a PDF. Video Tutorial Save as PDF This is the most commonly used method and you might have already used this once or twice already. Save a copy of the file as PDF. Easy enough, and your new PDF file will be saved in the same location as the Excel file is saved....
FIND(“]”,FilePath) will return the location of the “]” character before the sheet name (let’s call this the Location). In our example FIND(“]”,FilePath) returns the value 132 since “]” is the 132nd character in the FilePath text string. LEN(FilePath) will return the total character length of the FilePath text string (let’s call this the TotalLength). In our example LEN(FilePath) returns the value 140 since there are 140 characters in the text string....
Example Generic Formula What It Does This formula will return the folder path where the current workbook is saved. How It Works CELL(“filename”,AnyCell) will return the full folder path, file name and sheet name of the referenced cell. For my workbook this was the following location. All we need to do is parse all the text up until the first square bracket [ seen above highlighted in yellow. We will first need to find the location of [ using FIND(“[“,CELL(“filename”,AnyCell))....
VLOOKUP is one of the most useful functions in Excel, but after learning its use most people quickly realize some of its limitations. It only allows you to look up items to the right of a column with the criteria you’re looking up.It requires you to know how many columns away the results column is from the lookup column. This means if you insert a column later on your formula will break....
If you want to work more efficiently, then learning keyboard shortcuts is the way to go. Keyboard shortcuts can help you save time and effort when coding in VBA for Excel. You can learn how to do many actions in VBA for Excel with the press of a few keys. You’ll be able to work faster and smarter than ever before to build your macros when you use these keyboard shortcuts!...
It’s actually quite easy! The formatting features in Excel are quite powerful, and they allow you to conveniently spot and highlight important facts about your data such as negative numbers. Losses are usually displayed as red numbers in financial reports such as a company profit and loss statement. This red font color helps to make them easier to see. But you can also use these same techniques to show your negative values in any color of your choice....
Today I was using power query to import external data in a CSV file and then do some transformations on the data. I decided I wanted to import my data as a connection only first then perform the transformations in a separate query. I had My Sales Data connection only query ready to use in another query. I hadn’t really noticed before, but there’s actually no command in the ribbon to get data from a query....
Syntax TODAY() There are no arguments for this function. Example In this example we return today’s date.
Syntax YEAR(Date) Date (required) – This is the date value that you want to return the year from. Example In this example we use the function on several dates in different formats. Notice the text values we try result in a #VALUE! error while numerical values outside of the range 1 to 2,958,465 result in a #NUM! error.
This might be something you have accidentally enabled and don’t understand why your Excel is not scrolling normally or why the arrow keys aren’t moving the active cell cursor. It can be frustrating when your Excel workbook isn’t working normally as expected! This post is going to show you how to fix your scrolling problems and turn off the scroll lock feature in Excel. What is Scroll Lock? Scroll lock is a feature that allows you to scroll through your worksheet using the arrow keys without moving the active cell....
Excel has a great built in function called SUBSTITUTE which allows you to find one bit of text within another text string and substitute it for another bit of text. Copy and paste this table into cell A1 in Excel In the above example we can use the SUBSTITUTE function to clean the text of all the percentage signs by replacing them with an empty string (“”) in the following formula....
If you ever need to insert multiple blank rows into your data, doing it manually could be very time consuming if you have a large data set. Here’s a quick way to do this by inserting a blank row into your data after every Nth record. I realize it’s a bit of cluster bomb and I might have put a bit too much in it 🙁 To accomplish our task we’re going to need to add a helper column....
Web pages, FacebookExcel, CSV, XML, Text or Hadoop (HDFS) FilesA FolderVarious databases like MS Access, SQL Server, MySQL, Microsoft Azure SQL, Oracle, IBM DB2, PostgreSQL, Sybase, Teradata, OData etc… This is available as an add-in for excel 2010 professional plus or 2013 and comes already built in for Excel 2016. You can download Excel Power Query here from Microsoft. Unfortunately, if you’re not running Excel 2010 professional plus or 2013, then you will need to upgrade to Excel 2016 in order to use this feature as it’s not available for previous versions of Excel....
