After a bit of investigation, I came to the conclusion that what they really needed was a custom quote app which could be built fairly easily with Microsoft PowerApps. PowerApps can easily connect to and save data to many different data sources, so after a bit more investigating, I decided I would use SharePoint lists to store all the quote data from the app. I had heard about SharePoint before, but I never really knew what it was....
You’re gonna learn all the tips the pros use, so get ready for a very very long post! Download the example file with the data used in this post to follow along. Your Source Data Needs to be in Tabular Format When using a pivot table your source data will need to be in a tabular format. This means your data is in a table with rows and columns....
Microsoft Excel is used for many purposes across many industries, so it’s no wonder the need to add superscripts or subscripts arises quite frequently. Thankfully it’s very easy to insert subscripts and superscripts in Excel and there are quite a few options for this. This blog post will show you 11 easy ways to add subscripts and superscripts in Microsoft Excel. Add Subscript or Superscript from the Format Cells Dialog A lot of common formatting commands can be found in the Home tab in the Font, Alignment, or Number section....
Yes, I mean everything and there’s a lot. This post will tell you about all the awesome features Excel Tables have and why you should start using them. What is an Excel Table? Excel Tables are containers for your data. Imagine a house without any closets or cupboards to store your things, it would be chaos! Excel tables are like closets and cupboards for your data, they help to contain and organize data in your spreadsheets....
In a previous post I showed you How To View Actual Versus Target With A Thermometer Style Chart and in this post we’re going to look at how to display similar information in a gauge chart. Excel doesn’t have a gauge chart option, but we can use a pie chart to create something that looks like a gauge. In this example we’ll chart the current amount raised against a fundraising goal....
While entering or editing data in Excel it is possible to add line breaks in the cell using Alt + Enter. This can be a convenient way to display data in Excel, but is often not great for any type of data analysis. For example, it might be more convenient for the reader to display an address in a cell like this: Rather than like this: In this post I’ll show you how to separate the data in your cells based on the in-cell line breaks....
When you come across time series data analysis such as sales or stock trading. You have to calculate the period of time and play around with the Excel Date and Time functions. There are different methods to add months to a date in excel. This blog post explains each one with examples to help you understand how to do it. In this blog post, you will use built-in functions EDATE, DATE, and learn how to use Power Query to add months to a date....
For our example we’re going to pull the historical share prices for Apple Inc. These can be found here: https://www.google.com/finance/historical?q=NASDAQ%3AAAPL We are going to import this entire table into Excel using Power Query. Power Query makes this type of data grabbing very easy, quick and painless. To import the data: In the Navigator window: Now the data will appear in your sheet. If you need to refresh the data all you need to do is go to the Data tab and hit the Refresh button....
To view multiple sheets simultaneously, we need to open multiple windows and then arrange them. In the above example, I have created 4 windows to show each of the 4 sheets in the workbook and arranged them using the Tiled method.
Excel allows you to add multiple sheets within a workbook. This is a great way to organize your spreadsheet solutions as you can separate your inputs, data, calculations, reports, and visuals into different sheets. Organizing your workbooks with sheets can also make the spreadsheet easier to navigate for any user. How can you add new sheets to an Excel workbook? Follow this post to find out all the ways to add sheet tabs in Excel....
This method uses the legacy Excel 4 XLM functions. These functions aren’t like Excel’s other functions such as SUM, VLOOKUP, INDEX etc. These functions won’t work in a regular sheet, they only work in named functions and macro sheets. For this trick, you will need to create a named function. Use the above link to download the example workbook from this post. Create a named function. Specify the folder path....
I worked at a large insurance company and we were in the midst of a project to take inventory of all the spreadsheets used. We created a VBA procedure that would take a folder path and spit out all the file names in that folder. Each department was responsible for using this to take inventory of all their files. Fortunately for me, my department used only a few folders and subfolders for our work otherwise I would have tried to modify the code to loop through all subfolders in a folder....
Example Generic Formula What It Does This formula will return the weekday for a given date. How It Works The TEXT function will return a text string based on any custom format. This formula uses the “dddd” custom date format to return the weekday name for any given date. In our example, TEXT(“2030-01-01″,”dddd”) will return “Tuesday” since January 1st 2030 falls on a Tuesday. We can also use the “ddd” custom format to return abbreviated weekday name: Mon, Tue, Wed, Thu, Fri, Sat & Sun....
MOD(Range,2)=0 will create an array of Boolean values where TRUE will mean the number is even and FALSE will mean the number is not even. We then multiply this Boolean array by the Range to get an array where each value is either an even value from the Range or a 0. This is because NTRUE equals N and NFALSE equals 0 for any number N. In our example MOD({1....
Syntax IF(Expression, Value if true, Value if false) Expression (required) – This is the expression you want to test.Value if true (required) – This is the result the function will return if the expression is true.Value if false (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. Example In this example we use the formula IF(A2=”Donald Trump”,”Lame”,”Not Lame”)....
When you click the format painter you will be able to use it once, but if you double click it then you will be able to use it an unlimited number of times until you click the button again to turn it off. Easily Rename A Sheet Place your mouse cursor over the sheet you want to rename and double left click. Now type the name you want to change it to....
Not to be mistaken with Excel Tables, Data Tables are a tool that can be used to do “what if” type analysis and allows you to see the results of a calculation while varying up to two of the calculation inputs. In this example we’ll create a data table based on the accumulated value of a bank deposit that earns interest (i.e. how much money will we have after a given time at this interest rate)....
Syntax INT(Number) Number (required) – This is the number you want to round down to the nearest integer. Example In this example find the round down a few numbers. Text values will result in a #VALUE! error.
This is usually the area where we summarize fields by various different aggregation methods like taking the sum, average, minimum, maximum or standard deviation. But the thing is, these aggregation methods require numeric data! Is there any way to summarize text based data that will return text as the result? The answer is yes, but we will need to use the data model and DAX formulas to do this. Traditional pivot tables do not have this functionality....
Tables are a great tool for storing all your tabular data, but when you want to add new data to the table you will need to extend the boundaries of the table. This will allow you to add one or more rows to the bottom of your table so it’s obvious that you can add new data to the table. This post will show you how to expand your Excel table and increase its range size....
