# Category Archives: Excel

## AND, OR Logical Functions in Excel

By | 23. 5. 2020

Logical functions serve us to test conditions not only in Excel. They can be used in situation when we need to compare 2 or more values and find out if a certain condition is met. Often used function IF belongs to logical functions, except for this function, you will most often use functions AND and… Read More »

## Excel | Subtotal – 11 Functions in One

By | 23. 5. 2020

SUBTOTAL is a little strange function. There are 11 mathematical functions hiding inside it. We can calculate sum, arithmetic mean, spread, minimum/maximum and another statistic values using SUBTOTAL function. What’s more, we can limit whether we want to calculate even with hidden values which were hidden manually. All under roof of one function. SUBTOTAL –… Read More »

## Excel – Function for date and time – YEAR, MONTH, DAY, HOUR, MINUTE, SECOND

By | 23. 5. 2020

There is a huge number of functions for date and time in Excel. they are used to extract parts of the date or time type of cell. You may have needed to extract some certain parts of the data of date and time data types, for example month. All functions have identical and absolutely simple… Read More »

## WEEKNUM, ISOWEEKNUM,WEEKDAY Excel – Was it on Friday After 15th Week of The Year?

By | 21. 3. 2020

You have a huge table with many date entries in it. You need to find out which date is after 20th week of the year or if a certain date is Friday. MS Excel will help by three functions – WEEKNUM, ISOWEEKNUM,WEEKDAY . It is definitely more effective than searching in calendar. More so if… Read More »

## SQL Indexes – Indexing Theory Simply, Balanced Tree, Heaps

By | 11. 2. 2020

Correct table indexing in SQL Server is a base for good database performance during querying. You need to understand how SQL Server stores data into tables/indexes if you want to create appropriate sql indexes. It is also important to know how to approach these data correctly during querying. How Does SQL Server Organize Data Physically?… Read More »

## How to Execute SQL Query in Excel (Office 365)? Tutorial with Examples

By | 3. 2. 2020

This article is made especially for those who regularly export large data volumes to Excel in order to analyze them or process some kind of regular report. Usually, we insert the data into Excel simply by copying it from some factory system. But data can also be inserted into excel in more suitable ways which… Read More »

## How to Execute SQL Procedure in Excel incl. Parameters with Example

By | 3. 2. 2020

I must say right from the beginning that even though this process is possible, I do not recommend it (but I will show it to you anyway) :). Launching SQL queries in Excel is not optimal in general, but sometimes there is no other option. This guide is aimed on advanced Excel users with SQL… Read More »

## Excel Import to SQL Server Database via SSMS

By | 2. 2. 2020

Import of Excel sheet or csv file into SQL Server is a quite common task. You can find a guide on how to do it below. Let’s have an excel file containing data on companies:                   Steplist – Import Excel to SQL Database Example 1) Data import… Read More »

## Excel Fuzzy Lookup Guide – Comparing 2 Lists with Example

By | 2. 2. 2020

Did you ever need to compare 2 lists and test it for duplicities? The easiest way to do it in Excel is to use standard functions as VLOOKUP. Sometimes though, we might need to compare 2 lists where one of them contains typos. Here, Fuzzy lookup comes into play (can be downloaded here). Typical example… Read More »

## Weighted Arithmetic Mean in Excel – SUMPRODUCT

By | 2. 2. 2020

Weighted arithmetic mean is a statistical value of broad use not only in mathematics or statistics. If you need to calculate weighted arithmetic mean in Excel, here is a guide How Works Function SUMPRODUCT and Weighted Arithmetic Mean in Excel Example: Lets have few loans where every loan has different principal and interest rate. The… Read More »

## SSRS Tutorial Part 2 – Data Source Configuration in SSRS – Reporting services

By | 18. 1. 2020

We took a look at creation of project and empty report in the BI tool SSDT (SQL Server Data tools) in previous part of the miniseries (Part 1 -SSRS project and report creation). This set up the ground for this part in which we will configure the report for data tools (SQL Server database in… Read More »

## How to Enable Power Pivot in Excel

By | 26. 11. 2019

Powerpivot in excel is an add-in automatically pre-installed in MS Office 2013 + (all you need to do is add it to the ribbon). It is not present in previous Office release therefore it is needed to download it, install and then allow. Enabling Power Pivot in Excel Download add-in from following URL (Windows 7… Read More »

## Getting Started with Power Pivot – Data Modeling in Excel

By | 26. 11. 2019

Powerpivot is an excel add-in enabling data modeling and analysis in familiar Excel environment which is certainly an advantage. Import big amounts of data through Power Query and connect them to each other using PowerPivot One of the upsides of power pivot is possibility to connect multiple data sources into one excel. It even do… Read More »

## Excel | Text Functions – Top Functions Overview + Examples

By | 8. 11. 2019

In Excel, we work mostly with numbers (aggregation via SUM, AVERAGE, etc.) or with dates. However, it is true that very often we need to adjust, combine or clean text columns. There are many situations where we want to edit the text in some way (it cannot be generalized). It depends on a case-by-case, but… Read More »

## Excel | VLOOKUP vs HLOOKUP – What’s the Difference + Example

By | 8. 11. 2019

The VLOOKUP function is one of the most commonly used Excel functions in general. This function returns a value we are looking for from a particular column of a table containing many rows and columns. The function supports 2 modes – full and approximate match. In addition to the VLOOKUP function, there is a similar… Read More »

## Excel | Mean (Average) Functions Overview – Types, Formulas, Explanation and Examples

By | 2. 11. 2019

Let’s start with what the average means. In terms of descriptive statistics, the average is a certain reference (typical) value. It is a property of the data set that we are investigating. It belongs to the so-called measures of central tendency. If we have a large amount of data, we can describe the data with… Read More »

## Day Count Convention (Fractions) for Bonds, Loans, Accrued Interest – Financial Mathematics

By | 25. 10. 2019

Most calculations in the area of financial mathematics are relatively easy – for example, to calculate an annuity payment, interest rate or target amount value with regular deposits. However, financial calculations can be quite complex in certain circumstances. This is particularly true on the money and capital markets (bonds) or on some specifically agreed corporate… Read More »