Skip to main content

Posts

Showing posts from October, 2022

Data Cleaning Functions in Excel

  The CLEAN function Using the CLEAN function removes nonprintable characters text. For example, if the text labels shown in a column are using crazy nonprintable characters that end up showing as solid blocks or goofy symbols, you can use the CLEAN function to clean up this text. The cleaned‐up text can be stored in another column. You can then work with the cleaned text column. The CLEAN function uses the following syntax: CLEAN(text) The text argument is the text string or a reference to the cell holding the text string that you want to clean. For example, to clean the text stored in Cell A1, use the following syntax: CLEAN(A1) The CONCATENATE function The CONCATENATE function combines, or joins, chunks of text into a single text string. The CONCATENATE function uses the following syntax: CONCATENATE(text1,text2,text3,...) The text1, text2, text3, and so on arguments are the chunks of text that you want to combine into a single string. For example, if the city, state, and ZIP co...

Choose the Right Data Visualization for Numbers

  Only numbers - Showing Numbers in visuals  Sometimes, just showing the data as text is the most effective way of conveying information. Single value chart When you just have one number, it’s best to just report it as-is. Plotting a single value graphically (such as with a bar or point) usually isn’t meaningful if there aren’t other values to compare it to. Single value with indicator An indicator compares the single value to a second number. This is often to compare a metric’s value between the current period and the previous period. Bullet chart Chart type comparing a single value to another number, often a benchmark rather than another data point. The single value is shown with a bar’s length, while comparison points are shown as shaded regions or a perpendicular line. Table Compares data points (rows) across multiple different attributes (columns). Usually sorted by an important or prominent attribute to improve utility.

Count Functions Uses in Microsoft Excel

  1. Count No. of Unique Values Use following formula to count no. of unique values - =SUMPRODUCT((A1:A100<>"")/COUNTIF(A1:A100,A1:A100&"")) 2. Count No. of Unique Values Conditionally If you have data like below and you want to find the unique count for Region = “A”, then you can use below Array formula – =SUM(IF(FREQUENCY(IF(A2:A20<>"",IF(A2:A20="A",MATCH(B2:B20,B2:B20,0))),ROW(A 2:A20)-ROW(A2)+1),1)) If you have more number of conditions, the same can be built after A2:A20 = “A”. Note - Array Formula is not entered by pressing ENTER after entering your formula but by pressing CTRL+SHIFT+ENTER. If you are copying and pasting this formula, take F2 after pasting and CTRL+SHIFT+ENTER. This will put { } brackets around the formula which you can see in Formula Bar. If you edit again, you will have to do CTRL+SHIFT+ENTER again. Don't put { } manually. 3. Count Cells Starting (or Ending) with a particular String 1. Say you want to ...