Category: excel_usage

Move/Copy/Duplicate A Sheet (3 ways to do)

excel_usage, Shortcuts

Hi there,

 

Reporting team will have to work on weekly, bi-weekly, monthly or yearly reports on day-to-day reporting. This reporting mostly involves in duplicating worksheets except 5% of manual changes.

Let me explain with example: Say, we are working on Attendance report. Each sheet is renamed with Date, starting from 01-01-2015 till 01-31-2015. Here we require 31 sheets to display. The change between one sheet to the another is nothing except renaming the sheet name with different date. 

To do this, we will work on one sheet and then we will copy and insert the sheet next to the existing by renaming the sheet to next date. Similarly we will do it for the rest of the sheets.

Full story »

Sort Data Horizontally (quick tip)

excel_usage, Excel Tips

Hi there,

 

How are you doing today? Hope you are doing well, right?

Ok,

In this post, I am explaining about how to sort the data horizontally? Sorting data vertically has been our routine day to day work. But, data sorting horizontally has a small trick.

To know how to do it, please do watch this video.

Full story »

Rules to Follow While Creating Names

excel_usage

Hi there,

There are some rules which needs to be followed while creating names for a range of cells using NameBox feature or through "Create from Selection" feature.

The rules includes:

  • The first character should always be either 
    • _ (underscore)
    • / (backward slash)
    • a letter

Full story »

Convert Column Names into Numbers

excel_usage

Hi there,

 

Recently, I have received an excel workbook from my student and it was looking like this (empty sheet is posted here).

R1C1 Style of Excel


Did you find the difference from normal worksheet? No!, see the column names are showing in numbers and row names are also showing as numbers, right?


Alternative link to watch this video

Yes, even I was shocked when I see this for the first time. After a short shock, woke up after 10 - 15 mts of time to crack that worksheet. I also thought how is this possible in Excel. I think, even you would have shocked by now, right?, That is when I cracked this excel sheet. Do you know how to crack this column names with numbers rather than alphabets. If you know, stop this post here and post it in the comments section. Rest can proceed with me:

Full story »

Create a Client's Email id as Hyperlink (No VBA)

excel_usage, Excel Tips

Hi There,




Today, I want to share a time consuming trick using a function called HYPERLINK. I think you all know the purpose of this function in Excel. But, how to use this function thinking out of box?


Best Free Email Providers in Excel website


Here we go:

HYPERLINK function calls your client through E-mail. I mean to say using this function we can create an hyperlink to the client's email id.

Full story »

Custom Grouping in Pivot Table (A Challenge!)

excel_usage, Excel Tips, Pivot Tutorial

Hi There,


Today, when I was taking a class on PIVOT, one of my student asked this question and I found it challenging, not exactly but found two different methods to achieve this. So, thought of sharing with you. The Pivot Challenge is as below:

Grouping Animals like in Pivots in excel website


"How to Group Dates from Monday to Sunday using Pivot Table?"

Initially, I felt like pivot class is almost getting over and why is he asking grouping again (I covered group by words and other concepts yesterday). Any how, I should give him an answer. So, I took an example workbook where the starting day of the dump is from Thursday (08/02/2007) and the requirement to group the dates are opened cases on or after 08/25/2014. (08/25/2014 is Monday).

Full story »

Contact / Help. ©2019 by Abhilash VK. Design & icons by N.Design Studio. Skin by Tender Feelings / Evo Factory.