Category Archives: Excel

Enhance data after import using Power BI

We have already learnt how to import and clean data from various sources using Power Query (Transform). Once the data is inside Power BI Data tab (or Excel Data Model of Power Pivot), we can refine and enrich it further using various features like synonyms, custom sorting order, formatting, summarization and more. Watch the video for details – English as well as हिंदी versions are available.

English version of data shaping in Power BI
हिंदी version

10 rules for Clean data

We spend lot of time in data cleaning. But there is no simple definition of exactly what clean data means. Therefore, I created a simple 10 item checklist. If all 10 items are correct, data is clean. Simple and easy to use.

10 rules for Clean data

Remember, here we are talking about input data – not output (reports).

  1. Each column must have a heading
  2. No blank headings
  3. No duplicate headings
  4. No formulas in headings
  5. No merged cells
  6. Each column must have ONE meaning
  7. Each column must have ONE type of data
  8. No subtotals or grand totals
  9. Formatting should not be used instead of data
  10. Data must grow vertically, not horizontally
Watch this video to learn more.
This is the Hindi version.

Convert clean data to a Table

If you are using Excel, as soon as data is cleaned, convert it into a Table. Why? Here are the benefits of using Excel Tables – 13 articles.

Remove cell spill-over from Excel data

If a cell has no data in it, the data from the cell on the left side spills over it. This can lead to confusion while reading and understanding the data. How to remove this cell spill-over?  Very simple. Just three steps:

image

  1. Select the data range.
  2. Press Ctrl G or F5 to open the Go To dialog.
  3. Click the Special button.
  4. Choose the Blanks option.
  5. Now, all the empty cells in the highlighted area will be selected.
  6. Just type a spacebar. This will go automatically into the active cell.
  7. DO NOT press ENTER. DO NOT disturb the selection.
  8. Press CTRL ENTER.
  9. That’s it. Now all the spill-over items will be truncated.

image

Excel Data Model : Simplifying usage

Excel Data Model is a database that is built-in to Excel. It has been around since 2010. Using it increases the capacity of Excel to handle millions of rows, it reduces file size significantly and eliminates VLOOKUP for code to description mapping. These new options in Excel 2016 and above simplify the usage of Excel Data Model and improve performance for large data operations. Go to File – Options – Data tab.

Excel Data Model usage options

Options in section 1 and section 2 are already covered in my previous blog articles. Section 3 helps us incorporate Data Model more easily into day-to-day Excel data management activities.

Continue reading Excel Data Model : Simplifying usage