Feb12019 by KathyNo Comments
Refresh Pivot Table Data – Excel Tip

Refresh Pivot Table Data – Excel Tip

accounting, Excel Tip
If you are an intensive pivot-table-using business, slicing and dicing data into meaningful chunks, you may need to refresh the data often. When you are working with data in Power Pivot, from time to time you might need to refresh the data from the source, recalculate the formulas that you have created in calculated columns, or make sure data presented in a PivotTable is up-to-date. Per Microsoft Support, you can refresh the data for PivotTables connected to external data, such as a database (SQL Server, Oracle, Access, or other), Analysis Services cube, data feed, and many other sources. You can also refresh data from a source table in the same or a different workbook. And you can set your workbook to refresh its PivotTable data automatically when you open it. By…
Read More
Jan262019 by KathyNo Comments
Wildcards in Formulas – Excel Tip

Wildcards in Formulas – Excel Tip

accounting, Excel Tip
I learned a new trick last week – using wildcards in Excel formulas.  I’ve always known you can use wildcards characters such as the asterisk * or ? when searching for items in windows explorer, but I wasn’t quite sure how to use them in Excel formulas. For example, many times I want to calculate a formula if a certain condition is present, and usually using a =IF(condition..) formula works just fine when looking at numbers.  It can become more complicated if you are looking at text and want to make a calculation based upon it or other criteria.  COUNTIF is a function to count cells that meet a single criteria. COUNTIF can be used to count cells with dates, numbers, and text that match specific criteria. The COUNTIF function…
Read More
Sep192018 by Kathy DrungilasNo Comments
#DIV/0! Error -> Remove it – Excel Tip

#DIV/0! Error -> Remove it – Excel Tip

accounting, Excel Tip
No one likes to see that demented #DIV/0! Error in Excel spreadsheets and pivot tables!  We know that dividing by a 0 value will equate to zero, so why does Excel not return what we want to see? To fix #DIV/0! in a worksheet – Change your formula. In our example below, we are using the formula =M36896/L36896 to calculate a number formatted as a percentage.  Since our numbers in columns M and L are blank, we get the #DIV/0! Error.   If we change our formula to use the IFERROR function, we can replace the error with our choice of 0 or a blank or even another word.  The IFERROR function in the form of =IFERROR(M36896/L36896,0) will return a 0 instead of the error.     If you want…
Read More