Excel tables or formulas for data analysis

%3CLINGO-SUB%20id%3D%22lingo-sub-3288879%22%20slang%3D%22en-US%22%3EExcel%20tables%20or%20formulas%20for%20data%20analysis%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3288879%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20a%20data%20set%20of%20all%20our%20inbound%20calls%20from%20last%20week%20and%20what%20times%20the%20calls%20came%20in.%20To%20be%20able%20to%20monitor%20when%20our%20peak%20periods%20were%2C%20I%20want%20to%20create%20a%20pivot%20or%20table%20that%20tells%20me%20how%20many%20calls%20we%20got%20last%20week%20in%20each%2015minute%20time%20slot.%20for%20example%2C%20this%20is%20part%20of%20our%20data%20set%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22loljay_0-1650346573967.png%22%20style%3D%22width%3A%20413px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F365164iB4AA304B5931934A%2Fimage-dimensions%2F413x64%3Fv%3Dv2%22%20width%3D%22413%22%20height%3D%2264%22%20role%3D%22button%22%20title%3D%22loljay_0-1650346573967.png%22%20alt%3D%22loljay_0-1650346573967.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Eand%20I%20want%20to%20be%20able%20to%20put%20the%20data%20in%20here%20(below)%20for%20how%20many%20calls%20last%20week%20came%20in%20between%207.30%20-%207.45%20for%20example.%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22loljay_1-1650346127991.png%22%20style%3D%22width%3A%20190px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F365163iC8F649300269D669%2Fimage-dimensions%2F190x318%3Fv%3Dv2%22%20width%3D%22190%22%20height%3D%22318%22%20role%3D%22button%22%20title%3D%22loljay_1-1650346127991.png%22%20alt%3D%22loljay_1-1650346127991.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIs%20there%20a%20formula%20I%20can%20use%20to%20pull%20this%20data%20into%20the%20cells%3F%20or%20can%20someone%20help%20to%20create%20a%20pivot%20or%20normal%20table%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-3288879%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3294955%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20tables%20or%20formulas%20for%20data%20analysis%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3294955%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1366856%22%20target%3D%22_blank%22%3E%40loljay%3C%2FA%3E%26nbsp%3B%3CBR%20%2F%3EHere%20are%20couple%20of%20more%20variants%3C%2FP%3E%0A%3CP%3E-%20PivotTable%20(if%20you%20work%20with%20data%20model)%3C%2FP%3E%0A%3CP%3E-%20formula%20(if%20you%20work%20with%20dynamic%20arrays%2C%20i.e.%20Excel%20365%20or%202021)%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3294681%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20tables%20or%20formulas%20for%20data%20analysis%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3294681%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1366856%22%20target%3D%22_blank%22%3E%40loljay%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThere's%20no%20Excel%20formula%20as%20this%20is%20done%20with%20%3CA%20href%3D%22https%3A%2F%2Fsupport.microsoft.com%2Fen-us%2Foffice%2Fabout-power-query-in-excel-7104fbee-9e62-4cb9-a02e-5bfb1a6c536a%3Fmsclkid%3D0f98e684c52611eca5ad1cd31ee5db93%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3EPower%20Query%3C%2FA%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3294673%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20tables%20or%20formulas%20for%20data%20analysis%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3294673%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F75890%22%20target%3D%22_blank%22%3E%40L%20z.%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3Ehey%20thanks%26nbsp%3B%3C%2FP%3E%3CP%3Eits%20just%20not%20letting%20me%20see%20any%20formulas%20in%20that%20sample%20sheet...%20would%20you%20be%20able%20to%20send%20the%20formula%20you%20used%20in%20column%20D%20of%20the%20sample%20please%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ETysm%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3290507%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20tables%20or%20formulas%20for%20data%20analysis%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3290507%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1366856%22%20target%3D%22_blank%22%3E%40loljay%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWith%20Power%20Query%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22_Screenshot.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F365591i1F2682324E7B72EB%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22_Screenshot.png%22%20alt%3D%22_Screenshot.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESample%20attached%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

I have a data set of all our inbound calls from last week and what times the calls came in. To be able to monitor when our peak periods were, I want to create a pivot or table that tells me how many calls we got last week in each 15minute time slot. for example, this is part of our data set;

loljay_0-1650346573967.png

 

and I want to be able to put the data in here (below) for how many calls last week came in between 7.30 - 7.45 for example.

loljay_1-1650346127991.png

 

Is there a formula I can use to pull this data into the cells? or can someone help to create a pivot or normal table?

 

Thanks!

 

4 Replies

@loljay 

 

With Power Query

_Screenshot.png

 

Sample attached

@L z. 

hey thanks 

its just not letting me see any formulas in that sample sheet... would you be able to send the formula you used in column D of the sample please?

 

Tysm

@loljay 

There's no Excel formula as this is done with Power Query

@loljay 
Here are couple of more variants

- PivotTable (if you work with data model)

- formula (if you work with dynamic arrays, i.e. Excel 365 or 2021)